ByRef question
 

What's wrong with this code? I'm getting a ByRef type mismatch error.

Sub SetRange()
Dim topCel As Range, bottomCel As Range
Set topCel = ActiveCell
If IsEmpty(topCel) Then Set topCel = topCel.End(xlDown)
Set bottomCel = Cells(65536, topCel.Column).End(xlUp)
If bottomCel.Row < topCel.Row Then Set topCel = bottomCel
Set myRng = Range(topCel, bottomCel)
myRng.NumberFormat = "@"
Call FormatCells(myRng)
End Sub


Sub FormatCells(ByRef myRng As Range)
For Each cel In myRng
If cel.Value < 1000 Then
cel.Value = "0" & Left(cel, 1) & ":00"
Else
cel.Value = Left(cel, 2) & ":00"
End If
Next
End Sub

ByRef question
 

Tommy,

Declare the myRng variable as a range. E.g.,
Dim myRng As Range


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Tommy Flynn" wrote in message
...
What's wrong with this code? I'm getting a ByRef type mismatch error.

Sub SetRange()
Dim topCel As Range, bottomCel As Range
Set topCel = ActiveCell
If IsEmpty(topCel) Then Set topCel = topCel.End(xlDown)
Set bottomCel = Cells(65536, topCel.Column).End(xlUp)
If bottomCel.Row < topCel.Row Then Set topCel = bottomCel
Set myRng = Range(topCel, bottomCel)
myRng.NumberFormat = "@"
Call FormatCells(myRng)
End Sub


Sub FormatCells(ByRef myRng As Range)
For Each cel In myRng
If cel.Value < 1000 Then
cel.Value = "0" & Left(cel, 1) & ":00"
Else
cel.Value = Left(cel, 2) & ":00"
End If
Next
End Sub

ByRef question
 

Thanks, that did the trick.

Tommy


"Chip Pearson" wrote in message
...
Tommy,

Declare the myRng variable as a range. E.g.,
Dim myRng As Range


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Tommy Flynn" wrote in message
...
What's wrong with this code? I'm getting a ByRef type mismatch error.

Sub SetRange()
Dim topCel As Range, bottomCel As Range
Set topCel = ActiveCell
If IsEmpty(topCel) Then Set topCel = topCel.End(xlDown)
Set bottomCel = Cells(65536, topCel.Column).End(xlUp)
If bottomCel.Row < topCel.Row Then Set topCel = bottomCel
Set myRng = Range(topCel, bottomCel)
myRng.NumberFormat = "@"
Call FormatCells(myRng)
End Sub


Sub FormatCells(ByRef myRng As Range)
For Each cel In myRng
If cel.Value < 1000 Then
cel.Value = "0" & Left(cel, 1) & ":00"
Else
cel.Value = Left(cel, 2) & ":00"
End If
Next
End Sub