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" <(E-Mail Removed)> schreef in bericht
news:(E-Mail Removed)...
> 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
>
Similar Threads
