Macro to find a variable user defined cell in a worksheet

[patsdavixen]

Hi,

I have a worksheet named "ABC". In row 4 of this worksheet, each cell from G to BG is numbered from 1 onwards and each cell has a unique number. I would like to have a macro ask the user through a pop-up question box which column data should be calculated in. For example, if the user types "1" in the question box, the macro will go to column G which has "1" in cell G4 and will select G5 the cell just below the column the user defined and calculate the formula.

Any help would be appreciated.

Thanks

 

[Andrew Poulsom]

Record a macro while using Find in the user interface. That will give you some VBA code.

 

[patsdavixen]

I tried recording a macro before I posted my question but I do not know how to identify a variable user defined input in row 4 of my worksheet.

If someone could please help me with a code to find variable inputs, that would be very helpful.

Record a macro while using Find in the user interface. That will give you some VBA code.

 

[Andrew Poulsom]

Here you are:

Sub Test()
    Dim What As String
    What = InputBox("Search for?")
    If What = "" Then Exit Sub
    With Range("G4:BG4")
        On Error Resume Next
        .Find(What:=What, After:=.Cells(1, 1), LookIn:=xlFormulas, LookAt _
            :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).Activate
    End With
    If Err <> 0 Then MsgBox What & " not found!"
End Sub

 

[patsdavixen]

Thank you, Andrew. Code that you provide always work perfectly.
Unfortunately my requirement has changed slightly.
Instead of the user having a pop-up message box, is it possible for the macro to pick up the content of a particular cell. Here is what I need. I have one workbook with 20 sheets. One sheet is entitled "Input". In cell I2, the user will update any one of 40 options from a drop down list. Based on the user's selection in cell I2, the macro should go to another sheet entitled "DBC" and search for the user's selection in cell I2 of the "Input" sheet on the "DBC" sheet in row 5 (F5:AQ5).
Once the macro finds the user's selection of I2 in row 5 of the sheet entitled "DBC", it should copy the cell just below the cell in row 5 i.e. the value in row 6 (only that cell, not the entire row). When I record a macro to do this, it records the cell that I use an example. However, the cell will vary based on the input. Any help would be appreciated. Thanks

 

[Andrew Poulsom]

Why can't you use a lookup formula for that?

 

[patsdavixen]

Andrew, you are right.I used a match index formula to solve my question. Thanks again

INDEX('RB'!$E$6:$AP$538,MATCH(A5,'RB'!$A$6:$A$538,0),MATCH(Input!$I$2,'RB'!$E$5:$AP$5,0))