VBA Code for auto entry

Common problems – different language?
Let's talk in English.

VBA Code for auto entry

Poslaťod Palo-admin » 03.05.2010, 08:50

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
Palo-admin
Administrátor stránky
 
Príspevky: 473
Registrovaný: 18.07.2008, 16:43
Udelené poďakovania: 6 krát
Prijaté poďakovania: 63 krát

Späť na Solving MS Office problems in English

Kto je on-line

Užívatelia prezerajúci fórum: Žiadny registrovaný užívateľ nie je prítomný a 1 hosť