Finding Number on Col G and put an "X" in Col M in the same ROW

[Turbo68]

I need to see if there is a way to this:

Cell A1 I can enter a 4 digit number.

Click a button to run a macro that will go to another sheet in my workbook, Look in Col G ( there are hundreds of numbers) and locate that certain number on the ROW and on that same ROW place and "X" in col M so it can be checked off so to speak.

I don't thing V lookup is the key but it seems that I think this can be done.

Thanks in advance

Mike

 

[JackDanIce]

What is the name of the sheet you enter the 4 digit number into A1 called?
What is the name of the sheet to search column G in?

Change Data to the name of the sheet to search in and try:

Sub M1()

    Dim LR  As Long
    Dim x   As Long
    Dim v   As Variant

    v = InputBox("Enter 4-digit number: ")
    If Not (IsNumeric(v)) Or Val(v) <> Int(v) And Len(v) <> 4 Then
        MsgBox "Value: " & v & vbCrLf & vbCrLf & "Not found or invalid entry!", vbExclamation, "Value Not Found"
        Exit Sub
    End If
    
    With Sheets("Data")
        LR = .Cells(.Rows.count, 7).End(xlUp).row
        With .Cells(1, 7).Resize(LR)
            On Error Resume Next
            x = .find(what:=v, LookIn:=xlValues, LookAt:=xlWhole).row
            On Error GoTo 0
        End With
        If x > 0 Then .Cells(x, 13).Value = "x"
    End With
    
End Sub

 

[Turbo68]

What is the name of the sheet you enter the 4 digit number into A1 called? INPUT
What is the name of the sheet to search column G in? Cradlepoints

Thank you for the assitance!

 

[JackDanIce]

Try:

Sub M1()

    Dim LR  As Long
    Dim x   As Long
    Dim v   As Variant
    
    With Sheets("Cradlepoints")
        v = InputBox("Enter 4-digit number: ")
        If Not (IsNumeric(v)) Or Val(v) <> Int(v) And Len(v) <> 4 Then
            MsgBox "Value: " & v & vbCrLf & vbCrLf & "Not found or invalid entry!", vbExclamation, "Value Not Found"
            Exit Sub
        End If
        .Select
        LR = .Cells(.Rows.count, 7).End(xlUp).row
        With .Cells(1, 7).Resize(LR)
            On Error Resume Next
            x = .find(what:=v, LookIn:=xlValues, LookAt:=xlWhole).row
            On Error GoTo 0
        End With
        If x > 0 Then
            With .Cells(x, 13)
                .Value = "x"
                .Select
            End With
        End If
    End With
    
End Sub

 

[Turbo68]

Works perfect!

Is there a way for it to reference my number in STORE range name or am i going to have to use the InputBox all the time?

 

[JackDanIce]

STORE range name? It's not clear where that is, what range it refers to or the sheet it is on. Can you be more specific - pretend you're describing the issue to someone who can't see your PC monitor..

 

[Turbo68]

Yeah, i guess you cant see my pc...lol

The 4 digit number is located in cell B15 on sheet INPUT. I have named that cell STORE.

 

[Rick Rothstein]

Cell A1 I can enter a 4 digit number.

Click a button to run a macro that will go to another sheet in my workbook, Look in Col G ( there are hundreds of numbers) and locate that certain number on the ROW and on that same ROW place and "X" in col M so it can be checked off so to speak.

Some questions...

1) What are the names of your two worksheets?

2) Can the number you are searching for appear more than once on the worksheet with all your numbers?

3) If there are any X's from a previous running of the macro... should they remain or be deleted before placing the X's for the new number?

4) The sheet with all the numbers that you are searching in... what is in Column G, constants or formulas?

 

[JackDanIce]

Try:

Sub M1()
    
    Dim arr()   As Variant
    Dim LR          As Long
    Dim x           As Long
    
    With Sheets("INPUT")
        LR = Application.Max(16, .Cells(.Rows.count, 2).End(xlUp).row)
        arr = .Cells(2, 15).Resize(LR - 14).Value
    End With
    
    Application.ScreenUpdating = False
    
    With Sheets("Cradlepoints")
        LR = .Cells(.Rows.count, 7).End(xlUp).row
        For x = LBound(arr, 1) To UBound(arr, 1)
            With .Cells(1, 7).Resize(LR)
                On Error Resume Next
                .find(what:=v, LookIn:=xlValues, LookAt:=xlWhole).Offset(, 6).Value = "x"
                On Error GoTo 0
            End With
        Next x
    End With
    
    Erase arr
    
End Sub

 

[Turbo68]

Some questions...

1) What are the names of your two worksheets? INPUT ( there in cell B17 is my 4 digit number ) Cradlepoints ( the listing of all my numbers to add the "X" to so I can keep track of what I have done)

2) Can the number you are searching for appear more than once on the worksheet with all your numbers? NO

3) If there are any X's from a previous running of the macro... should they remain or be deleted before placing the X's for the new number? The X is going to another cell adjacent to the number found in Cradlepoints. All it will do is let me know that I have done the work for that particular store number.

4) The sheet with all the numbers that you are searching in... what is in Column G, constants or formulas?

G is just my store number listings 4 digit numbers.

Thanks