Selecting a particular cell in Excel

Question

I have two Excel sheets. The first one has some data. The second has updated data and should be used to alter specific information on the first sheet.

For example: If I had (in the first sheet) employee wage and his number and in the second sheet also. I want to write code that will find the employee number in the second sheet and change the wage according to the wage stated in the second sheet.

Answer 1

You don't need VBA, this can be done through a simple cell function: VLOOKUP().

Sheet1:

    A      B      C         D
1 Name    No.   Wage      NewWage
---------------------------------
2 Adam    111   1000.00   [xxxx]
3 Brad    222   1300.00
4 Charly  333   2000.00

Sheet2:

    A      B      C
1 Name    No.   Wage
-----------------------
2 Adam    111   1100.00
3 Brad    222   1400.00
4 Charly  333   2100.00

The formula for [xxxx] would be:

=VLOOKUP(B2;Sheet2!B:C;2;FALSE)

This looks up the new wage for each person from the second sheet. Fill the formula down.

Make sure that values in Sheet2 are sorted by employee number, or VLOOKUP() will not find them. Read through the help page for VLOOKUP() for more details.

Answer 2

You could Use .find:

dim findHere as Range
dim result as Range

set findHere = Sheet2.Range("A:A")
set result = findHere.Find("EmployeeCode")

if not result is nothing    'you get 'nothing' back if it can't find it.
result.offset(0, 2).Value = "New Wage Here"
end if

Or you could loop through the cells (I'd avoid this one if possible though, it can be v slow if you don't disable screen updating and automatic calculation)

Dim r as Range
dim finished as boolean
set r = Sheet1.Range("A1")
do
if r.Value = <ID> finished = true

while not unfinished

' do stuff with r