Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Code stops after column "L"
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default Code stops after column "L"
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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 25
Default Code stops after column "L"
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



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro stops with "Large Amount of Data" Message dhstein Excel Discussion (Misc queries) 4 November 12th 08 02:17 AM
How do I change the column heading in Excel to display "A" "B" "C Thai New Users to Excel 1 November 30th 07 08:06 PM
macro stops on "selection.autofilter" line of code Rob Excel Programming 5 December 10th 05 02:50 PM
Please add a "sheet" function like "row" and "column" functions Spreadsheet Monkey Excel Programming 2 November 8th 05 04:08 PM
How do I split "A1B2" into "A1" and "B2" using text to column fun. Jennifer Excel Programming 1 February 2nd 05 10:01 PM


All times are GMT +1. The time now is 12:15 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement