VBA run-time error 91, variable not set

[zcahlmc]

I'm now getting run-time error 91 with it highlighting my variavle rng1 as not being set. The code worked previously but now on re-opening the file isn't working.

If someone could please let me know what I need to alter here in my code I'd greatly appreciate it.


Dim x As Integer
Dim y As Integer
Dim variableRange As String
Dim concatRange As String
Dim programmeMatch As String
Dim rng1 As Long


x = 3
y = Workbooks("Overnights Calculator - 1 min.xlsm").Worksheets("Data").Cells(5, 15)


Do Until x = y + 1


variableRange = "Q" & x & ":" & "AB" & x
concatRange = "AF" & x
programmeMatch = Workbooks("Overnights Calculator - 1 min.xlsm").Worksheets("toreport").Range(concatRange).Value


Windows("Overnights Calculator - 1 min").Activate


ActiveWorkbook.Sheets("toreport").Range(variableRange).Select
Selection.Copy


rng1 = Workbooks("UK_Consolidated_1920_MASTER.xlsm").Worksheets("Master Data").Range("AC:AC").Find(programmeMatch)

Windows("UK_Consolidated_1920_MASTER").Activate

Workbooks("UK_Consolidated_1920_MASTER.xlsm").Worksheets("Master Data").Range("q" & rng1).PasteSpecial Paste:=xlPasteValues

x = x + 1
Workbooks("Overnights Calculator - 1 min.xlsm").Worksheets("Data").Cells(5, 13) = x


Loop


Windows("UK_Consolidated_1920_MASTER").Activate
Workbooks("UK_Consolidated_1920_MASTER.xlsm").Save

 

[Joe4]

Welcome to the Board!

A few things I noticed upon first glance.

It appears that the intention on "rng1" is to be a range, but you have declared it as "Long" instead of "Range":

[COLOR=#333333]Dim rng1 As Long[/COLOR]

If the code is unable to find the value you are looking for, it may return an error. So you may need error handling around it.

 

[zcahlmc]

Welcome to the Board!

A few things I noticed upon first glance.

It appears that the intention on "rng1" is to be a range, but you have declared it as "Long" instead of "Range":

[COLOR=#333333]Dim rng1 As Long[/COLOR]

If the code is unable to find the value you are looking for, it may return an error. So you may need error handling around it.

Thanks Joe. I've realised what I actually want rng1 to return, is the row value is finds programmeMatch at so I can use this to know where to paste the copied data.

If rng1 is now equal to the following, what should I declare it as?

rng1 = Workbooks("UK_Consolidated_1920_MASTER.xlsm").Worksheets("Master Data").Range("AC:AC").Find(programmeMatch).Row

 

[Joe4]

If you want it to return the Row number, then declaring it as long is fine.
Note, however, if it cannot find the value you are looking for, you will get an error message, if you do not add error handling.

A better way to approach it is to use the Range way, and check to see if anything was found, i.e.

Dim rng1 as Range
Dim rw as Long

Set rng1 [COLOR=#333333]= Workbooks("UK_Consolidated_1920_MASTER.xlsm").Worksheets("Master Data").Range("AC:AC").Find(programmeMatch)

If rng1 is Nothing Then
    MsgBox "Value of " & [/COLOR][COLOR=#333333]programmeMatch & " not found"
Else
    rw = rng1.Row
End If[/COLOR]

 

[zcahlmc]

If you want it to return the Row number, then declaring it as long is fine.
Note, however, if it cannot find the value you are looking for, you will get an error message, if you do not add error handling.

A better way to approach it is to use the Range way, and check to see if anything was found, i.e.

Dim rng1 as Range
Dim rw as Long

Set rng1 [COLOR=#333333]= Workbooks("UK_Consolidated_1920_MASTER.xlsm").Worksheets("Master Data").Range("AC:AC").Find(programmeMatch)

If rng1 is Nothing Then
    MsgBox "Value of " & [/COLOR][COLOR=#333333]programmeMatch & " not found"
Else
    rw = rng1.Row
End If[/COLOR]

Thank you again for your help. I used the code above and also the below code and it now works.

 .Find(programmeMatch, , xlValues, xlWhole)

 

[Joe4]

You are welcome.