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))

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))

modify cell references in a formula programatically
 

=IF(sheet1!C1="R",VLOOKUP(cand!C1,reps,3,FALSE),VL OOKUP(cand!C1,dems,3,FALSE
))

Maybe you modify these two formulas on row 1 and 2 grab both and just copy
down
=VLOOKUP(G1,IF(E1="r",reps,dems),3,FALSE)
=VLOOKUP(INDIRECT("G"&ROW(A1)+6),IF(E2="r",reps,de ms),3,FALSE)


--
Don Guillett
SalesAid Software

"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))