Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
modify cell references in a formula programatically
First time post and new to Excel VBA. Using win98/xl97.
I have a spreadsheet that requires a set of similar formulae. The need to be replicated multiple times. Each formula is identical except the logical value cell reference in the IF function i incremented by 1 and the reference to the lookup value cells in th VLOOKUP functions is incremented by 6. Like this: A =IF(sheet1!C1="R",VLOOKUP(cand!C1,reps,3,FALSE),VL OOKUP(cand!C1,dems,3,FALSE)) =IF(sheet1!C2="R",VLOOKUP(cand!C7,reps,3,FALSE),VL OOKUP(cand!C7,dems,3,FALSE)) =IF(sheet1!C3="R",VLOOKUP(cand!C13,reps,3,FALSE),V LOOKUP(cand!C13,dems,3,FALSE)) =IF(sheet1!C4="R",VLOOKUP(cand!C20,reps,3,FALSE),V LOOKUP(cand!C20,dems,3,FALSE)) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
modify cell references in a formula programatically
Ralph,
Is this what you want Dim iRow As Long For iRow = 1 To 4 Cells(iRow, "A").Formula = _ "=IF(sheet1!C" & iRow & "=""R""," & _ "VLOOKUP(cand!C" & iRow & ",reps,3,FALSE)," & _ "VLOOKUP(cand!C" & iRow * 6 + 1 - 6 & ",dems,3,FALSE))" Next iRow -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "ralphehowardjr" wrote in message ... First time post and new to Excel VBA. Using win98/xl97. I have a spreadsheet that requires a set of similar formulae. They need to be replicated multiple times. Each formula is identical, except the logical value cell reference in the IF function is incremented by 1 and the reference to the lookup value cells in the VLOOKUP functions is incremented by 6. Like this: A 1 =IF(sheet1!C1="R",VLOOKUP(cand!C1,reps,3,FALSE),VL OOKUP(cand!C1,dems,3,FALSE )) 2 =IF(sheet1!C2="R",VLOOKUP(cand!C7,reps,3,FALSE),VL OOKUP(cand!C7,dems,3,FALSE )) 3 =IF(sheet1!C3="R",VLOOKUP(cand!C13,reps,3,FALSE),V LOOKUP(cand!C13,dems,3,FAL SE)) 4 =IF(sheet1!C4="R",VLOOKUP(cand!C20,reps,3,FALSE),V LOOKUP(cand!C20,dems,3,FAL SE)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
modify all formula references in multiple cells | Excel Discussion (Misc queries) | |||
How can I programatically move cell contents | Excel Discussion (Misc queries) | |||
Modify an existing Cell formula using VBA | Excel Discussion (Misc queries) | |||
can i access the cell colour programatically? eg if cell is blue | Excel Discussion (Misc queries) | |||
Can a Formula in Cell X modify Cell Y? | Excel Discussion (Misc queries) |