Creating a "MATCH" formula with VBA using values from an array 2

[PWD]

Good afternoon, I want to copy certain columns from a file to another file in a particular order. I have created an array of all the column headings I'm looking for (in case my 'supplier' changes the order of my source data) & thought that a good way would be to use MATCH to find the relevant column number; however, I either can't get the syntax correct or it's not possible. This is a distilled version of that part of the code:-

ha = "redflag_nps"

    ActiveCell.FormulaR1C1 = "=MATCH(" & ha & ",UKC12400657_0.csv!R1,0)"

This is what happens:-

=MATCH(redflag_nps,UKC12400657_0.csv!$1:$1,0)
Result: #NAME?

Instead of what I was hoping for:-

=MATCH("redflag_nps",UKC12400657_0.csv!$1:$1,0)
Result: 223

I've tried various combinations of speech marks but it's not doing it? Any suggestions please?

Many thanks,
D€$

 

[SkipVought]

hi,

So is redflag_nps a variable or a literal? Your SECOND Match() example has QUOTES around it as a literal.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

I think that this is the syntax you need...

ActiveCell.Formula = "=MATCH(""" & ha & """,csvtest.csv!$1:$1,0)"

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PWD]

Yep, yep yep!! Thanks Skip. I hadn't tried that combination of quotes - don't think I would either!

Many thanks,
D€$

 

[PHV]

Another (clearer ?) way:
ActiveCell.Formula = "=MATCH(" & Chr(34) & ha & Chr(34) & ",csvtest.csv!$1:$1,0)"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ClulessChris]

just curious but would this also work?

ActiveCell.Formula = "=MATCH('" & ha & "',csvtest.csv!$1:$1,0)"

Never knock on Death's door: ring the bell and run away! Death really hates that!

 

[PWD]

Hi Guys.

PHV, "Yes"
Chris, "No"

OK; moving on, if you don't mind! I want to use this column information to copy from Row 4 to Row (EndRow) and paste in the current (active) workbook.

So I have this formula in each of the columns in Row 2:-

Cells(2, x).Formula = "=MATCH(" & Chr(34) & ha & Chr(34) & "," & AWB & "!$1:$1,0)"
haCol = Cells(2, x).Value

Where x starts at 3 and increments through each loop of the entries in the array and "AWB" was the active Workbook from which I created this new (current) workbook (NWB).

So I thought along these lines:-

With Workbooks(AWB)
.Range(Cells(4, haCol), Cells(EndRow, haCol)).Copy  'Copy data from Source Sheet
End With

Runtime error 438; Object doesn't support this property or method.

I don't want to use something like this:-

Workbooks(AWB).Activate
Range(Cells(4, shaCol), Cells(EndRow, shaCol)).Copy

Workbooks(NWB).Activate
    With ActiveSheet
    .Cells(2, x).Select
    .Paste
    End With

Many thanks,
D€$

 

[SkipVought]

Qualify the Cells properties in the Range object, to the workbook., like

   .Range(.Cells(.....

From a mobile device

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PWD]

Hi Skip, I had previously tried:-

Workbooks(AWB).Sheets(1).Range(Cells(4, haCol), (Cells(EndRow, haCol))).Copy

but get

error 1004: Application-defined or object-defined error.

Many thanks,
D€$

 

[PHV]

With Workbooks(AWB).Sheets(1)
  .Range([!].[/!]Cells(4, haCol), [!].[/!]Cells(EndRow, haCol)).Copy 
End With

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PWD]

Thanks PHV.

Again, a combination that I hadn't tried - ever!!

That's all for now; thank you everyone for your input.

Many thanks,
D€$

 

[SkipVought]

With Workbooks(AWB).Sheets(1)
   .Range([b][red].[/red][/b]Cells(4, haCol), [b][red].[/red][/b](Cells(EndRow, haCol))).Copy 
end with  ^                 ^

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}