modify cell references in a formula programatically

  • Thread starter Thread starter ralphehowardjr
  • Start date Start date
R

ralphehowardjr

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),VLOOKUP(cand!C1,dems,3,FALSE))

=IF(sheet1!C2="R",VLOOKUP(cand!C7,reps,3,FALSE),VLOOKUP(cand!C7,dems,3,FALSE))

=IF(sheet1!C3="R",VLOOKUP(cand!C13,reps,3,FALSE),VLOOKUP(cand!C13,dems,3,FALSE))

=IF(sheet1!C4="R",VLOOKUP(cand!C20,reps,3,FALSE),VLOOKUP(cand!C20,dems,3,FALSE))
 
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 said:
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),VLOOKUP(cand!C1,dems,3,FALSE
))
=IF(sheet1!C2="R",VLOOKUP(cand!C7,reps,3,FALSE),VLOOKUP(cand!C7,dems,3,FALSE
))
=IF(sheet1!C3="R",VLOOKUP(cand!C13,reps,3,FALSE),VLOOKUP(cand!C13,dems,3,FAL
SE))
4
Click to expand...
=IF(sheet1!C4="R",VLOOKUP(cand!C20,reps,3,FALSE),VLOOKUP(cand!C20,dems,3,FAL
SE))
 
=IF(sheet1!C1="R",VLOOKUP(cand!C1,reps,3,FALSE),VLOOKUP(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,dems),3,FALSE)


--
Don Guillett
SalesAid Software
(e-mail address removed)
ralphehowardjr said:
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),VLOOKUP(cand!C1,dems,3,FALSE
))
=IF(sheet1!C2="R",VLOOKUP(cand!C7,reps,3,FALSE),VLOOKUP(cand!C7,dems,3,FALSE
))
=IF(sheet1!C3="R",VLOOKUP(cand!C13,reps,3,FALSE),VLOOKUP(cand!C13,dems,3,FAL
SE))
4
Click to expand...
=IF(sheet1!C4="R",VLOOKUP(cand!C20,reps,3,FALSE),VLOOKUP(cand!C20,dems,3,FAL
SE))
 
Back
Top