Adding Formulas onto existing formulas using VBA

Akolas

New Member
Joined
Apr 20, 2015
Messages
10
I want to be able to add additional parts of a string onto an already existing formula in a cell, but it's throwing me an error. I'm trying to get something like this to work:
Code:
Range(Cells(x, y), Cells(x, y)).Formula = Range(Cells(x, y), Cells(x, y)).Formula & "Additional string"
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,411
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Try:

Rich (BB code):
Range("A1").Formula = Range("A1").Formula & "&string"
 

Akolas

New Member
Joined
Apr 20, 2015
Messages
10

ADVERTISEMENT

Thanks for the help, Mr. Rothstein, but that didn't quite do the trick either. As far as I can tell, I can't mix variables and fixed strings. I did find a work around though: if I construct my string and save it as something else, and then call it, then it's work properly.
Code:
formulastring = Range(Cells(x, y), Cells(x, y)).Formula & "Additional String"
Range(Cells(x, y), Cells(x, y)).Formula = formulastring
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,858
Office Version
  1. 2010
Platform
  1. Windows
Thanks for the help, Mr. Rothstein, but that didn't quite do the trick either. As far as I can tell, I can't mix variables and fixed strings. I did find a work around though: if I construct my string and save it as something else, and then call it, then it's work properly.
Code:
formulastring = Range(Cells(x, y), Cells(x, y)).Formula & "Additional String"
Range(Cells(x, y), Cells(x, y)).Formula = formulastring
Perhaps if you show us the "additional string", that might help. We all took it to mean a string constant, but you seem to be saying it is something else. Also, why are you using...

Range(Cells(x, y), Cells(x, y))

Since both arguments to the Range property are the same cell reference, you can just use the cell reference directly instead...

Cells(x, y)
 

Akolas

New Member
Joined
Apr 20, 2015
Messages
10

ADVERTISEMENT

I was using a range because I couldn't get .formula to work with just the Cells(x, y), but I could with the range. I have no idea why. My full code is a little more involved, and I was just trying to simplify down to the issue at hand. Here's the full snippet of code that's erroring, even though it's a small part of the full code:

Code:
For i = startrow To 500
    If Cells(i, 4).Value <> Cells(i + 1, 4) Then
        Range(Cells(z, 1), Cells(i, 9)).name = "_" & Replace(Replace(Cells(z, 4).Value, " ", ""), "-", "")
        name = "_" & Replace(Replace(Cells(z, 4).Value, " ", ""), "-", "")
        z = i + 1
        Sheets("Time to Fill Data 2").Activate
        For x = 2 To 500
            For y = 11 To 19
                formulastring = Range(Cells(x, y), Cells(x, y)).Formula & "IF('Time to Fill Data 2'!Y5=" & counter & _
                ", INDEX(" & name & ", " & Cells(x, 19) & ", " & Cells(2, y + 9) & "), "
                Range(Cells(x, y), Cells(x, y)).Formula = formulastring
            Next y
        Next x
        counter = counter + 1
    End If
Next i
 

Akolas

New Member
Joined
Apr 20, 2015
Messages
10
I think I found the main problem. If a cells().function isn't complete (like what I'm doing when I'm building it), it errors in VBA. I was hoping it would just insert the text and then execute the function at the end of the macro, but that doesn't seem to be the case.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,858
Office Version
  1. 2010
Platform
  1. Windows
I was using a range because I couldn't get .formula to work with just the Cells(x, y), but I could with the range. I have no idea why.
Trust me when I tell you that this...

Range(Cells(x, y), Cells(x, y)).Formula

is exactly equivalent to this...

Cells(x, y).Formula

and it will work fine, so I am not sure why you say it wouldn't work for you. Were you, perhaps, noticing that Cells(x,y) does not provide an intellisense listing of properties and methods like Range does? I don't know why that is so, but if you type .Formula after it, it will work correctly.



Akolas;4195956My full code is a little more involved said:
Usually that is a bad idea as the real problem may be hidden by your simplification.



Here's the full snippet of code that's erroring, even though it's a small part of the full code:

Code:
formulastring = Range(Cells(x, y), Cells(x, y)).Formula & "IF('Time to Fill Data 2'!Y5=" & counter & _
                ", INDEX(" & name & ", " & Cells(x, 19) & ", " & Cells(2, y + 9) & "), "
[/QUOTE]
The formula produced from what you posted above will not work once placed in the cell because the IF function you are adding is not properly formed... your formula ends with a comma and the IF function is not closed off with its own closing parenthesis. There may be other problems with the formula, but I cannot tell without knowing what is in the cell and variables referenced in it.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,119
Messages
5,622,845
Members
415,934
Latest member
adstocking

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top