Search code examples
excelvbanestedwith-statement

Can I nest a With inside a With when both are designating a different sheet in the same workbook? VBA


Basically, I've got a workbook that imports values from one sheet, then allows you to select some, add extra data, then transfers the selected items, your added values, and additional info contained in the sheet whose values are pulled from initially to another workbook. I need to be able to match the values inside the initial worksheet with the corresponding info in the other sheet (in the same workbook) so that I can take data from both and loop them in. I am worried that I am not allowed to nest my Withs when they both designate worksheets within the same workbook because I keep getting Error 91 (Object variable or With block variable not set). Is this true?

Dim RMs As Worksheet
Dim FLF As Worksheet
Set RMs = Workbooks("FLF Coding Trials.xlsm").Worksheets("Program")
Set FLF = Workbooks("FLF Template.xlsx").Worksheets("FLF Sheet")

Dim lng As Long
Dim cnt As Long
Dim check As Long
Dim length As Long

Dim namecheck As Range
Dim vencheck As Range

With ThisWorkbook.Sheets("Program")
lng = Me.Cells(.Rows.Count, "N").End(xlUp).Row
For cnt = 1 To lng - 1 Step 1
    'The two below lines work reliably
    FLF.Range("B" & cnt + 23).Value = Me.Range("N" & cnt + 1).Value
    FLF.Range("N" & cnt + 23).Value = Me.Range("P" & cnt + 1).Value

    'Adding the following variable designations and the With gives me the error. If I change the below two variables to strings I get a compile error
    namecheck.Value = Range("N" & cnt + 1).Value
    vencheck.Value = Range("P" & cnt + 1).Value

        With ThisWorkbook.Sheets("Names and Vendors")
        length = Me.Cells(.Rows.Count, "B").End(xlUp).Row
        check = ThisWorkbook.Sheets("Names and Vendors").Evaluate("MATCH(1,(B1:B" & length & "=""" & namecheck.Value & """)*(C1:C" & length & "=""" & vencheck.Value & """),0)")
        'more commands will go here
        End With
    Next cnt
End With

Solution

  • If you're in a With block and your code enters a second With (without first having hit an End With) then the scope defined by the second With becomes the active scope until you hit the End With (or another With)

    With A
       'scope here is A
       With B
           'scope here is B
       End with
       'scope here is A
    End With