If and then statement macro

[Guest]

I am trying to write a macro which checks the data in cell A and B in a
worksheet then outputs in col c.
e.g
If Cell A1 = "Fund A" and ' check Fund here
Cell B1 = "Client Name" then ' check list of clients here
Cell C1 = "account number" ' output clients a/c no. here from list
end

This statement needs to run through all of Col A and B and check 10
different Funds and 20 client names. to produce given account number

 

[Guest]

I would set up Fund A, Fund B...etc in say 10 blocks
each containing two columns for clients and account numbers

loop through column A
for i = startrow to lastrow
select case range("A" & i)
"Fund A" : range("C" & i) = vlookup(range("B" & i), block of Fund A, 2)
"Fund B" : etc
etc
end select
next i

 

[Guest]

Thanks KC,
I am trying the below code but there seems to be errors?
I cant see in the lookup where we find fund A, b, c in col A then look up
a/c name in col b then output data from col C?

For i = startrow To lastrow
Select Case Range("A" & i)
"FUND A" : range("C" & i) = vlookup(range("B" & i), block of FUND A, 2)
"FUND B" :range("C" & i) = vlookup(range("B" & i), block of FUND B, 2)
"FUND C" : range("C" & i) = vlookup(range("B" & i), block of FUND C, 2)
"FUND D" : range("C" & i) = vlookup(range("B" & i), block of FUND D, 2)
"FUND E" : range("C" & i) = vlookup(range("B" & i), block of FUND E, 2)
End Select
Next i

 

[Guest]

I was only suggesting the approach I would use.
The code as it is will never work.

You have to define where "block of FUND A" etc are
also use index and match to get the account number into range("C" & i)

 

[Guest]

Code below tested OK
I leave the adjustment to you as exercise

Sub main()
Dim rng As Range
Dim rng1 As Range
Set rng = Range(Cells(1, 5), Cells(20, 6))
Set rng1 = Range("E1:E20")
For i = 1 To 5
j = Application.Match(Range("A" & i), rng1, 0)
Range("B" & i) = Application.Index(rng, j, 2)
Next i
End Sub