Coding for offset cell references with multiple row headings with merged cells

Hi

I understand how to code to specify a cell in which to enter data into a spreadhseet, when the heading contains only one row.

In the attached example the headings contain multiple rows with merged cells, is there any way of overcoming this so that the entries are placed in the correct cells?

Many thanks.

Andy

It'sbest to avoid Merged Cells completely. Your code would be easier if you didn't have empty columns as well

Change your Submit code to this

Private Sub cmbSubmit_Click()
    Dim NextRw As Long
    'Write data to worksheet
    If Me.cboOvertimeType.Value = "" Then
        MsgBox "Please select the type of Overtime worked.", vbExclamation, "Overtime Form"
        Me.cboOvertimeType.SetFocus
    End If

    With Worksheets("Overtime")
     NextRw = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
        .Cells(NextRw, 1).Value = Me.RosStartDatePicker.Value
        .Cells(NextRw, 2).Value = TimeValue(Me.RosStartTimePicker.Value)
        .Cells(NextRw, 3).Value = Me.RosEndDatePicker.Value
        .Cells(NextRw, 4).Value = TimeValue(Me.RosEndTimePicker.Value)
        .Cells(NextRw, 6).Value = Me.ActStartDatePicker.Value
        .Cells(NextRw, 7).Value = TimeValue(Me.ActStartTimePicker.Value)
        .Cells(NextRw, 8).Value = Me.ActEndDatePicker.Value
        .Cells(NextRw, 9).Value = TimeValue(Me.ActEndTimePicker.Value)
        .Cells(NextRw, 12).Value = Me.cboOvertimeType.Value
    End With

    Unload Me

End Sub

Hello Andy,

I have your workbook open. Can you give me an example of what the problem is?

Have you checked the code that I posted?

Hello AndyE,

I revised your macro after I figured out what you wanted to do. This macro has been added to the user form in the attached workbook.

Private Sub cmbSubmit_Click()
  
  Dim Rng As Range
  Dim RngEnd As Range
  Dim Wks As Worksheet
  
    Set Wks = Worksheets("Overtime")

   'First entry starts at "A4"
    Set Rng = Wks.Range("A4")
    
   'Find the last entry in column "A"
    Set RngEnd = Wks.Cells(Rows.Count, Rng.Column).End(xlUp)
    
   'If last entry is below "A4" then use row 4. Otherwise, use the last entry row + 1
    Set Rng = IIf(RngEnd.Row < Rng.Row, Rng, RngEnd.Offset(1, 0))
    
   'Make the range a single row of 12 columns
    Set Rng = Rng.Resize(ColumnSize:=12)
    
   'Write data to worksheet
    If Me.cboOvertimeType.Value = "" Then
       MsgBox "Please select the type of Overtime worked.", vbExclamation, "Overtime Form"
       Me.cboOvertimeType.SetFocus
    End If

      With Rng
        .Item(1).Value = RosStartDatePicker.Value
        .Item(2).Value = VBA.Format(RosStartTimePicker.Value, "hh:mm:ss")
        .Item(3).Value = RosEndDatePicker.Value
        .Item(4).Value = VBA.Format(RosEndTimePicker.Value, "hh:mm:ss")
        .Item(6).Value = ActStartDatePicker.Value
        .Item(7).Value = VBA.Format(ActStartTimePicker.Value, "hh:mm:ss")
        .Item(8).Value = ActEndDatePicker.Value
        .Item(9).Value = VBA.Format(ActEndTimePicker.Value, "hh:mm:ss")
        .Item(12).Value = cboOvertimeType.Value
      End With

    Unload Me

End Sub

Originally Posted by AndyE

I figuered out the starting point in Row 18...just me being addtitionally thick....

What was it?

Originally Posted by AndyE

Yes I have Roy, sorry I replied for the benefit of Leigh & hadn't read the code at that time.

I've tried it and it works a peach except that it begins entering the data on Row 18 for some reason.....any ideas?

It shouldn't,it posted to Row 4 in your example

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save