Help- Translating entry in input box?

[jmthompson]

Good morning,
I've searched the boards, but haven't been able to find my answer.

I am beginning my first macro with input boxes. My input box is going to ask the user to enter a month. The user will enter text like January-08. I already have the code to create this Input box.

I want the macro to take that answer and place data in the active row in the column with that header. I am fuzzy on how to do this.

Now, how do I get the macro to match the entered text with the correct column? I will make sure that the user's entry is always formatted the same way as the column headers.

Also, the number of columns in this spreadsheet will continue to grow, so I will need to build in a reference to the last column into any code.

I appreciate any guidance you can lend

 

[xenou]

The simple answer to your question might be something like:

Sub MatchIt()
'Matches a string value from an input box _
    to the text value (value as displayed) in header row

Dim LCol As Long
Dim rsp As String
Dim y As Long
Const lngHeaderRow = 1 'Headers in row 1

    LCol = Cells(lngHeaderRow, 1).End(xlToRight).Column
    
    rsp = InputBox("Enter a date like January-08")
    
    For y = Cells(lngHeaderRow, 1).End(xlToRight).Column To 1 Step -1
        If Cells(lngHeaderRow, y).Text = rsp Then
            '~do something in y column
        End If
    Next y

End Sub

I have a tendency to put any hard values (such as the location of my header row) into constants, so if this changes I only need to change the value of the constant - in case you are wondering about the constant declaration. it's hard to tell if your concern with formatting means you have real dates in the header row or not. I find it easier to always use real dates no matter where or what - then your user can enter a regular date (i.e., the last day of a given month, if data is organized by months). At that point, you only need to see if the dates are the same. Perhaps you could also return a message if no match is found...

Sub MatchDates()
'This works if real dates are in headers and user enters a valid date _
    in the inputbox
'Uses Brian Baulsom's custom function as posted at MrExcel.com

Dim myDate As Date
Dim y As Long
Const lngHeaderRow As Long = 1 'Headers in row 1
    
    myDate = GetDateFromInputBox()
    If myDate = 0 Then GoTo Handler
    
    y = Cells(lngHeaderRow, 1).End(xlToRight).Column
    Do While y > 0
        If Cells(lngHeaderRow, y).Value = myDate Then
            Exit Do
        End If
        y = y - 1
    Loop
    
    If y = 0 Then
        MsgBox "No match found!"
        GoTo Handler
    Else
        MsgBox "Do Something in column " & y & " !!"
    End If
        
Handler:
End Sub
'---------------------------------------------------------------
Public Function GetDateFromInputBox() As String
    '- MACRO TO GET DATE FROM USER IN ANY VALID FORMAT (Inputbox)
    '- Keep showing inputbox until proper date entry or cancel
    '- uses VBA IsDate() function to check
    '- NB. If in a UserForm the 'Calendar Control' is better.
    '- Brian Baulsom
    
    Dim strDateString As String
    Dim dtmRealDate As Date
    Dim EntryOK As Boolean
    '----------------------------------------------------------
    '- keep showing inputbox until proper date or cancel
    EntryOK = False
    
    Do While EntryOK = False
        strDateString = InputBox("Please enter date", "DATE ENTRY")
        
        '- Cancel button -> exit
        If strDateString = "" Then
            GetDateFromInputBox = 0
            Exit Function
        End If
        
        '- check date
        If IsDate(strDateString) Then
            EntryOK = True
        Else
            '- error message
            MsgBox ("That is not a valid date format." & vbCr _
                & "Please try again or Cancel.")
        End If
    
    Loop
    
    GetDateFromInputBox = strDateString
    
End Function

HTH