PDA

View Full Version : Solved: represent column by variable



Yjmmay34
07-29-2010, 11:45 PM
Hi, all. when i use the formula below:


ActiveSheet.Cells(i, 197) = "=if( SUM(IA" & StartRow & ":IA" & i - 1 & ") =0 , 0,(SUMPRODUCT(IA" & StartRow & ":IA" & i - 1 & ",GO" & StartRow & ":GO" & i - 1 & "))/SUM(IA" & StartRow & ":IA" & i - 1 & "))"

i know the" IA " represent the column.But how about if i cannot comfirm the exact column, which means the column is changing based on some condition, like below, variable "x" will represent the column.


x = 13
Do While x <= 27
If range("GO3").Value = Cells(4, x).Value Then
If range("GO4").Value = Cells(5, x).Value Then

then how should i put "x" instead of "IA" to the formula??
Thank you!

Bob Phillips
07-30-2010, 12:50 AM
TRy this



R1C1Part1 = "R" & StartRow & "C" & x & ":R" & i - 1 & "C" & x
R1C1Part2 = "R" & StartRow & "C197" & ":R" & i - 1 & "C197" '197 is column GO
ActiveSheet.Cells(i, 197).FormulaR1C1 = "=IF(SUM(" & R1C1Part1 & ")=0,0,SUMPRODUCT(" & R1C1Part1 & "," & R1C1Part2 & ")/SUM(" & R1C1Part1 & "))"

Yjmmay34
08-02-2010, 01:03 AM
Thank you xld. your code is working perfectly. have a nice day.