Code stops after column "L"

G

Gretchen

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
 
R

Rob

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 said:
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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

One line is killing my program! 3
Scrabble Value calculation for Welsh words 0
Autofill is overwriting the column title..... 4
Auto-calculate after a cell is filled in - Macro is looping!!! 2
Auto-calculate after a cell is filled in - macro shouldn't be loop 5
Macro autofills formula when a cell value is changed 4
help plz in sorting out the error in problem 2
Run Time Error"13".. Mismatch..help plz 2

Top