CODEWITHSUNDEEP
excelvbareplace
Stephanie
Stephanie

Reputation: 7

Object variable or With Block Variable not set - Find and Replace Macro - Multiple Find Iterations

When running the macro I receive the error "Object variable or With block variable not set". I am unsure which variable is not set properly, now how to figure it out.

I have gone through and tried ensuring all my variables were set up properly but I'm pretty sure I'm missing something. 

Sub NewMonth_Setup()

'
'Find and Replace
'
Dim sht As Worksheet
Dim fnd1 As Variant
Dim rplc1 As Variant
Dim fnd2 As Variant
Dim rplc2 As Variant
Dim fndList As Variant
Dim rplcList As Variant
'Activates currently selected sheet
    Sheets(ActiveSheet.Name).Select
'
'Replaces old table name with new
    fnd1 = InputBox("Old Table Name: _MMYYWS")
    rplc1 = InputBox("New Table: _MMYYWS")
    fnd2 = InputBox("Old Table Name: _MMYY")
    rplc2 = InputBox("New Table: _MMYY")
'
    fndList = Array(fnd1, fnd2)
    rplcList = Array(rplc1, rplc2)
'
' Perform Find/Replace All
    sht.Cells.Replace What:=fndList(x), Replacement:=rplcList(x), _
        LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
        SearchFormat:=False, ReplaceFormat:=False
'
MsgBox "I have completed my search and made replacements"
'


End Sub

Expected results would take the user's input for two variables and replace them respectively. It is replacing the inputs but it still returns with the error and does not output the MsgBox

Upvotes: 0

Views: 169

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71197

You can't make a member call against Nothing. You've declared an object variable here:

Dim sht As Worksheet

This reserves a spot in memory where an object reference can be stored, and until that reference is Set, VBA understands this memory space to be Nothing - in other languages this might be null or some similar concept: bottom line is, there's no valid object reference at that address, so VBA raises error 91 when you try to access it... here:

sht.Cells.Replace ...

This shouldn't need to be done:

Sheets(ActiveSheet.Name).Select

Instead, Set the sht object reference:

Set sht = ActiveSheet ' or, get a worksheet reference from the Workbook.Worksheets collection

And that should fix it... assuming x is given a value somewhere. Presumably the search is intended to run in a loop? You need a loop construct for that:

For x = LBound(fndList) To UBound(fndList)
    sht.Cells.Replace ...
Next

Upvotes: 2

Related Questions