Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
If and then statement macro
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
If and then statement macro
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 "George" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
If and then statement macro
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 "KC" wrote: 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 "George" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
If and then statement macro
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) "George" wrote: 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 "KC" wrote: 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 "George" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
If and then statement macro
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 "KC" wrote: 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) "George" wrote: 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 "KC" wrote: 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 "George" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If Statement to run a macro | Excel Discussion (Misc queries) | |||
Run Macro From If Statement | Excel Discussion (Misc queries) | |||
can i use an IF Statement to run a macro? | Excel Worksheet Functions | |||
Macro and If Statement | Excel Discussion (Misc queries) | |||
If-statement in Macro | Excel Programming |