ISERROR(VLOOKUP) in VBA

[laurenbattaglia]

I am using VB6 in Excel to do a comparitive of two tables and copy if a vlookup value does not exist in the lookup array. I am using the following code, but the value keeps coming up false.

Dim vLookupValue
Dim C As Integer
Dim lngLastRow As Long




Workbooks.Open FileName:="O:\Lauren\Comparatives\SEC DB TEMP.xls"
Sheets("BALANCE_SHEET&quot.Select
Windows("SEC DB TEMP.xls&quot.Activate
Range("A2&quot.Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Sort Key1:=Range("I2&quot, Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Windows("SEC DATABASE V2.xls&quot.Activate
Sheets("BALANCE_SHEET&quot.Select
Range("A2&quot.Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Sort Key1:=Range("I2&quot, Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom


'Do comparative to replace old duplicate data
C = 2
lngLastRow = Workbooks("SEC DATABASE V2.xls&quot.Worksheets("Balance_Sheet&quot.Range("I65536&quot.End(xlUp).Row
lngLastRow = lngLastRow + 1

Windows("SEC DATABASE V2.xls&quot.Activate
Sheets("Balance_Sheet&quot.Select
Range("I" & C).Select




vLookupValue = Workbooks("SEC DATABASE V2.xls&quot.Worksheets("Balance_Sheet&quot.Range("I" & C)

Do Until C = lngLastRow
If Application.WorksheetFunction.IsError("VLookup(vLookupValue, '[SEC DATABASE V2.xls]BALANCE_SHEET'!$i$1:$i$" & Range("I1&quot.End(xlDown).Row & ", 1, False)&quot Then
Windows("SEC DATABASE V2.xls&quot.Activate
Sheets("Balance_Sheet&quot.Select
Range("I" & C).Select
ActiveCell.EntireRow.Copy
Windows("SEC DB TEMP 2.xls&quot.Activate
Sheets("BALANCE_SHEET&quot.Select
Range("A1&quot.Select
Selection.End(xlDown).Select
ActiveCell.Offset(rowOffset:=1, ColumnOffset:=0).Activate
ActiveSheet.Paste
Application.CutCopyMode = False


End If
C = C + 1

Loop


Thanks for any help you can give me,

Lauren

 

[duncansancho]

Hi Lauren
I'm not sure by looking at your code because it is difficult for a beginner like me to visualise the process without the worksheets but similar problems I have had I have solved with "paste special - values" instead of paste.
Duncan

 

[SkipVought]

Hi,

You are testing a STRING...

Set MyRange = Workbooks("SEC DATABASE V2.xls").Worksheets("BALANCE_SHEET").Range(Range("I1"), Range("I1").End(xlDown))
vLV = WorksheetFunction.VLookup(vLookupValue, MyRange, 1, False)

If IsError(vLV) then
...

Also, I am assuming that your Lookup Range is I1 down to the last filled row. That's what ...

Range(Range("I1"), Range("I1").End(xlDown))

dose

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[laurenbattaglia]

I am getting 'Run-time error 1004': Application-defined or object-defined error on:


Set myRange = Workbooks("SEC DATABASE V2.xls&quot.Worksheets("BALANCE_SHEET&quot.Range(Range("I1&quot, Range("I1&quot.End(xlDown))

 

[SkipVought]

set wsBS = Workbooks("SEC DATABASE V2.xls").Worksheets("BALANCE_SHEET")
Set myRange = Range(wsBS.Range("I1"), wsBS.Range("I1").End(xlDown))

SORRY!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[laurenbattaglia]

I did as was suggested and my code looks like this:

'Do comparative to replace old duplicate data
C = 2
lngLastRow = Workbooks("SEC DATABASE V2.xls&quot.Worksheets("Balance_Sheet&quot.Range("I65536&quot.End(xlUp).Row
lngLastRow = lngLastRow + 1
Set wsBS = Workbooks("SEC DB TEMP.xls&quot.Worksheets("BALANCE_SHEET&quot
Set myRange = Range(wsBS.Range("I2&quot, wsBS.Range("I2&quot.End(xlDown))
vLookupValue = Workbooks("SEC DATABASE V2.xls&quot.Worksheets("Balance_Sheet&quot.Range("I" & C)
vLV = WorksheetFunction.VLookup(vLookupValue, myRange, 1, False)

Do Until C = lngLastRow
If IsError(vLV) Then
Windows("SEC DATABASE V2.xls&quot.Activate
Sheets("Balance_Sheet&quot.Select
Range("I" & C).Select
ActiveCell.EntireRow.Copy
Windows("SEC DB TEMP 2.xls&quot.Activate
Sheets("BALANCE_SHEET&quot.Select
Range("A1&quot.Select
Selection.End(xlDown).Select
ActiveCell.Offset(rowOffset:=1, ColumnOffset:=0).Activate
ActiveSheet.Paste
Application.CutCopyMode = False

End If

C = C + 1

Loop

But the vLookupValue reads the first cell and value displayed does not change when I loop though, therefore it is never finding a true condition. The value of C is incrementing with each loop, so I am not sure what is wrong.

Also, when I do ?myRange in the immediate window, I get a type mismatch error. Shouldn't there be a value for myRange? I have it declared as a range.

I am new to VBA and any help you could give is greatly appreciated.

Lauren

 

[SkipVought]

MyRange is a range of cellin in column I, from I2 to the row before the first blank cell.

Where do you update vLV???

    Dim wsBS1 As Worksheets, wsBS2 As Worksheets
    c = 2
'    Set wsBS = Workbooks("SEC DB TEMP.xls").Worksheets("BALANCE_SHEET")
    Set wsBS1 = Workbooks("SEC DATABASE V2.xls").Worksheets("Balance_Sheet")
    Set wsBS2 = Workbooks("SEC DB TEMP.xls").Worksheets("BALANCE_SHEET")
    lngLastRow = wsBS1.Range("I65536").End(xlUp).Row
    lngLastRow = lngLastRow + 1
    Set myRange = Range(wsBS2.Range("I2"), wsBS2.Range("I2").End(xlDown))
    vLookupValue = wsBS1.Cells(c, "I")
    vLV = WorksheetFunction.VLookup(vLookupValue, myRange, 1, False)
    
    Do Until c = lngLastRow
        If IsError(vLV) Then
            
' where are you updating vLV???????
            
            wsBS1.Cells(c, "I").EntireRow.Copy
            wsBS2.Range(Range("A1"), Range("A1")).End(xlDown).Offset(1, 0).Select
            wsBS2.Paste
        End If
        
        c = c + 1
        
    Loop

You code somewhat simplified


Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com