Why does this work????

[Mr Walker]

I had trouble getting my hlookup to work, and whilst trying a few things, I wrote this. I'm glad it works, but I always like to know why, and I just can't think this through in my head.

It's the If Error line. Can someone explain what it's doing? I know it's probably simple, but I'm still fairly new to the whole VB thing. Great fun though!!!

BTW, ReceiptsCol and LookupRange are dimmed early on in my sub.

On Error Resume Next
If Error = Sheet21.Cells(r, 1) = WorksheetFunction.HLookup(Sheet21.Cells(r, 1), LookupRange, 1, False) Then
On Error GoTo 0

Sheet3.Columns(ReceiptsCol).EntireColumn.Insert
End If

 

[Andrew Poulsom]

How have you dimensioned the Error variable? In your code it is Boolean, True if:

Sheet21.Cells(r, 1) = WorksheetFunction.HLookup(Sheet21.Cells(r, 1), LookupRange, 1, False)

(ie it's in the LookupRange) False if not.

Is that what you intended?

 

[Mr Walker]

I thought that's why it works originally. That's why I'm a bit confused. I type Option Explicit in my Declarations as a matter of course, and Error isn't dim'd at all.

Also, my code inserts a column when the data is NOT in the Lookup Range, ie when the error is true. Is this something

Don't get me wrong, I'm not unhappy that it works, but I'd love to know why.

I'm thinking that the lookup, when it works, returns a TRUE value , (as Sheet21.Cells(r, 1) does equal WorksheetFunction.HLookup(Sheet21.Cells(r, 1), LookupRange, 1, False)), which is an invalid error number, so it goes straight to End If, and when the lookup fails it returns a valid error number (1004), so it executes the "Insert Column" procedure.

 

[Mr Walker]

Hmm. I did a bit of testing, and it returns a value of nothing (Empty) if the lookup fails, and a value of True if the lookup is successful.

I'm not sure the Error function is really supposed to be used like this, but it works very well for me. :D

 

[goblin]

Comparison operators are evaluated left-to-right so this is what is happening in your code (shortend for readablilty):

If (Error = Sheet21.Cells(r, 1)) = HLookup() then...

Thus Error = Cells(r,1) is evaluated first. If you haven't declared Error at all in your code, it is a Variant/String with value = ""

Thus if Cells(r,1) is empty you get TRUE from this comparison, otherwise FALSE.

You then compare TRUE/FALSE to the results of the HLookup()

Hope this helps your understanding. If not, I can break it down further.
Goblin

 

[goblin]

Update on my explanation before:

(Error = Cells(r,1)) will always evaluate to FALSE, AFAIK, because you are comparing the cell itself to "", not the value of the cell.

(Error = Cells(r,1).Value) would have resulted in TRUE for an empty cell.

Goblin

 

[Mr Walker]

What this routine does is a lookup on a series of dates in Sheet21, compares that to dates in Sheet3, and inserts the column in Sheet3 if the date in Sheet21 is not present in Sheet3.


Goblin, wouldn't your explanation mean that my lookup data is constantly being compared to a 'False' value, as there is always something present in the column in Sheet21?

And when the lookup fails, this means that the value that the lookup returns is "", and 'False' (or the returned lookup value for that matter) would never equal "", so inserting the column would never happen.

Sorry if I seem so thick, but this is baking my noodle.

 

[goblin]

That is correct.

Unless the HLookup column can return true/false or 0.

The statement 'false = 0' evaluates to true, so if there are any 0 in the HLookup result column, you can expect the statement to fire for those.

To understand the statement you've written play around with this simplified statement:

Sub testError()
    Dim a, b
    a = "x"    'Try out a = ""
    b = "ABC"  'Try out b = True
    '  NOTE: a = b is never evaluated as such!!!!!
    If Error = a = b Then
        MsgBox "TRUE" & Chr(10) & "Error = a is " & (Error = a) & Chr(10) _
        & "a = b is " & (a = b), vbOKOnly, "Results of IF"
    Else
        MsgBox "FALSE" & Chr(10) & "Error = a is " & (Error = a) & Chr(10) _
        & "a = b is " & (a = b), vbOKOnly, "Results of IF"
    End If
End Sub

Goblin

 

[Juan Pablo González]

One more example of why you should always use Option Explicit AND excplicitly declare the variable types as well...

 

[Mr Walker]

Hey Juan, I take notice of good advice. I use Option Explicit always, including this case. I'm a good boy.... :D :P

I think I see what you're saying Goblin. If the lookup's not found, it evaluates both as "", and as "" = "", the column is inserted. If the lookup is found (no zeros or true/falses BTW), then "" <> "returned value" and the column isn't inserted.

I think my brain has wrapped around it now.

Thanks for the help. I really appreciate it. The fact that I wasn't sure how it worked was irritating me enormously. I like to get to the bottom of these things.

I do think I'll re-write that section of code to make it a little more understandable though. :D

Cheers!