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

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

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


.

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