Trapping errors.

G

Graeme

Hello,

I have written the below code fragment in Excel Vba. It copies information
from several data sheets to an output sheet if the data sheetname is
contained within a "map" and has been flagged with a "Y".
This works fine if all the sheetnames are contained within the "map". I
noticed however that vlookup falls over if I add new data sheets and don't
remember to update the map at the same time.
Could someone advise as to how to trap the error?

For Each wks In ActiveWorkbook.Sheets
If Application.VLookup(wks.Name, Range("Map!b2:c200"), 2,
False) = "Y" Then
Sheets("Output").Range("A:A").Offset(0, x + 1) =
wks.Range("J:J").Value
x = x + 1
End If
Next wks
 
D

dbKemp

Hello,

I have written the below code fragment in Excel Vba. It copies information
from several data sheets to an output sheet if the data sheetname is
contained within a "map" and has been flagged with a "Y".
This works fine if all the sheetnames are contained within the "map". I
noticed however that vlookup falls over if I add new data sheets and don't
remember to update the map at the same time.
Could someone advise as to how to trap the error?

For Each wks In ActiveWorkbook.Sheets
If Application.VLookup(wks.Name, Range("Map!b2:c200"), 2,
False) = "Y" Then
Sheets("Output").Range("A:A").Offset(0, x + 1) =
wks.Range("J:J").Value
x = x + 1
End If
Next wks

Try this:

Dim wks As Excel.Worksheet
Dim x As Long
Dim vValue As Variant

For Each wks In ActiveWorkbook.Sheets
vValue = Application.VLookup(wks.Name, Range("Sheet1!e2:f9"), 2,
False)
If Not IsError(vValue) Then
If vValue = "Y" Then
Sheets("Output").Range("A:A").Offset(0, x + 1) = wks.Range
("J:J").Value
x = x + 1
End If
End If
Next wks
Set wks = Nothing

I'm not sure this line is going to work => Sheets("Output").Range
("A:A").Offset(0, x + 1) = wks.Range("J:J").Value
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top