CODEWITHSUNDEEP
vba
Zachary Decent
Zachary Decent

Reputation: 11

Trying to delete headers with certain names - run time error 91

So I'm very new to VBA but have lots of experience in SQL and some basic coding practice.

I tried writing a loop to find the names of certain headers and then delete them, but during the first if function it throws error 91 "Object Variable or With block variable not set" ... any idea what I'm doing wrong? The source code looks like:

'Deletes system specific fields that aren't required

Public Sub Delete_Fields_Per_System()

    Dim v_count_colum As Integer
    Dim v_count_colum_letter As String
    Dim i2 As Integer

        With Sheets("Results_Detail")

        i2 = 0

        ' Declare loop to replace four different columns for this system
        Do While i2 < 4

        'Change String to different headers for each loop
        If i2 = 0 Then
            v_count_colum = .Cells.Find("Create Time").Column
        ElseIf i2 = 1 Then
            v_count_colum = .Cells.Find("Post Time").Column
        ElseIf i2 = 2 Then
            v_count_colum = .Cells.Find("Approver ID").Column
        Else
            v_count_colum = .Cells.Find("Approver Name").Column
        End If

        'For each header, find number in the list, then corresponding letter and delete
        If v_count_colum > 26 Then
            v_count_colum_letter = Chr(Int((v_count_colum - 1) / 26) + 64) & Chr(((v_count_colum - 1) Mod 26) + 65)
        Else
            v_count_colum_letter = Chr(v_count_colum + 64)
            .Columns(v_count_colum_letter).EntireColumn.Delete
        End If

        i2 = i2 + 1

        Loop

    End With

End Sub

Upvotes: 1

Views: 32

Answers (1)

luke_t
luke_t

Reputation: 2985

It looks like the search can't find the string provided, which means it's causing an error when trying to assign 'nothing' to the integer variable.

Is the value "Create Time" definitely on the sheet you're searching? Try searching for something which is definitely on the sheet, and it should then be able to assign the column integer to the variable.

If you need to search the sheet, even in the scenario that the string you're searching for is not evident, you will be best assigning the found cell to a range, and then if the range is not nothing, you can assign the column integer value. The below should work for you.

Dim rng As Range
If i2 = 0 Then
    Set rng = .Cells.Find("Create Time")
ElseIf i2 = 1 Then
    Set rng = .Cells.Find("Post Time")
ElseIf i2 = 2 Then
    Set rng = .Cells.Find("Approver ID")
Else
    Set rng = .Cells.Find("Approver Name")
End If

If Not rng Is Nothing Then
    v_count_colum = rng.Column
    rng = Nothing
End If

Upvotes: 1

Related Questions