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
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