WorksheetFunction.Match multiple sheets Excel VBA error 1004

Question

Getting a run-time error '1004': Application-defined or object-defined error

I have 2 data sets in 2 different sheets. I'm trying to run a WorksheetFunction.Match in each data set and copy/paste the appropriate columns to 2 new sheets.

ABC data -> ABC works fine but getting error after Sheets("XYZ data").Select

Sub ccc()

    Sheets("ABC data").Select
    sedol = WorksheetFunction.Match("Sedol", Rows("1:1"), 0)
    isin = WorksheetFunction.Match("Isin", Rows("1:1"), 0)

    Sheets("ABC data").Columns(sedol).Copy Destination:=Sheets("ABC").Range("A1")
    Sheets("ABC data").Columns(isin).Copy Destination:=Sheets("ABC").Range("B1")

    Sheets("XYZ data").Select
    sedol1 = WorksheetFunction.Match("SEDOL1", Rows("1:1"), 0)
    ticker = WorksheetFunction.Match("Ticker", Rows("1:1"), 0)

    Sheets("XYZ data").Columns(sedol1).Copy Destination:=Sheets("XYZ").Range("A1")
    Sheets("XYZ data").Columns(ticker).Copy Destination:=Sheets("XYZ").Range("B1")

End Sub

Any ideas?

Copy *SEDOL1* from the VBA code sheet then go to the *XYZ Data* worksheet and see if you can find it in the first row with `Ctrl+F` using the *Match entire cell contents* option. — , Mar 05 '15 at 13:43

score 2 · Accepted Answer · edited May 23 '17 at 12:14

2

Get away from .Select and .Activate and stop relying on a dynamic ActiveSheet to define the parent of your ranges.

Sub ccc()

    with Sheets("ABC data")
      sedol = WorksheetFunction.Match("Sedol", .Rows("1:1"), 0)
      isin = WorksheetFunction.Match("Isin", .Rows("1:1"), 0)

      .Columns(sedol).Copy Destination:=Sheets("ABC").Range("A1")
      .Columns(isin).Copy Destination:=Sheets("ABC").Range("B1")
    end with

    with Sheets("XYZ data")
      sedol1 = WorksheetFunction.Match("SEDOL1", .Rows("1:1"), 0)
      ticker = WorksheetFunction.Match("Ticker", .Rows("1:1"), 0)

      .Columns(sedol1).Copy Destination:=Sheets("XYZ").Range("A1")
      .Columns(ticker).Copy Destination:=Sheets("XYZ").Range("B1")
    end with

End Sub

Note that the prefixing periods (e.g. . or full stop) in .Rows("1:1") and .Columns(...) determine that the parent is the worksheet defined in the With ... End With statement.

See How to avoid using Select in Excel VBA macros.

edited May 23 '17 at 12:14

Community

1
1

answered Mar 05 '15 at 13:50

1

I would only add that it looks like he is running similar code repeatedly. I'd be willing to bet there's a way to loop it. We'd need more info on the project and it looks like he filtered it out. If I had to guess he works in public finance. – gNerb Mar 05 '15 at 13:56
@Toby - I agree. A loop through a variant array of worksheet names would be one possibility. – Mar 05 '15 at 13:59
I'd bet it's every worksheet. The concerning part is the variable string names in the compare. There are a bunch of different ways to handle that though. – gNerb Mar 05 '15 at 14:00
Hey guys, thanks for the comments and suggestions. I was able to resolve the issue by putting all of my syntax in a workbook. I had my syntax all under the PMR sheet previously. – xslyx Mar 05 '15 at 14:39

gNerb · Answer 2 · 2015-03-05T13:59:08.177

I do not use Sheets(Name).Select:

Sheets("ABC Data").Activate

Is my preferred method. I would also recommend creating objects to make it easier for you to type things out:

Dim ABC as WorkSheet
Set ABC = ActiveSheet

So something like:

Dim ABC as Worksheet
Dim XYZ as worksheet

Set ABC = Sheets("ABC data")
With ABC
    sedol = .WorksheetFunction.Match("Sedol", Rows("1:1"), 0)
    isin = .WorksheetFunction.Match("isin", Rows("1:1"), 0)

    .Columns(sedol).Copy Destination:=.Range("A1")
    .Columns(isin).Copy Destination:=.Range("B1")
End With

Set XYZ = Sheets("XYZ data")
With XYZ
    sedol1 = .WorksheetFunction.Match("SEDOL1", Rows("1:1"), 0)
    ticker = .WorksheetFunction.Match("Ticker", Rows("1:1"), 0)

    .Columns(sedol1).Copy Destination:=.Range("A1")
    .Columns(ticker).Copy Destination:=.Range("B1")
End With

Finally, If you are doing this for every sheet in your workbook, you should loop it. If you need that code let me know

If that method of selecting/working with sheets doesn't work for you let me know and Ill update the answer.

Might be missing the `.` in `.Rows("1:1")`. Don't want to leave that up to a guess. The destinations are different as well. — , Mar 05 '15 at 14:01
The downfalls of copy-pasta. It was a quick and dirty example. You are very likely correct though (unless the rows are on the active sheet and he set them all to 1:1 as a method of filtering proprietary data). — gNerb, Mar 05 '15 at 14:15

score 0 · Answer 3 · edited May 04 '15 at 01:59

0

Posting it so other people can learn from my boneheaded move. Noticed my syntax were all entered in PMR sheet - moved all of my syntax to a module and it worked like a charm.

edited May 04 '15 at 01:59

Nilesh

4,137
6
39
53

answered Mar 05 '15 at 13:47

xslyx

35
7

WorksheetFunction.Match multiple sheets Excel VBA error 1004

3 Answers3