Index/Match using predefined columns

[Alroj]

Hi Team,

I am trying to do an index match using pre-defined columns from 2 sheets. The sheet named "Descriptions have 2 columns pre-defined "GrwDef" and "DescAsset" while the sheet named "IR115 Data" has one predefined column "Asset"

This formula should fetch data from the sheet descriptions and populated in the sheet IR115 Data. However I am not having success with formula and the error message popping up is "run-time error 424, object required"

Your assistance would be greatly appreciated please

ActiveCell.Formula2R1C1 = "=INDEX(Descriptions!" & GrwDef.Address(ReferenceStyle:=xlR1C1) & ",MATCH(@'IR115 Data'!" & Asset.Address(ReferenceStyle:=xlR1C1) & ",Descriptions!" & DescAsset.Address(ReferenceStyle:=xlR1C1) & ",FALSE))"

 

[Alex Blakenburg]

What does pre-defined mean ? Do you mean a Named Range or did you use the Set command ?
If you are using Set show us the "Set" lines.
If you mean a Named Range then you need something more like this:

ActiveCell.Formula2R1C1 = "=INDEX(Descriptions!" & Range("GrwDef").Address(ReferenceStyle:=xlR1C1) & ",MATCH(@'IR115 Data'!" & Range("Asset").Address(ReferenceStyle:=xlR1C1) & ",Descriptions!" & Range("DescAsset").Address(ReferenceStyle:=xlR1C1) & ",FALSE))"

 

[Alroj]

Hi Alex, apologies for my slow response, might be the time difference.

I have predefine the location of the columns where the relevant data is located e.g. find a value and make the column where the value is a predefined column then I've used these "predefined columns" in the formula as per the code below:

' Source sheet
Sheets("Descriptions").Select

'Setting ranges to be used in formulas
 
Range("a1:az8").Select
Selection.Find(What:="Asset Class", LookIn:=xlValues).Select
 
DescAsset = ActiveCell.Column
 
Range("a1:az8").Select
Selection.Find(What:="Growth/Defensive", LookIn:=xlValues).Select
 
    GrwDef = ActiveCell.Column

'destination sheet
Sheets("IR115 Data").Select
    lastrow = Cells(Rows.Count, "b").End(xlUp).Row
 
Range("a1:az8").Select

Selection.Find(What:="Level Name 1", LookIn:=xlValues).Select
Asset = ActiveCell.Column
 
 
 Range("A1:aa" & lastrow).Select
 Selection.Find(What:="Fund Code", LookIn:=xlValues).End(xlToRight).Offset(0, 1).Select
 ActiveCell.Value = "Growth/Defensive"
ActiveCell.Offset(1, 0).Select

ActiveCell.Formula2R1C1 = "=INDEX(Descriptions!" & Range("GrwDef").Address(ReferenceStyle:=xlR1C1) & ",MATCH(@'IR115 Data'!" & Range("Asset").Address(ReferenceStyle:=xlR1C1) & ",Descriptions!" & Range("DescAsset").Address(ReferenceStyle:=xlR1C1) & ",FALSE))"

 

[Alex Blakenburg]

OK that is quite different to what I was thinking.
You are only capturing Column numbers. So your formula would need to look something like this:

ActiveCell.Formula2R1C1 = "=INDEX(Descriptions!" & Columns(GrwDef).Address(ReferenceStyle:=xlR1C1) & ",MATCH('IR115 Data'!" & Cells(ActiveCell.Row, Asset).Address(ReferenceStyle:=xlR1C1) & ",Descriptions!" & Columns(DescAsset).Address(ReferenceStyle:=xlR1C1) & ",FALSE))"

The code needs a bit of a rewrite. Ideally you don't want to have Select or Activate appear in your code.
If you want me to rewrite it just let me know but then it would be helpful to know:
• Why are you looking in rows 1 - 8 ? Wouldn't the headings always be in a specific row.
• Your formula seems to be using Entire Column referencing can we limit it to a smaller range ? in which case
what Column in the Descriptions sheet will always be populated and can be used to work out what the last row is ?
• Isn't Fund Code a heading ? Why is the find using all the rows in IR Data ?

 

[Alroj]

Hi Alex,

Thank you very much for your response!!!. It was a good starting point, I had to modify your formula slightly (in red) for it to work . The final formula was:

 ActiveCell.Formula2R1C1 = "=INDEX(Descriptions!" & Columns(GrwDef).Address(ReferenceStyle:=xlR1C1) & ",MATCH(@'IR115 - Data Mart PA View Repor'!" & [B][COLOR=rgb(184, 49, 47)]Columns(Asset).Address(ReferenceStyle:=xlR1C1)[/COLOR][/B] & ",Descriptions!" & Columns(DescAsset).Address(ReferenceStyle:=xlR1C1) & ",FALSE))"

The trick with the file I am dealing with is that the headings don't necessarily show up in the same cell each month I open the file. The only certainty that I have is that the heading names won't change althought they can show up in a different column. I use the name "Fund Code" as a kind of an anchor because I know the remaining data will be around it.

I would like to use ranges rather that the whole column, any assistance would be greatly appreciated please!

Cheers

 

[Alex Blakenburg]

any assistance would be greatly appreciated please!

It sounds like you now have the formula working, does this mean you want me to show you a cleaned up version of the code.
If so can you answer the other 2 questions above being:
• In the description sheet is the heading row always going to be row 1 or are you really expecting it to be any row from 1 to 8 ?
• In the IR sheet is "Fund Code" always going to be in Row 1 ?

 

[Alroj]

Hi Alex,

Thank you for your response.

Yes, the formula is working now, thank you!. Regarding your questions, the heading row could be located anywhere between row 1 and 8, throught history it's never gone below row 8. So I am confident that it will always show up between those rows. Similarly, "Fund Code" of the IR sheet would show up anywhere between row 1 and 8.

If you could do your magic with a clean version, it would be greatly appreciated

Cheers

 

[Alex Blakenburg]

In your current code on the IR sheet you are looking for "Level Name 1" in rows 1-8 but "Fund Code" in rows 1 to Last Row.
If I don't change that the code and don't change the approach you are using then the code might look something like this:
(Note: Under normal circumstances you would have some checks in case the Find lines don't actually find what they are looking for)

Sub InsertFormula()

Dim DescAsset As Long
Dim GrwDef As Long
Dim Asset As Long
Dim lastrow As Long

' Source sheet
With Sheets("Descriptions").Range("a1:az8")
    'Setting ranges to be used in formulas
    DescAsset = .Find(What:="Asset Class", LookIn:=xlValues).Column
    GrwDef = .Find(What:="Growth/Defensive", LookIn:=xlValues).Column
End With
    
'destination sheet
With Sheets("IR115 Data")
    lastrow = .Cells(Rows.Count, "b").End(xlUp).Row
    Asset = .Range("a1:az8").Find(What:="Level Name 1", LookIn:=xlValues).Column
    
    With .Range("A1:aa" & lastrow).Find(What:="Fund Code", LookIn:=xlValues).End(xlToRight)
        .Offset(0, 1).Value = "Growth/Defensive"
        .Offset(1, 1).Formula2R1C1 = _
            "=INDEX(Descriptions!" & Columns(GrwDef).Address(ReferenceStyle:=xlR1C1) & ",MATCH(@'IR115 Data'!" & Columns(Asset).Address(ReferenceStyle:=xlR1C1) & ",Descriptions!" & Columns(DescAsset).Address(ReferenceStyle:=xlR1C1) & ",FALSE))"
    End With
End With

End Sub

 

[Alroj]

Hi Alex,

Brilliant! Thank you for sharing your knowledge. Your code works perfectly!

Much appreciated

Al

 

[Alex Blakenburg]

You're welcome. Glad I could help.