CODEWITHSUNDEEP
excelvba
wahib
wahib

Reputation: 11

Selecting a particular cell in Excel

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.

Upvotes: 1

Views: 727

Answers (3)

Mischinab
Mischinab

Reputation: 2921

If the employee numbers are not going to be sorted in the second sheet, you can also use MATCH in combination with INDEX:

Using Tomalak's example, the formula for [xxxx] would be:

=INDEX(Sheet2!C:C;MATCH(B2;Sheet2!B:B;0)

In effect, the MATCH statement finds the row in Column B of sheet 2 where there is an exact match for the employee ID (B2). INDEX then takes this a an offset into Column C of sheet 2 where the new wage is stored.

Upvotes: 0

Matthew Rathbone
Matthew Rathbone

Reputation: 8269

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

Upvotes: 0

Tomalak
Tomalak

Reputation: 338406

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.

Upvotes: 4

Related Questions