VLOOKUP using IF function and multiple sheets

[eeyore89]

Hi,

I'm writing a VBA code:

There are 10 members who are either Male or Female.
"Males" and "Females" are two different worksheets with data on them.

Dim ELT As Variant

'If "M" is on the sheet, refer to "Males" worksheet else "F" for "Females" worksheet.
If ActiveCell.Offset(0,2).Value = "M" Then ELT = "Males" Else ELT = "Females"

I want to use a VLookup function to obtain a value dependent on whether they are Male or Female and hence to know which worksheet to look through.

I have tried:

Sheets(ELT).Activate
Lx = WorksheetFunction.VLookup(Age, Range("A1:B200"), 2, False)

but it cannot locate the 2nd argument i.e. the sheet where the data needs to be obtained.

Any help would be greatly appreciated!

Many thanks

 

[smiler]

if ELT="Males" then
Lx = WorksheetFunction.VLookup(Age, sheets("Males").Range("A1:B200"), 2, False)
else
Lx = WorksheetFunction.VLookup(Age, sheets("FeMales").Range("A1:B200"), 2,false)
end if

 

[eeyore89]

Thank you so much!! I didn't think an IF statement would work properly whilst already within another IF statement and a FOR loop. Thanks once again!