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
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