Search a row a dates, identify a start date and end date, merge cells in row from start date to end date

Question

I have searched high and low for examples to guide me in the development of a calendar spreadsheet. The customer has very specific guidelines.

1. First 11 columns contain event details are setup as filters
2. Row 1 is currently blank, but will be used for heading
3. Row 2 from column K to infinity (BXY) is the year
4. Row 3 from column K to BXY is the month
5. Row 4 from column K to BXY is the day

Other notes: Cell K6 has the Date 1-Jul-18. All cells in row 4 continue as =K$6+1..etc. The week is calculated with the formula:

=CONCATENATE("WEEK ", IF((IF(MONTH(K6)>=10, WEEKNUM(K6)-WEEKNUM(DATE(YEAR(K6),10,1))+1, 53-WEEKNUM(DATE(YEAR(K6)-1,10,1))+WEEKNUM(K6)))=53,1,(IF(MONTH(K6)>=10, WEEKNUM(K6)-WEEKNUM(DATE(YEAR(K6),10,1))+1,53-WEEKNUM(DATE(YEAR(K6)-1,10,1))+WEEKNUM(K6)))))

All cells in row 2 are calculated:

=YEAR($K$6)

Now to my dilemma. I am working on a userform for the user to input all data that populates the next empty row in the spreadsheet. I can not post the code that does that, as it is on a proprietary computer system. But, that code that populates the filter range A through J works fine. This code finds the next empty row:

lastRow = ws.Cells.Find(What:="*", LookIn=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1

As part of the form the user inputs the start date and end date of an event. I am trying to search row 4 for the start date and end date, merge all cells between the start date and end date and insert the event title into the merged cell.

This is my code so far for searching Row 4:

For Each eventDate In .Range("K$4:BXY$4)
If IsDate(eventDate.Value) = IsDate(Me.tbStartDate.Value) Then
.Cells(lastRow, eventDate.Address(RowAbsolute:=True, ColumnAbsolute:=False)).Value = Me.tbEventName.Value
End If
Next eventDate

I am new to excel programming, and really only a hobbyist programmer. I was given this task at work and have been reading and researching examples for what I am trying to do...to no avail.

I am looking at modifying this snippet to work:

For iCounter = myLastRow To myFirstRow Step -1
If .Cells(iCounter, myCriteriaColumn).Value = myCriteria Then 
.Range(.Cells(iCounter, myFirstColumn), .Cells(iCounter, myLastColumn)).Merge
Next iCounter

with this:

LastCol = sh.Cells.Find(What:="*", After:=sh.Range("A1"), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False).Column

Any assistance and guidance on how to accomplish this will be much appreciated.

v/r Dusty

Answer 1

I figured it out, probably not the best solution, but it works:

lastRow = .Cells.Find(What:="*",_
    LookIn:=xlFormulas,_
    LookAt:=xlPart,_
    SearchOrder:=xlByRows,_
    SearchDirection:=xlPrevious).Row + 1
eventName = Me.tbID.Value + " " +_
    Me.tbOpName + " " +_
    Me.tbStartDate.Value + "-" +_
    Me.tbEndDate.Value
startDate = Format(Me.tbStartDate.Value, "dd-mmm-yyyy;@")
endDate = Format(Me.tbEndDate.Value, "dd-mmm-yyyy;@")

For startCol = 14 to 959
    startDateColumn = Format(.Cells(6, startCol).Value, "dd-mmm-yyyy;@")

    If StrComp(startDate, startDateColumn, vbTextCompare) = 0 Then
        For endCol = 14 to 959
            endDateColumn = Format(.Cells(6, endCol).Value, "dd-mmm-yyyy;@")
            If StrComp(endDate, endDateColumn, vbTextCompare) = 0 Then
                .Range(.Cells(lastRow, startCol), .Cells(lastRow, endCol)).Merge
                .Cells(lastRow, startCol).Value = eventName
                Exit For
            End If
        Next endCol
     End If
Next startCol