VBA Excel Scanning Range of Cells and Filling With ComboBox Selection

0
votes

I have a spreadsheet consisting primarily of 12 sheets corresponding to the months Jan-Dec. I am attempting to fill the sheets on specific dates based upon TextBox entry of dates and ComboBox selection of value. Each sheet has 6 weeks in a separate range. I am hoping to scan all ranges for a specific date and fill the 14 cells down from the found value with a value based on the ComboBox Selection. NewYearsHoliday is a String Value in MM/DD/YYYY format that is equivalent to the value cells being scanned. The Cells being scanned are Date Formatted in Excel. The Range corresponds to the cells that show the date. The ComboBox is filled with a DefinedName list of choices based on an OffSet value on another worksheet.

Sub Holiday()

'Select Sheet
    Sheets("Jan").Select
'Scan Cells for Dates Within Range
    For Each Cell In ActiveSheet.Range("B3:H3,B19:H19,B35:H35,B51:H51,B67:H67,B83:H83")
'Set Column Value Based On ComboBox Selection
    If Cell.Value = NewYearsHoliday Then
         Cell.Select
         ActiveCell.Offset(1, 0).Resize(14, 1).Select
         Selection.Value = ComboBoxNewYears.Value
    End If
Next

End Sub

I want to allow the user to specify the date in the TextBox using MM/DD format I will combine that with the Year chosen in another TextBox then they can select a value from the ComboBox and when they choose apply it will fill the corresponding column in the worksheet with the value selected.

I am aware that New Years day doesn’t move but this is proof of concept code that will be duplicated for all the holidays of the year. Can anyone share any input on why my code does not fill as I expect?

1
excelcomboboxvba
what does the Cell.Value return. I reckon it is something like 40235, the way excel stores dates. If so you must convert your NewYearsHoliday to that as well. Step through your code using F9 and mouse over Cell.Value to see its value. – Luuklag
If I create a message box showing the cell value it returns. MM/DD/YYYY ex. Cell B3 returns 1/1/2018. – Victor Losse
Try to see if there ever is a match by putting a messagebox in your if-statement. – Luuklag
Putting the message box in was a huge help. – Victor Losse

1 Answers

0
votes

Thank you for the suggestions guys. I found two issues with my code.

A) My TextBox input was formatted as 01/01 and needed to be 1/1 to match the cells.

B) The separate module didn't have access to the ComboBox.Value so I had to set that to a public variable so it could have access to the String value.

 Sub Holiday()

'Select Sheet
     Sheets("Jan").Select
'Scan Cells for Dates Within Range
     For Each Cell In ActiveSheet.Range("B3:H3,B19:H19,B35:H35,B51:H51,B67:H67,B83:H83")
'Set Column Value Based On ComboBox Select
        If Cell.Value = NewYearsHoliday Then
             Cell.Select
             ActiveCell.Offset(1, 0).Resize(14, 1).Select
             Selection.Value = NewYearsHolidaySelection
        End If
     Next

End Sub

For Reference the NewYearsHoliday and NewYearsHolidaySelection Variables are Strings set by on a CommandButton press based on the values of a TextBox and ComboBox.