gravanoc
Active Member
- Joined
- Oct 20, 2015
- Messages
- 351
- Office Version
- 365
- Platform
- Windows
- Mobile
I have a range variable set to a named range that is consistently reverting to nothing. Earlier it was working fine, but now it isn't.
It may be because at one point it is set using the SpillingToRange. I'm not sure if the mechanics for that works differently than normal.
It seems to work alright in this sub. It is called rngCustomer (not to be confused with rngECustomer):
It fails when it reaches rngCustomer.Resize(1000, 2).ClearContents here (I removed code I thought was irrelevant, marked by ...):
It may be because at one point it is set using the SpillingToRange. I'm not sure if the mechanics for that works differently than normal.
It seems to work alright in this sub. It is called rngCustomer (not to be confused with rngECustomer):
VBA Code:
Dim rngECustomer As Range, rngCustomer As Range
Dim sLookup As String
Dim matchNo As Long, revMatchNo As Long
Dim i As Long, x As Long, y As Long
wksExtract.Columns.EntireColumn.Hidden = False ' If Customer column is hidden, unhide it and others
Set rngHeader = wksExtract.UsedRange.Rows(1).SpecialCells(xlCellTypeConstants) ' Used Range is all data on a sheet, special cells are non-formulas
Set rngOutput = wksUpload.Range("Output") ' Set range variables to named ranges
Set rngMatches = wksDash.Range("Matches")
Set rngCustomer = wksDash.Range("Customer")
Set rngECustomer = wksExtract.Cells.Find("Customer", wksExtract.Cells(1, 1), xlFormulas, xlPart, xlByColumns, xlNext) 'Uses Find function to locate Customer column on Extract worksheet
Set rngECustomer = rngECustomer.Resize(rngECustomer.Offset(100000).End(xlUp).Row, 1).Offset(1) ' Changes rngECustomer to encompass entire column of data
rngMatches.Resize(100, 3).ClearContents ' Next three clear old data from Uploader workbook
rngOutput.Resize(100000, rngOutput.Columns.Count).Offset(1).ClearContents
rngCustomer.ClearContents
' Next uses UNIQUE & FIlTER formulas on dashboard to populate
' all unique customer names by using the extraction workbook name, worksheet name
' and address of the customer column, offset by 1 row.
rngCustomer.Formula2 = "=FILTER(UNIQUE('[" & wbExtract.Name & "]" & wksExtract.Name & "'!" & _
rngECustomer.Address & "),UNIQUE('[" & wbExtract.Name & "]" & wksExtract.Name & "'!" & _
rngECustomer.Address & ")<>"""")"
It fails when it reaches rngCustomer.Resize(1000, 2).ClearContents here (I removed code I thought was irrelevant, marked by ...):
VBA Code:
Dim rngMatches As Range, rngCustomer As Range
...
Set wbUpload = Workbooks(wksDash.Parent.Name) ' Sets Upload workbook variable in case necessary
Set rngHeader = wksExtract.UsedRange.Rows(1) ' Same ranges as in getMatches, except rngData is used to set the specific column receiving data
Set rngOutput = wksUpload.Range("Output")
Set rngData = rngHeader.Resize(rngHeader.Offset(100000).End(xlUp).Row, rngHeader.Columns.Count)
...
If CheckFilters(wksExtract) Then rngData.AutoFilter ' Calls function CheckFilters with 1 arg, the extraction sheet variable
' If it returns True then turn off AutoFilter, which is mandatory.
Set rngData = rngHeader.Resize(rngHeader.Offset(100000).End(xlUp).Row, rngHeader.Columns.Count) ' Change rngData to encompass entire column of data
rngData.AutoFilter Field:=1, Criteria1:=">=" & CDbl(dateStart), Operator:=xlAnd, Criteria2:="<=" & CDbl(dateEnd) ' Sets the filter to include the chosen dates
On Error Resume Next
...
Set rngOutput = wksUpload.Range("Q2:Q" & rowCount) ' Set to item rate column
Set rngCustomer = wksDash.Range("Customer").SpillingToRange ' Set to customer column on dashboard
Set rngCustomer = rngCustomer.Resize(rngCustomer.Rows.Count, 2) ' Now customer and item rate
' Formula looks up item rate
rngOutput.Formula2 = "=VLOOKUP(" & rngOutput.Cells(1, -14).Address(False, False) & ", " & wksDash.Name & "!" & rngCustomer.Address & ", 2, FALSE)"
rngOutput.Copy ' Next statements copy then pastes as values the item rate
rngOutput.PasteSpecial xlPasteValues
Application.CutCopyMode = False
...
wksDash.Range("DateA").Resize(2, 1).ClearContents ' Clears dashboard data
wksDash.Range("FileName").Resize(3, 1).ClearContents
wksDash.Range("Matches").Resize(10000, 3).ClearContents
rngCustomer.Resize(1000, 2).ClearContents 'FAILS, rngCustomer is Nothing