Hi Gretchen,

Where's the full procedure? What worksheets does it use? What's in these
sheets? What does it have to do.

No-one here can make soup of your question, I think. But I want to help you
another way wit a few tips.

- First: break up your code in little pieces/procedures. Name those
pieces with names you recognize.

- Use logic names for you variables, like usein intRow instead of r (int
is for knowing there's in integer in it, Row for knowing it's for storing a
rownumber.)

- Test your procedure in parts. Say you have broken down your full
procedure into 4 subparts. Do this:

Sub DoEverything
' Start with this procedure and test only the first part.
' If it works, remove the ' before the second part, and run this
procedure again
Call Part1
' Call Part2
' Call Part3
' Call Part4
' BTW, I alway use Call the know I call for another procedure

End if

Sub Part1
' Here's something happening
End sub

Sub Part2
' Here's something else happening
End sub

etc.

This way you are likely to find the problem yourself.

Rob


"Gretchen" schreef in bericht
oups.com...
The following code is making me tear my hair out. I hope someone can
help.

I cannot figure out why but the code is stopping with no error after
column "L" for the first case, and after the third formula for the
second.


For p = HeaderRow + 1 To FinalRow
If Rows(p).EntireRow.Hidden = False Then
r = wsReport.Cells(p, 1)
Select Case r
Case 15, 16, 17
s = wsReport.Cells(p, 3).Value
q = Application.Match(s,
wsReport.Range(wsReport.Cells(1, 3), wsReport.Cells(p - 1, 3)), 0)
For n = 4 To 33
If wsReport.Cells(HeaderRow + 1, n).NumberFormat =
"#,##0_);(#,##0)" Then
wsReport.Cells(p, n).FormulaR1C1 = "=IF(R" & q
& "C="""","""",R" & q & "C*PYUSD)"
Else
wsReport.Cells(p, n).FormulaR1C1 = "=IF(R" & q
& "C="""","""",R" & q & "C)"
End If
Next
For n = 34 To FinalCol - 2
If wsReport.Cells(HeaderRow + 1, n).NumberFormat =
"#,##0_);(#,##0)" Then
wsReport.Cells(p, n).FormulaR1C1 = "=IF(R" & q
& "C="""","""",R" & q & "C*USD)"
Else
wsReport.Cells(p, n).FormulaR1C1 = "=IF(R" & q
& "C="""","""",R" & q & "C)"
End If
Next
Case 4, 7, 10, 13, 19, 21, 27, 29, 31
s = wsReport.Cells(p, 2).Value
q = Application.Match(s,
wsReport.Range(wsReport.Cells(1, 2), wsReport.Cells(p - 1, 2)), 0)
wsReport.Range(wsReport.Cells(p, 4), wsReport.Cells(p,
13)).FormulaR1C1 = "=IF(R" & q & "C="""","""",R" & q & "C)"
wsReport.Range(wsReport.Cells(p, 14),
wsReport.Cells(p, 16)).FormulaR1C1 = "=IF(R" & q & "C[3]="""","""",R"
& q & "C[3])"
wsReport.Range(wsReport.Cells(p, 17),
wsReport.Cells(p, 19)).Value = "NA"
wsReport.Range(wsReport.Cells(p, 20),
wsReport.Cells(p, 22)).FormulaR1C1 = "=IF(R" & q & "C[4]="""","""",R"
& q & "C[4])"
wsReport.Range(wsReport.Cells(p, 23),
wsReport.Cells(p, 25)).Value = "NA"
wsReport.Cells(p, 26).FormulaR1C1 = "=IF(ISERR(RC[-2]/
RC[-3]),"""",ROUND(RC[-2]/RC[-3],2))"
wsReport.Range(wsReport.Cells(p, 27),
wsReport.Cells(p, 29)).FormulaR1C1 = "=IF(R" & q & "C[3]="""","""",R"
& q & "C[3])"
wsReport.Range(wsReport.Cells(p, 30),
wsReport.Cells(p, 32)).Value = "NA"
wsReport.Cells(p, 33).FormulaR1C1 = "=IF(ISERR(RC[-2]/
RC[-3]),"""",ROUND(RC[-2]/RC[-3],2))"
wsReport.Cells(p, 34).FormulaR1C1 = "=IF(R" & q &
"C[10]="""","""",R" & q & "C[10])"
wsReport.Range(wsReport.Cells(p, 35),
wsReport.Cells(p, 37)).FormulaR1C1 = "=IF(R" & q & "C="""","""",R" & q
& "C)"
wsReport.Cells(p, 38).FormulaR1C1 = "=IF(R" & q &
"C[7]="""","""",R" & q & "C[7])"
wsReport.Cells(p, 39).FormulaR1C1 = "=IF(ISERR(RC[-1]/
RC[-5]),"""",ROUND(RC[-1]/RC[-5],2))"
wsReport.Range(wsReport.Cells(p, 40),
wsReport.Cells(p, 41)).FormulaR1C1 = "=IF(R" & q & "C[6]="""","""",R"
& q & "C[6])"
wsReport.Range(wsReport.Cells(p, 42),
wsReport.Cells(p, 43)).FormulaR1C1 = "=IF(R" & q & "C="""","""",R" & q
& "C)"
wsReport.Range(wsReport.Cells(p, 44),
wsReport.Cells(p, 47)).Value = "NA"
wsReport.Range(wsReport.Cells(p, 48),
wsReport.Cells(p, 49)).FormulaR1C1 = "=IF(R" & q & "C="""","""",R" & q
& "C)"
End Select
End If
Next