Hello, I have not completed working on the code below, but it is hopefully going to uncheck an item in column B's filter (Filter:=2) of the Billing workbook if the address in column D of the Billing workbook is found in the AddrList workbook.
I have a question about why my variable, 'matchedaddress', shows as empty when the column D in the Billing workbook it is pointing to definitely has addresses in it.
The funny thing is, this code is almost exactly the same as code I modified from the internet to highlight a row if criteria is matched between workbooks and the variable works there, though it is called another name and is 'Dim variable as String'. I don't understand why it doesn't work here.
In Excel 2013 I've tried:
==> results in ""
and
==> results in Empty
I checked to see if it is maybe pointing to the wrong workbook, the AddrList workbook, but column D has numbers there so 'Dim matchedaddress as Variant' should have caught that.
EDIT: I should say, too, that I don't think spaces in the columns being matched are an issue. I didn't use TRIM to check for spaces b/c there aren't any spaces in the columns being matched in the 2 workbooks. In fact, a regular VLOOKUP function in the workbook, finds a match between the 2 workbooks.
Pic including column D of Billing workbook:
https://drive.google.com/open?id=0B9IyKJSJ52ghUHpzOG5fX0xJWkE
The Billing workbook is downloaded from the internet and column D comes formatted as General, if that makes a difference.
I appreciate the help.
I have a question about why my variable, 'matchedaddress', shows as empty when the column D in the Billing workbook it is pointing to definitely has addresses in it.
Code:
matchedaddress = wkbkBilling.ActiveSheet.Cells(iBilling, "D").Value
The funny thing is, this code is almost exactly the same as code I modified from the internet to highlight a row if criteria is matched between workbooks and the variable works there, though it is called another name and is 'Dim variable as String'. I don't understand why it doesn't work here.
In Excel 2013 I've tried:
Code:
Dim matchedaddress as String
and
Code:
Dim matchedadddress as Variant
I checked to see if it is maybe pointing to the wrong workbook, the AddrList workbook, but column D has numbers there so 'Dim matchedaddress as Variant' should have caught that.
EDIT: I should say, too, that I don't think spaces in the columns being matched are an issue. I didn't use TRIM to check for spaces b/c there aren't any spaces in the columns being matched in the 2 workbooks. In fact, a regular VLOOKUP function in the workbook, finds a match between the 2 workbooks.
Pic including column D of Billing workbook:
https://drive.google.com/open?id=0B9IyKJSJ52ghUHpzOG5fX0xJWkE
The Billing workbook is downloaded from the internet and column D comes formatted as General, if that makes a difference.
I appreciate the help.
Code:
Sub FilterBillingBridges()
Dim wkbkBilling As Workbook
Dim iBilling As Long
Dim lastrowBilling As Long
Dim lastcolBilling As Long
Dim wkbkAddrList As Workbook
Dim jAddrList As Long
Dim lastrowAddrList As Long
Dim lastcolAddrList As Long
Dim matchedaddress As Variant
Dim AddrListRng As Range
'Billing
Set wkbkBilling = Workbooks(1)
lastrowBilling = ActiveSheet.Range("D" & Rows.Count).End(xlUp).Row
lastcolBilling = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
For iBilling = 2 To lastrowBilling
matchedaddress = wkbkBilling.ActiveSheet.Cells(iBilling, "D").Value
Set wkbkAddrList = Workbooks(2) 'Address List
'Addr List (Address List)
wkbkAddrList.Activate
lastrowAddrList = ActiveSheet.Range("H" & Rows.Count).End(xlUp).Row
lastcolAddrList = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
For jAddrList = 2 To lastrowAddrList
If ActiveSheet.Cells(jAddrList, "K").Value = matchedaddress Then
'Activate Billing Workbook
wkbkBilling.Sheets(1).Activate
'Will add code to check if already unchecked
ActiveSheet.Range(Cells(iBilling, "B"), Cells(iBilling, lastcolBilling)).AutoFilter Field:=2, Criteria1:=matchedaddress, Operator:=xlAnd
End If
Next jAddrList
Next iBilling
wkbkBilling.Sheets(1).Activate
wkbkBilling.ActiveSheet.Range("A1").Select
End Sub
Last edited: