CODEWITHSUNDEEP
excelvba
sant
sant

Reputation: 101

Insert new row under a specific text using vba

I am trying to insert new row under a particular text(selected from userform) with the new text but getting an error "Object variable or With block variable not set" in the line "fvalue.Value = Me.txtremark.Value".

Please help me to find where exactly the mistake I did in the Code. I was trying to find many ways but failed.

Excel Table:

enter image description here

Required Output:

enter image description here

Private Sub cmdadd_Click()

    Dim fvalue As Range
    Dim wks As Worksheet
    Set wks = ThisWorkbook.Worksheets("Sheet1")
    wks.Activate
    Set fvalue = wks.Range("B:B").Find(What:=Me.txtremark.Value, LookIn:=xlFormulas, LookAt:=xlWhole)
    fvalue.Value = Me.txtremark.Value
    fvalue.Offset(1).EntireRow.Insert Shift:=xlDown
    fvalue.Offset(0, 1).Value = Me.txtplace.Value

End Sub

Upvotes: 1

Views: 451

Answers (2)

Tim Stack
Tim Stack

Reputation: 3248

Try using a separate variable to pass values to the worksheet, or just refer to the textbox.

Additionally, activating (and selecting) is not necessary and will hurt your macro's speed and is prone to errors.

Option Explicit

Private Sub cmdadd_Click()

    Dim fvalue As Range
    Dim wks As Worksheet
    Set wks = ThisWorkbook.Worksheets("Sheet1")

    Set fvalue = wks.Range("B:B").Find(What:=Me.txtremark.Value, LookIn:=xlFormulas, LookAt:=xlWhole)
    If Not fvalue Is Nothing Then
        wks.Rows(fvalue.Row + 1).EntireRow.Insert
        wks.Cells(fvalue.Row + 1, fvalue.Column + 1).Value = Me.txtremark.Value
    End If
End Sub

I have taken the liberty to check if the value is found in the first place

Upvotes: 1

Error 1004
Error 1004

Reputation: 8220

Try:

Option Explicit

Sub test()

    Dim Position As Range, rngToSearch As Range
    Dim strToFound As String

    'Change strToFound value
    strToFound = "Test"

    With ThisWorkbook.Worksheets("Sheet1")

        Set rngToSearch = .Range("B:B")

        Set Position = rngToSearch.Find(strToFound, LookIn:=xlValues, Lookat:=xlWhole)

        If Not Position Is Nothing Then

            Debug.Print Position.Row
            .Rows(Position.Row).Offset(1).EntireRow.Insert

        End If


    End With

End Sub

Upvotes: 1

Related Questions