Range With Variables
 

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

Range With Variables
 

On Monday, May 7, 2012 12:35:30 PM UTC-5, m5u4r3p2h1y wrote:
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




--
m5u4r3p2h1y
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