Cells.find

[John Davis]

Hello All:

I have the following code snipit:

Dim X As Variant

X = Inputbox(--CM21158--)
'
Columns("A:A").Select
    Cells.Find(What:="&X&", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlDown).Activate

How can I use the find method to activate the cell with CM21158 in it?

 

[VoG]

Try

Dim X As Variant
Dim Found As Range
X = InputBox("Enter Code")
'
Set Found = Columns("A").Find(What:=X, LookIn:=xlValues, LookAt:=xlPart)
If Not Found Is Nothing Then Found.Select

 

[jbeaucaire]

Change these two parameters

What:=X
LookAt:=xlPart

 

[John Davis]

Try

Dim X As Variant
Dim Found As Range
X = InputBox("Enter Code")
'
Set Found = Columns("A").Find(What:=X, LookIn:=xlValues, LookAt:=xlPart)
If Not Found Is Nothing Then Found.Select

Thanks for your reply Peter. This almost works except that it is copying not only the entered Code but other Codes as well? But much more closer that what I had previously? Can you tell me what may be causing the problem here is the entire code.

Sub Summarize()
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim X As Variant
Dim Found As Range

X = InputBox("Please Enter Customer Number to Search")
    
Worksheets.Add.Name = "Consolidate"

Set wbBook = ThisWorkbook

For Each wsSheet In wbBook.Sheets

If wsSheet.Name <> "Consolidate" Then wsSheet.Activate

Set Found = Columns("A").Find(What:=X, LookIn:=xlValues, LookAt:=xlPart)
If Not Found Is Nothing Then Found.Select

    ActiveCell.EntireRow.Copy Sheets("Consolidate").Range("A" & Rows.Count).End(xlUp)(2)


Next wsSheet



End Sub

 

[John Davis]

Change these two parameters

What:=X
LookAt:=xlPart

Thanks for your reply jbeaucaire. In this case X is a variable that I need to find, and it occupied the entire cell. So I'm not sure, how I'd change those.

 

[VoG]

If you are entering the entire code to search for then try

Sub Summarize()
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim X As Variant
Dim Found As Range

X = InputBox("Please Enter Customer Number to Search")

Worksheets.Add.Name = "Consolidate"

Set wbBook = ThisWorkbook

For Each wsSheet In wbBook.Sheets

    If wsSheet.Name <> "Consolidate" Then
        wsSheet.Activate
        
        Set Found = Columns("A").Find(What:=X, LookIn:=xlValues, LookAt:=xlWhole)
        If Not Found Is Nothing Then Found.EntireRow.Copy Sheets("Consolidate").Range("A" & Rows.Count).End(xlUp)(2)
    End If

Next wsSheet



End Sub

 

[John Davis]

Change these two parameters

What:=X
LookAt:=xlPart

Sorry, I just realized that my example was not clear.

 

[John Davis]

If you are entering the entire code to search for then try

Sub Summarize()
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim X As Variant
Dim Found As Range

X = InputBox("Please Enter Customer Number to Search")

Worksheets.Add.Name = "Consolidate"

Set wbBook = ThisWorkbook

For Each wsSheet In wbBook.Sheets

    If wsSheet.Name <> "Consolidate" Then
        wsSheet.Activate
        
        Set Found = Columns("A").Find(What:=X, LookIn:=xlValues, LookAt:=xlWhole)
        If Not Found Is Nothing Then Found.EntireRow.Copy Sheets("Consolidate").Range("A" & Rows.Count).End(xlUp)(2)
    End If

Next wsSheet



End Sub

Super!!! Thanks alot for your observations and help.