match function, iserror

[Decar]

Hi,

I'm trying to "match" values from one table in another and if there is a value not in the table it should do something. The loop works but the match function does not work properly.

I can not set Search1 equal to the match function which I would like to do as it makes the code so much easier to read/adjust

and

Dim Search1 as long

with sheet9
If IsError(Application.Match(.Cells(n, 3), sheet1.Range("B:B"), 0)) = True Then
                    Search1 = Application.Match(.Cells(n, 3), sheet1.Range("B:B"), 0)
                    
                        sheet1.Cells(Search1, 2) = sheet9.Cells(n, 3)
                        sheet1.Cells(Search1, 3) = sheet9.Cells(n, 5) 
                        sheet1.Cells(Search1, 4) = sheet9.Cells(n, 9) 
                        sheet1.Cells(Search1, 5) = sheet9.Cells(n, 10) 
                        sheet1.Cells(Search1, 6) = sheet9.Cells(n, 13) 
                    Else
                        lr2 = sheet1.Cells(Rows.Count, 2).End(xlUp).Row + 2

                        sheet1.Cells(lr2, 2) = sheet9.Cells(n, 3) 
                        sheet1.Cells(lr2, 3) = sheet9.Cells(n, 5) 
                        sheet1.Cells(lr2, 4) = sheet9.Cells(n, 9)
                        sheet1.Cells(lr2, 5) = sheet9.Cells(n, 10) 
                        sheet1.Cells(lr2, 6) = sheet9.Cells(n, 13)                           
                    
End If
end with

[Leith Ross]

Hello Decar,

Please post a copy of your workbook. It will be easier to isolate the problem that way.

[Decar]

Thank you Leith,

after catching a break and looking at it again I found my mistake. The ISerror method was new for me and I did not think enough about how the output is "true/False" so it basically told the range to be in row "true" which makes not much sense for VBA...

Next time I'm going to add a workbook.


regards

[Leith Ross]

Hello Decar,

You're welcome, although I didn't do anything.

Thanks for posting back with the solution. Glad to know that you have the macro working now.