Excel VBA: String or variant variable showing empty when it is not (1 Viewer)

Apples241

Registered User.
Local time
Today, 15:13
Joined
Aug 29, 2016
Messages
54
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.

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
==> results in ""

and

Code:
Dim matchedadddress as Variant
==> 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.


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:

Apples241

Registered User.
Local time
Today, 15:13
Joined
Aug 29, 2016
Messages
54
I found out what the issue was. I did not mention that I had my code in a module in the PERSONAL workbook.

Code:
Set wkbkBilling = Workbooks(1)
is the problem.

Since my code is in the PERSONAL workbook, I should have said:

Code:
Set wkbkBilling = ActiveWorkbook

ThisWorkbook (and probably Workbooks(1)) apparently references the workbook with the code, which in this case is the PERSONAL workbook.

Source: http://stackoverflow.com/questions/39779576/using-personal-xlsb-referencing-active-workbook-in-vba/39779724
 
  • Like
Reactions: Rx_

Rx_

Nothing In Moderation
Local time
Today, 16:13
Joined
Oct 22, 2009
Messages
2,803
Glad you found the solution. Being specif about the workbook referenced is easy to miss.
Filling in the answer will help others who are searching for a solution later.
Please feel free to use the Thread Tools and mark your post as [SOLVED].
And, also feel free to post the key parts of the entire solution. It will help others later.
 

Apples241

Registered User.
Local time
Today, 15:13
Joined
Aug 29, 2016
Messages
54
Just saying thanks b/c I never knew the SOLVED option was under Thread Tools when you log in.

If/when I get this whole code working, I will post. Thanks!
 

Users who are viewing this thread

Top Bottom