Using a string variable as a formula

Hello Everyone,

When I record a macro while creating a formula, I get the following:

ActiveCell.FormulaR1C1 = _"=CONCATENATE(""T"",""QQQQ"",""_"",RIGHT(RC[3],3),""_"",RIGHT(RC[4],3))"

Variable Qcode = "QQQQ"

Variable myString = """T"",""QQQQ"",""_"",RIGHT(RC[3],3),""_"",RIGHT(RC[4],3)"

I have searched the forums and tried different ways to use this in a macro all to no avail

I have tried:

Range("A2").Formula = "=CONCATENATE(" & myString & ")"

Results in error

Range("A2").Formula = "=CONCATENATE(myString)"

Results #NAME (=CONCATENATE(myCombo)) in cell

And also creating another string variable for the entire thing:

myString2 = "=CONCATENATE(""T"",""QQQQ"",""_"",RIGHT(RC[3],3),""_"",RIGHT(RC[4],3))"

Range("A2").Formula = myString2

Results in an error

I am missing something, but I don't know exactly what.

Any and all help is greatly appreciated.

Re: Using a string variable as a formula

Thank you both for the quick replies - I will try this out first thing in the morning.

Re: Using a string variable as a formula

Quote from bryce;778114

I am sure there are a few ways to do this but when ever I try to do this

Code

Range("A2").Formula = "=CONCATENATE(" & myString & ")"

I use a range rather than a string. I would set a range and use that range in the formula with its address like this

Code

Set rng = ws.Range("G4:G6")
    
    ws.Range("G7").formula = "=sum(" & rng.Address & ")"

Thanks, but when I use

Set rng = ws.Range("G4:G6")

I get an "Object Required" error.

Re: Using a string variable as a formula

Quote from mrmmickle1;778115

If you have these values in Cell A1 (XXX) and A2 (YYY) and you want A3 to equal your string then you can use the following.....

Code

Sub Test()
    Range("A3") = "T" & "QQQQ" & "_" & Right(Range("A1"), 3) & "_" & Right(Range("A2"), 3)
End Sub

A3 now has this value:

[TABLE="width: 114"]

[tr]

[TD="class: xl63, width: 114"]TQQQQ_XXX_YYY[/TD]

[/tr]

[/TABLE]

Display More

Thanks - this worked, but I have 18,644 (not unusual for me) cells to put this in - when I use this method and a for loop it takes approx. 45 seconds to run. I suspect that if I could get the formula in and copy down, it would be faster.

Re: Using a string variable as a formula

Update - I have another way to prep the data, by copy and paste, then filter for unique values that will reduce the number of cells needed to copy into to approx. 1200 which will greatly reduce the time required.

Thanks again!!