VBA for Selecting a Cell From Input Box Text

[FrenchCelt]

Hello,

I'm trying to code a macro that will pop up an input box and then select a range with the text entered into that input box.

For example, if the user types "name1" into the input box, the macro will search for name1 in Column A and select that cell plus the six cells to the right (e.g. if the text is found in A35, the range selected would be A35:G35).

I should be able to code what I need to do next for that worksheet, but then I want to switch to a different worksheet and search Column C for the same text and select the entire row.

Can anyone provide some guidance?

 

[Micron]

Not sure how much help you need. This should find "Red" in column A from row 1 to 50 but as any part of the cell values. That is, John Red will qualify. You can actually select a range that an inputbox will use rather than type it in if that would make a difference. You'd change ("A1:A50") to be the value that was typed into the inputbox. This code is in a standard module, not a sheet module. That way it could work for any sheet.

Dim rng As Range

With ActiveSheet.Range("A1:A50")
    Set rng = .Find("Red", LookIn:=xlValues)
    If Not rng Is Nothing Then
        Range(rng.Address, rng.Offset(0, 6)).Select
    End If
End With

 

[jolivanes]

Change references where required.

Sub Or_So()
    Sheets("Sheet1").Columns(1).Find(Application.InputBox("Find Value", "Enter a value to find in column A.", , , , , , 2)).Resize(, 7).Select
End Sub

 

[FrenchCelt]

Sheets("Sheet1").Columns(1).Find(Application.InputBox("Find Value", "Enter a value to find in column A.", , , , , , 2)).Resize(, 7).Select

Thanks, this did exactly what I wanted for the first part. I guess the way it's coded precludes doing the second part, which is to then look for the same text in a different worksheet, but I can probably build a workaround.