Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code won't run in a subroutine!
Hi folks,
I have some code that works just fine until I place a portion of it in a subroutine...and then it just doesn't do squat! Here's the code that works fine: Dim CellContents As Variant FinalRow = Cells(65536, 1).End(xlUp).Row LastColumn = Cells(1, 255).End(xlToLeft).Column - 2 For i = 2 To FinalRow CellContents = Trim(Cells(i, 2).Value) Select Case CellContents Case 9101010 To 9101011 StartColumn = 4 For j = StartColumn To LastColumn Step 1 If IsNumeric(Cells(i, j)) Then Cells(i, j).Interior.ColorIndex = 40 Cells(i, j).Value = "-" & Cells(i, j).Value Else Cells(i, j).Interior.ColorIndex = 35 Cells(i, j).Value = Left(Cells(i, j).Value, Len (Cells(i, j).Value) - 2) End If Next j Cells(i, 1).Resize(1, 8).Interior.ColorIndex = 4 Case Else End Select Next i End Sub But...when I pull out the stuff from the middle, and put this in a subroutine: StartColumn = 4 For j = StartColumn To LastColumn Step 1 If IsNumeric(Cells(i, j)) Then Cells(i, j).Interior.ColorIndex = 40 Cells(i, j).Value = "-" & Cells(i, j).Value Else Cells(i, j).Interior.ColorIndex = 35 Cells(i, j).Value = Left(Cells(i, j).Value, Len (Cells(i, j).Value) - 2) End If Next j the program no longer works! Suggestions??? Thanks, Craig |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code won't run in a subroutine!
Possibly because the subroutine doesn't know what i and LastColumn are?
-- Vasant "Craig" wrote in message ... Hi folks, I have some code that works just fine until I place a portion of it in a subroutine...and then it just doesn't do squat! Here's the code that works fine: Dim CellContents As Variant FinalRow = Cells(65536, 1).End(xlUp).Row LastColumn = Cells(1, 255).End(xlToLeft).Column - 2 For i = 2 To FinalRow CellContents = Trim(Cells(i, 2).Value) Select Case CellContents Case 9101010 To 9101011 StartColumn = 4 For j = StartColumn To LastColumn Step 1 If IsNumeric(Cells(i, j)) Then Cells(i, j).Interior.ColorIndex = 40 Cells(i, j).Value = "-" & Cells(i, j).Value Else Cells(i, j).Interior.ColorIndex = 35 Cells(i, j).Value = Left(Cells(i, j).Value, Len (Cells(i, j).Value) - 2) End If Next j Cells(i, 1).Resize(1, 8).Interior.ColorIndex = 4 Case Else End Select Next i End Sub But...when I pull out the stuff from the middle, and put this in a subroutine: StartColumn = 4 For j = StartColumn To LastColumn Step 1 If IsNumeric(Cells(i, j)) Then Cells(i, j).Interior.ColorIndex = 40 Cells(i, j).Value = "-" & Cells(i, j).Value Else Cells(i, j).Interior.ColorIndex = 35 Cells(i, j).Value = Left(Cells(i, j).Value, Len (Cells(i, j).Value) - 2) End If Next j the program no longer works! Suggestions??? Thanks, Craig |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code won't run in a subroutine!
O.K., that might be true. Do I just "Dim" both of those in
the Declaration section? Craig -----Original Message----- Possibly because the subroutine doesn't know what i and LastColumn are? -- Vasant "Craig" wrote in message ... Hi folks, I have some code that works just fine until I place a portion of it in a subroutine...and then it just doesn't do squat! Here's the code that works fine: Dim CellContents As Variant FinalRow = Cells(65536, 1).End(xlUp).Row LastColumn = Cells(1, 255).End(xlToLeft).Column - 2 For i = 2 To FinalRow CellContents = Trim(Cells(i, 2).Value) Select Case CellContents Case 9101010 To 9101011 StartColumn = 4 For j = StartColumn To LastColumn Step 1 If IsNumeric(Cells(i, j)) Then Cells(i, j).Interior.ColorIndex = 40 Cells(i, j).Value = "-" & Cells(i, j).Value Else Cells(i, j).Interior.ColorIndex = 35 Cells(i, j).Value = Left(Cells(i, j).Value, Len (Cells(i, j).Value) - 2) End If Next j Cells(i, 1).Resize(1, 8).Interior.ColorIndex = 4 Case Else End Select Next i End Sub But...when I pull out the stuff from the middle, and put this in a subroutine: StartColumn = 4 For j = StartColumn To LastColumn Step 1 If IsNumeric(Cells(i, j)) Then Cells(i, j).Interior.ColorIndex = 40 Cells(i, j).Value = "-" & Cells(i, j).Value Else Cells(i, j).Interior.ColorIndex = 35 Cells(i, j).Value = Left(Cells(i, j).Value, Len (Cells(i, j).Value) - 2) End If Next j the program no longer works! Suggestions??? Thanks, Craig . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code won't run in a subroutine!
Craig
You can do it several different ways The prefered methods is Method 1 follow by Method 2 then Method 3 Method 1 Pass variables from one routine to another routine The following example passes the startcol value to scol in Macro2 Note you can use the same variable names in both macros Sub macro1() Call Macro2(startcol, endcol) End Sub Sub Macro2(scol As Integer, ecol As Integer) Dim j As Integer For j = scol To ecol code here nextj End Sub Method 2 Declare your variable as private at the top of your module sheet as before any macro code. This makes the variable available in every macro on that sheet eg Private Startcol as integer Method 3 Declare your variable as Public at the top of your module sheet as before any macro code. This makes the variable available in every macro on every modul sheets eg PublicStartcol as intege -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Call a subroutine using variable subroutine name | Excel Discussion (Misc queries) | |||
SUBROUTINE HELP | Excel Discussion (Misc queries) | |||
Second subroutine to run automatically | Excel Programming | |||
Code ends subroutine in error | Excel Programming | |||
Every second subroutine | Excel Programming |