Set range is getting blank...

[sakthi santhanam]

Private Sub CommandButton1_Click()
Dim lw As Long
Dim m As Long
Dim rnglast As Range
lw = Sheet1.UsedRange.Rows.Count + 1
Set rnglast = Sheet1.Range("A7:A" & lw).Find(Sheet1.ComboBox1.Value, ActiveCell, xlValues, xlWhole, xlByRows, xlNext, False)
Sheet1.Range("A" & rnglast.Row).EntireRow.Insert
End Sub

Here the error occurs Object variable or without block variable not set....

I tried this code to check rnglast gets any value or not....

if not rnglast is nothing then
  msgbox "True"
else
   msgbox "False"
end if

For the First time my code was working fine but after I closed and reopened I am getting this error.....

My objective is to find the value from the column range and by row no using "rnglast.row" I will get the row no.

Please do help me...

Sakthi

 

[Hui]

Why use Set

rnglast = Sheet1.Range("A7:A" & lw).Find(Sheet1.ComboBox1.Value, ActiveCell, xlValues, xlWhole, xlByRows, xlNext, False)

should work fine

 

[NARAYANK991]

Hi Sakthi ,

Try this :

Private Sub CommandButton1_Click()
            Dim lw As Long
            Dim m As Long
            Dim rnglast As Range
            With Me
                lw = .UsedRange.Rows.Count + 1
                Set rnglast = .Range("A4:A" & lw).Find(Val(.ComboBox1.Value), , xlValues, xlWhole, xlByRows, xlNext, False)
                .Range("A" & rnglast.Row).EntireRow.Insert
            End With
End Sub

I have used the VAL function with the Combobox1 value because I used numeric values in my test range ; if you are sure the values in both the Combobox and your test range are String , you can eliminate this.

I removed the Activecell parameter.

I have used the ME keyword instead of Sheet4 , since I placed the code in the Sheets section ; if you have placed the code in a code module , you will have to replace the ME keyword with Sheet4.

Narayan