Solved: Insert row above next empty cell within a column

[Learner123]

Hi everyone,

I am having a serious problem with one of my codes. Its holding up my entire project and my boss is growing impatient.

Any help will be appreciated!!!

I am trying to develop a code that looks up a specific value ('ComboBox2'). It should then move over one column and down one row (offset (1,1)). At this point I need the code to search downward for the first empty cell and insert a row above it.

The code that I have is:

[vba]
Private Sub Update_Click()
Dim RowCount As Long
Dim c As Range

Set c = Columns(1).Find(ComboBox2.Value, lookat:=xlWhole)
If c.Offset(1, 2) = "" Then
Set c = c.Offset(1, 1)
Else
'''''' insert row after
Set c = c.End(xlDown)(2)
c.Offset(1).EntireRow.Insert
End If

c.Offset(, 1) = Me.TextBox1a
c.Offset(, 4) = Me.TextBox2a
c.Offset(, 7) = Me.TextBox3a
c(2).EntireRow.Insert
End Sub[/vba]

I welcome any level of guidance.

Thanks in advance for your time!!!

[mancubus]

......
deleted. double post.

[mancubus]

hi.

not sure. perhaps...

[vba]Sub Update_Cl()
Dim RowCount As Long
Dim c As Range

Set c = Columns(1).Find(ComboBox2.Value, lookat:=xlWhole)

If Not c Is Nothing Then
Set c = c.Offset(1, 1)
If c.Offset(1, 0) <> "" Then
Set c = c.End(xlDown)
End If
c.Offset(1, 0).EntireRow.Insert
c.Offset(, 1) = Me.TextBox1a
c.Offset(, 4) = Me.TextBox2a
c.Offset(, 7) = Me.TextBox3a
c(2).EntireRow.Insert
Else
MsgBox "Search item not found!", vbCritical
Exit Sub
End If
End Sub
[/vba]
ps: not tested. work on a backup.

[Learner123]

Mancubus - Thank you very much for your time.

I am still not able to figure out how to correct the code. I have provided an attachment with your suggested code applied.

If possible, can you please pin point where my error lies.

I am very greatful for any assistance.

Thanks again!

[mancubus]

hi.
test the below code on a backup of your file.
i added msgbox to display the range address.
activate sht GC. hit Alt+F8 for macros then doubleclick "update" to see the ranges change in desired order. if stg goes wrong, adjust the "set c =" and "entirerow.insert" offset values.
test for, eg, combobox2.Value = 5 and 9.

and last:
c(2).EntireRow.Insert
do you really want to insert 2 additional rows after last c?

[VBA]
Set c = Columns(1).Find(ComboBox2.Value, lookat:=xlWhole)

If Not c Is Nothing Then
MsgBox c.Address 'delete this line after test
Set c = c.Offset(1, 1)
MsgBox c.Address 'delete this line after test
If c.Offset(1, 0) = "" Then
c.Offset(1, 0).EntireRow.Insert
Else
Set c = c.End(xlDown)
MsgBox c.Address 'delete this line after test
c.EntireRow.Insert
End If
c.Offset(, 1) = Me.TextBox1a
c.Offset(, 4) = Me.TextBox2a
c.Offset(, 7) = Me.TextBox3a
c(2).EntireRow.Insert
Else
MsgBox "Search item not found!", vbCritical
Exit Sub
End If
[/VBA]

[Learner123]

Thank you very much!

With you help I wound up with the below code:

[VBA] Set c = Columns(1).Find(ComboBox2.Value, lookat:=xlWhole)

If Not c Is Nothing Then
Set c = c.Offset(1, 1)
c.Offset(0, 0).EntireRow.Insert
c.Offset(-1, 1) = Me.TextBox1a
c.Offset(-1, 4) = Me.TextBox2a
c.Offset(-1, 7) = Me.TextBox3a

Else
MsgBox "Search item not found!", vbCritical
Exit Sub
End If
End Sub[/VBA]

It works great! Much appreciated!!!

[mancubus]

glad it helped.