VBA find

Hi everyone.
Wondering if any one can help. The following code below is used in a large excel spreadsheet with a ton of numbers. in the top row (1) are reference numbers which at this preliminary stage in my coding I am just trying to find. (the reference numbers are in the sheet like this ex. 100,040 I hope the comma is not a big deal)

Code:
Sub ref()

Dim RefNumber As Long
Dim RefFound As Range
RefNumber = Application.InputBox("Reference #", "Meter Point Reference_ Number", , , , , , 4)

Set RefFound = Cells.Find(what:=RefNumber, *REALLY DONT KNOW WHAT_ TO PUT HERE", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows,_ SearchDirection:=xlNext)

MsgBox "Found Reference # at column" & RefFound.Column

End Sub

I had gotten it to run but for every reference number I tried it gave me a column of 238 (even when i changed the reference number to a different corresponding column). Any help would be greatly appreciated.

[For those wondering my goal is: find reference number, find last row with data in the column, copy range, paste into new sheet.]

Originally Posted by m5u4r3p2h1y

Hi everyone.
Wondering if any one can help. The following code below is used in a large excel spreadsheet with a ton of numbers. in the top row (1) are reference numbers which at this preliminary stage in my coding I am just trying to find. (the reference numbers are in the sheet like this ex. 100,040 I hope the comma is not a big deal)

Code:
Sub ref()

Dim RefNumber As Long
Dim RefFound As Range
RefNumber = Application.InputBox("Reference #", "Meter Point Reference_ Number", , , , , , 4)

Set RefFound = Cells.Find(what:=RefNumber, *REALLY DONT KNOW WHAT_ TO PUT HERE", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows,_ SearchDirection:=xlNext)

MsgBox "Found Reference # at column" & RefFound.Column

End Sub

I had gotten it to run but for every reference number I tried it gave me a column of 238 (even when i changed the reference number to a different corresponding column). Any help would be greatly appreciated.

[For those wondering my goal is: find reference number, find last row with data in the column, copy range, paste into new sheet.]

New Problem:

code:
Sub ref()

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

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

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

Range("A1").EntireRow.Select
Selection.Find("RefNumber", ActiveCell, xlValues, xlyColumns, xlNext).Activate

'Set RefFound = Cells.Find(RefNumber, , xlValues, xlPart, xlByRows, xlNext)

'MsgBox "Found Reference # at column" & RefFound.Column

MsgBox "Found Ref # at column" & ActiveCell.Column

End Sub

This is giving me a run time error 9 and I am not sure why.

The example value in cell(M1) has in the cell 100,040
I input into the InputBox 100040 and it gives me the error. Any ideas?

m5u4r3p2h1y wrote :
> Hi everyone.
> Wondering if any one can help. The following code below is used in a
> large excel spreadsheet with a ton of numbers. in the top row (1) are
> reference numbers which at this preliminary stage in my coding I am just
> trying to find. (the reference numbers are in the sheet like this ex.
> 100,040 I hope the comma is not a big deal)
>
> Code:
> Sub ref()
>
> Dim RefNumber As Long
> Dim RefFound As Range
> RefNumber = Application.InputBox("Reference #", "Meter Point Reference_
> Number", , , , , , 4)
>
> Set RefFound = Cells.Find(what:=RefNumber, *REALLY DONT KNOW WHAT_ TO
> PUT HERE", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows,_
> SearchDirection:=xlNext)
>
> MsgBox "Found Reference # at column" & RefFound.Column
>
> End Sub
>
> I had gotten it to run but for every reference number I tried it gave me
> a column of 238 (even when i changed the reference number to a different
> corresponding column). Any help would be greatly appreciated.
>
> [For those wondering my goal is: find reference number, find last row
> with data in the column, copy range, paste into new sheet.]

You might want to change SearchOrder to search by columns, and LookAt
might be better as 'xlWhole'. Don't enter the comma in the input box if
it's there due to number formatting.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion