Posted by Avinash Naik
Hi,
I want a VBA code by which I can get one values in particular cells or rows by entering in other cells or rows.
(I don't want excel formula)
For E.g. If I enter "1" in cell A1 in B2 it must be attomatically enter "Apple"
if I enter "2" in A2 in B2 it must come "Orange"
in A3 "1" again in B3 "Apple".
But it must be a VBA code not an excel formula [=if(a1=1,"Apple",if(a1=2,"Orange","Else"))]
Hi Avinash
If you need complex solution (list of compared data in separate sheet), you can use code described below.
You can use Worksheet_Change function over the input range sheet, like code below:
"Code List" is prepared in separate list "Data_Source", but also can be used array inside of code.
As you can see from code, only data in range “range_Input” (C2:C10) will be evaluated.
See sample (VBA code - ALT+F11):
http://pc-prog.eu/phpBB3/images/xMyKB/VBA_Code_for_auto_entry.xls
But maybe simple macro like this, could be enough.
- Kód: Vybrať všetko
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> Columns("A").Column Then Exit Sub
Dim v As String
Select Case Target.Value
Case 1
v = "Apple"
Case 2
v = "Orange"
End Select
Target.Offset(, 1).Value = v
End Sub
Palo