Using Input box for all variables in countif vba

Question

I have tried to code a macro where a search is conducted for a specific text. A)The column to search in, B)The column where the result should appear and C) The text for which the search is being conducted All are referred by respective input boxes. The input box for columns to be searched and where output is to be placed should only need the column name,(and not range) indicated by letters (text and so string) as value. For example, if a search in column Y is to be done the input box should only need entry of letter "Y". I have tried various permutations, but could not replace Y2:Y&LastRow in the code below, so that it refers to the input from input box for the column to search in.

The code is as follows:-

Sub CountIfAllVariablesFromInputBox()
    Dim LastRow    As Long
    Dim ChkColumn  As String
    'display an input box asking for column
    ChkColumn = InputBox( _
    "Please enter column to check")
    'if no input stop
    ColumnNumber = Columns(ChkColumn).Column
    If Len(ChkColumn) = 0 Then
        MsgBox "No column entered"
        Exit Sub
    End If

    Dim InputColumn    As String
    'display an input box asking for column
    InputColumn = InputBox( _
    "Please enter column to insert results")
    'if no input stop
    If Len(InputColumn) = 0 Then
        MsgBox "No column entered"
        Exit Sub
    End If
'inputbox for text string to search for    
    Dim SuccessKeyWord As String
    SuccessKeyWord = InputBox(Prompt:="Enter KeyWord For Success", _
    Title:="KeyWord For Success", Default:="WOW!!")

    LastRow = Range(ChkColumn & Rows.Count).End(xlUp).Row

    Range(InputColumn & "1").Formula = "=COUNTIF(Range("Y2:Y"&LastRow),""" & SuccessKeyWord & """)"
 End With
End Sub

Googling threw up so many ways to refer to ranges (with cells, cell, variables) that I am overwhelmed, unfortunately I could not get result by any of those.

I would be really thankful of your kind help.

I have posted a screenShot.sometimes I may need to search in column "W" and at others in column "Y". I need that flexibility by using the inputbox.

Screen Shot of the columns

Error after replacing the last line of the code by:-

Range(InputColumn & "1").Formula = "=COUNTIF(Range(""" & ChkColumn & 2 & ":" & ChkColumn & """&LastRow),""" & SuccessKeyWord & """)"

or

Range(InputColumn & "1").Formula = "=COUNTIF(Range(""" & ChkColumn & 2 & ":" & ChkColumn &LastRow & """),""" & SuccessKeyWord & """)"

Note:-

Search in column W

Result in column AA

Text to search WOW!!

Answer 1

Oh, Finally got it working

Sub CountIfAllVariablesFromInputBox()
        Dim LastRow    As Long
        Dim ChkColumn  As String
        Dim InputColumn  As String
        Dim SuccessKeyWord As String
        Dim rng As Range
        'display an input box asking for column
        ChkColumn = Application.InputBox("Please enter column to check")
        'if no input stop
        On Error Resume Next
        ColumnNumber = Columns(ChkColumn).Column
        If Err.Description <> "" Then
            MsgBox "No column entered or Something Error"
            Exit Sub
        End If
        On Error GoTo 0
        'display an input box asking for column
        On Error Resume Next
        InputColumn = Application.InputBox( _
        "Please enter column to insert results")
        'if no input stop
        If Err.Description <> "" Then
            MsgBox "No column entered or Something Error"
            Exit Sub
        End If
        On Error GoTo 0
    'inputbox for text string to search for
        SuccessKeyWord = Application.InputBox(Prompt:="Enter KeyWord For Success", _
        title:="KeyWord For Success", Default:="WOW!!")
        LastRow = Range(ChkColumn & Rows.Count).End(xlUp).Row
       Set rng = Range(ChkColumn & 2 & ":" & ChkColumn & LastRow)
        Range(InputColumn & "1").Value = WorksheetFunction.CountIf(rng, SuccessKeyWord)
    End Sub

Thanks @JvdV @chrisneilsen @user11982798 @Davesexcel

Answer 2

Some modification from your line code:

Sub CountIfAllVariablesFromInputBox()
    Dim LastRow    As Long
    Dim ChkColumn  As String
    'display an input box asking for column
    ChkColumn = InputBox( _
    "Please enter column to check")
    'if no input stop
    On Error Resume Next
    ColumnNumber = Columns(ChkColumn).Column
    If Err.Description <> "" Then
        MsgBox "No column entered or Something Error"
        Exit Sub
    End If
    On Error GoTo 0
    Dim InputColumn  As String
    'display an input box asking for column
    On Error Resume Next
    InputColumn = InputBox( _
    "Please enter column to insert results")
    'if no input stop
    If Err.Description <> "" Then
        MsgBox "No column entered or Something Error"
        Exit Sub
    End If
    On Error GoTo 0


'inputbox for text string to search for
    Dim SuccessKeyWord As String
    SuccessKeyWord = InputBox(Prompt:="Enter KeyWord For Success", _
    Title:="KeyWord For Success", Default:="WOW!!")

    LastRow = Range(ChkColumn & Rows.Count).End(xlUp).Row

    Range(InputColumn & "1").Formula = "=COUNTIF(Range(""" & ChkColumn & 2 & ":" & ChkColumn &LastRow & """),""" & SuccessKeyWord & """)"
 'End With
End Sub

Answer 3

Assumed you want the user to select the columns

Sub CountIfAllVariablesFromInputBox()
    Dim LastRow As Long, Rng As Range
    Dim ChkColumn As Range
    Dim InputColumn As Range
    Dim SuccessKeyWord As String


    'display an input box asking for column
    Set ChkColumn = Application.InputBox("Please enter column to check", Type:=8)
    'if no input stop
    If Len(ChkColumn) = 0 Then
        MsgBox "No column entered"
        Exit Sub
    End If

    ColumnNumber = ChkColumn.Column

    'display an input box asking for column
    Set InputColumn = Application.InputBox( _
                      "Please enter column to insert results", Type:=8)
    'if no input stop
    If InputColumn Is Nothing Then Exit Sub
    'inputbox for text string to search for
    SuccessKeyWord = InputBox(Prompt:="Enter KeyWord For Success", _
                              Title:="KeyWord For Success", Default:="WOW!!")

    LastRow = Cells(Rows.Count, ColumnNumber).End(xlUp).Row
    Set Rng = Range(Cells(1, ColumnNumber), Cells(LastRow, ColumnNumber))
    Cells(1, InputColumn.Column) = "=COUNTIF(" & Rng.Address & ",""" & SuccessKeyWord & """)"

End Sub