Where's Waldo (AKA the Problem)

[gheyman]

What my code is supose to do is go down column E starting at row 9. If the value in Row E is "No" then it puts the first formula in column G. If Row E does not equal "No" then it puts the second formula in column G.

When I run it I dont get either formula in column G

Sub PriceSumFormula1()
'Put one of these formulas in Column G depending on value (Yes or No) in column E
'
Dim p As Long
Dim LastRow As Long

For p = 5 To LastRow
'
If Sheets("Pricing Summary").Cells(p, 5).Value = "No" Then
Sheets("Pricing Summary").Cells(p, 7).Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(SUMIF(INDIRECT(""A""&ROW()&"":A""&IF(COUNTIF(R[1]C1:R1003C1,RC1),MATCH(RC1,R[1]C1:R1003C1,0)+ROW(RC1),1003)),RC1+1,INDIRECT(""G""&ROW()&"":G""&IF(COUNTIF(R[1]C1:R1003C1,RC1),MATCH(RC1,R[1]C1:R1003C1,0)+ROW(RC1),1003)))),"""",SUMIF(INDIRECT(""A""&ROW()&"":A""&IF(COUNTIF(R[1]C1:R1003C1,RC1),MATCH(RC1,R[1]C1:R1003C1,0)+ROW(RC1),1003)),RC1+1,INDIRECT(""G""&ROW()&"":G""&IF(COUNTIF(R[1]C1:R1003C1,RC1),MATCH(RC1,R[1]C1:R1003C1,0)+ROW(RC1),1003))))"
Else
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(INDIRECT(RC2&""!F82"")),"""",INDIRECT(RC2&""!F82""))"
'
'
End If
'
Next p
'
End Sub

 

[shg]

You're not initializing lastrow, so it's zero, so the code in the loop never executes.

 

[gheyman]

Can you elaborate. I am self taught and not that technical with VBA yet.

Thanks

 

[Tuta]

You have to tell Excel how to 1) calculate what the LastRow is or 2) just tell it what number it is

 

[gheyman]

now I see!

Dim LastRow As Long

With Sheets("Pricing Summary")
LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
End With

Thanks for simplifing that. Much appreciated

 

[gheyman]

Still have a problem. I fixed the problem with identifying the LastRow. But the code still doesnt perfrom as I intended. It is running through each cell down column E. But is only putting formulas in some of the cell in column G. Its only putting formulas in where E = No. Even then its not always putting in the correct formula of the two in the code.

If E = No then insert the first formula in the code. If E <> No then put in the second formula.

The code seems to make sense to me.

If Sheets("Pricing Summary").Cells(p, 5).Value = "No" Then
Sheets("Pricing Summary").Cells(p, 7).Select
ActiveCell.FormulaR1C1 = XYZ
Else
ActiveCell.FormulaR1C1 = ABC

WHAT AM I MISSING????????

 

[shg]

Have you tried to step through the code and watch it execute?

Time for some OJT. Take 30 minutes to read http://www.cpearson.com/excel/debug.htm