Range With Variables

[m5u4r3p2h1y]

Hi,
Here's my code:

Sub ref()

Dim RefNumber As String
Dim RefFound As Range
Dim LastRow As Long

Workbooks("2009 Hourly by res.xlsx").Sheets("Jan-Feb").Activate

With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

RefNumber = Application.InputBox("Reference #", "Meter Point Referenc
Number")

Set RefFound = Cells.Find(What:=RefNumber, After:=ActiveCell
LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext
MatchCase:= _
False, SearchFormat:=False)

Range(Cells(5, RefFound), Cells(LastRow, RefFound)).Select

MsgBox "Found Ref # at column" & RefFound.Column
MsgBox "and last row at" & LastRow
End Sub
----------------------------------------
When i run this code I get a runtime error 1004 (only happens with m
range line in)

LastRow will be the final row and RefFound.Column is the column for m
range.

If anyone could help with this I'm having a hard time creating a selec
range with my variables.

Thanks,
Chri

 

[Don Guillett]

Hi,
Here's my code:

Sub ref()

Dim RefNumber As String
Dim RefFound As Range
Dim LastRow As Long

Workbooks("2009 Hourly by res.xlsx").Sheets("Jan-Feb").Activate

With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

RefNumber = Application.InputBox("Reference #", "Meter Point Reference
Number")

Set RefFound = Cells.Find(What:=RefNumber, After:=ActiveCell,
LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False)

Range(Cells(5, RefFound), Cells(LastRow, RefFound)).Select

MsgBox "Found Ref # at column" & RefFound.Column
MsgBox "and last row at" & LastRow
End Sub
----------------------------------------
When i run this code I get a runtime error 1004 (only happens with my
range line in)

LastRow will be the final row and RefFound.Column is the column for my
range.

If anyone could help with this I'm having a hard time creating a select
range with my variables.

Thanks,
Chris

Several errors.
Option Explicit

Sub testthis()
Dim lastrow As Long
Dim refnumber As Long
Dim mf As Range
Dim reffound As Long

Workbooks("2009 Hourly by res.xlsx").Sheets("Jan-Feb").Activate
'with not needed on activesheet
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
refnumber = Application.InputBox("Reference #", _
"Meter Point Reference Number ")
Set mf = Cells.Find(What:=refnumber, _
LookIn:=xlFormulas, LookAt:=xlWhole, after:=Range("a1"), _
SearchOrder:=xlByColumns, SearchDirection:=xlNext)
If Not mf Is Nothing Then
reffound = mf.Column
MsgBox reffound
'what to do after selection. Selects usually NOT needed.
Range(Cells(5, reffound), Cells(lastrow, reffound)).Select
Else
End If
End Sub