Using VBA .Formula to add a formula typed into an excel cell

[Mr Wilson]

Hello, Sorry in advance if I'm not using the correct terminology but hopefully I can explain it well enough. This is for some code I am still writing so don't have a complete code to post but I have copied what I think are the relevant bits.

I'm trying to get a formula added to a range by using a string variable based on an excel cell (I want multiple formula's and the ability to change them without hardcoding into the VBA).

Key bits I'm trying to get to work are:

Dim strFormula as String
strRange = Sheet1!A:AP
strHeaderColumn = 2

strFormula = wks1.Cells(intMasterRow, 5)
wks2.range("B2").Formula = strFormula

The issue I have is:

If I enter
strFormula = "=IFERROR(VLOOKUP(A2," & strRange & "," & strHeaderColumn & ",0),"""")"
in the Immediate window it recognises it as a formula and strFormula shows as
=IFERROR(VLOOKUP(A2,,Sheet1!A:AP,2,0),"")
This works as expected when executing 'wks2.range("B2").Formula = strFormula'.

If I enter
"=IFERROR(VLOOKUP(A2," & strRange & "," & strHeaderColumn & ",0),"""")"
in wks2.Cells(intMasterRow, 5) then strFormula shows as
"=IFERROR(VLOOKUP(A2," & strRange & "," & strHeaderColumn & ",0),"""")"
when executing 'wks2.range("B2").Formula = strFormula' it adds this text in, instead of the expected formula.

The cell in wks2 is set as text and I've tried various variations of quote marks to try and get it to work with no luck.

Is there a way to get VBA to interpret the Excel Cell value as it would in the Immediate Window and get the correct formula entered? I'm guessing either I have the quotes set up wrong for what I'm doing and/or there is a function I can add to get it to work.

Hopefully the issue is clear, if not let me know.

many thanks

 

[Micron]

Did you type in that code or copy and paste it? I'm thinking typed. You have an extra comma in a formula, which I expect would cause an issue one way or the other.
=IFERROR(VLOOKUP(A2,,Sheet1!A:AP,2,0),"")
Best to copy & paste, and for that matter, probably the whole sub unless maybe it's huge and most of it isn't needed. When you post code, please use code tags (vba button on posting toolbar) to maintain indentation and readability. Difficult to troubleshoot code issues when reading about what you say you are doing versus seeing the actual code.

 

[Mr Wilson]

Copied and then amended as my code is still a work in progress (and fairly long with a lot of parts that work fine). It shouldn't have the double comma.
=IFERROR(VLOOKUP(A2,Sheet1!A:AP,2,0),"")

I've attached some screenshots showing the below which will hopefully help.

My code has the following:

                strFormula = wksMappingMaster.Cells(strMappingMasterRow, 5)
                wksCompare.Range(Cells(2, strCompareColumn), Cells(strCompareRows, strCompareColumn)).Formula = strFormula

After running the 1st line in the above, in the Immediate Window, I get:
?strFormula
"=IFERROR(VLOOKUP(TEXT(A2,""0"")," & strRange & "," & StrCascadeHeaderColumn & ",0),"""")"

If in the Immediate Window I enter:
strFormula = "=IFERROR(VLOOKUP(TEXT(A2,""0"")," & strRange & "," & StrCascadeHeaderColumn & ",0),"""")"
i.e. the same text

It updates strFormula to:
?strFormula
=IFERROR(VLOOKUP(TEXT(A2,"0"),Workday!A:CD,2,0),"")

What I'm after is a way to get the code to treat strFormula to get the result at the end. I've tried a few combination of different quote marks but either haven't found the right combination or there is something I'm missing.

 

[Micron]

Sorry, text in your pics too small to read on my laptop. Posting actual code as suggested might help anyone to help you.

 

[RoryA]

You cannot type VBA variables in the cell as part of the formula and then expect the code automatically to convert those to their values at runtime, which is what I think you are saying you are trying to do. What you could do is have your code replace placeholders in the string with the current value of your variables - for example:

Cell:

=IFERROR(VLOOKUP(TEXT(A2,"0"),SomeRange,2,0),"")

Code:

strFormula = Replace(wksMappingMaster.Cells(strMappingMasterRow, 5), "SomeRange", strRange)