Rang offset question

[itsupport]

Hi

I have a userform that adds data in an offset(1). value method based on 3 criteria on the form (a "Task" combobox, a ""Notes" textbox and a Now() statement.

The task, notes and timestamp are added to the sheet in sequence - this works fine.

My simple but annoying issue is thus...if someone adds a "Task" but doesnt add "Notes" it leaves a blank cell in the sequence.
The next "Task" update with "Notes" added will populate the "Task" in the right place, but the "Notes" in the previous line.

Is there a way to allow users not to add notes with the offset staying in line with the "task" entry?

Code below:


Range("A10000").End(xlUp).Offset(1).Value = ComboBox1.Value
Range("b10000").End(xlUp).Offset(1).Value = TextBox1.Value
Range("c10000").End(xlUp).Offset(1).Value = Now()

MsgBox "Your entry has been recorded.", vbInformation + vbOKOnly, "Spreadsheet update"
TextBox1.Value = ""
UserForm1.Hide

 

[Colin Legg]

Hi,

One way would be to get a reference to the first free cell in column A, then to use that as a reference point to offset across the row.

    Dim rngFreeCell As Range
    
    Set rngFreeCell = Range("A10000").End(xlUp).Offset(1)
    
    rngFreeCell.Value = ComboBox1.Value
    rngFreeCell.Offset(, 1).Value = TextBox1.Value
    rngFreeCell.Offset(, 2).Value = Now()

 

[itsupport]

Genius!

Thanks so much - it worked perfectly.