Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 Macro/VB Question DDE Question | Excel Worksheet Functions | |||
where can I see my question and answer? Yesterday I ask a question | Excel Discussion (Misc queries) | |||
Newbie Question - Subtraction Formula Question | Excel Discussion (Misc queries) | |||
ByRef not passing address | Excel Programming | |||
Is ByVal always better if ByRef isn't necessary | Excel Programming |