Adding Formulas onto existing formulas using VBA

[Akolas]

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:

Range(Cells(x, y), Cells(x, y)).Formula = Range(Cells(x, y), Cells(x, y)).Formula & "Additional string"

 

[JoeMo]

Try:

Range("A1").Formula = Range("A1").Formula & "&string"

 

[Akolas]

Ah, I see what you're going for! Sadly, it didn't work though.

 

[Rick Rothstein]

Ah, I see what you're going for! Sadly, it didn't work though.

See if this works for you...

Cells(x, y).Formula = Cells(x, y).Formula & "&""Additional String"""

 

[Akolas]

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.

formulastring = Range(Cells(x, y), Cells(x, y)).Formula & "Additional String"
Range(Cells(x, y), Cells(x, y)).Formula = formulastring

 

[Rick Rothstein]

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.

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]

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:

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]

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]

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.

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:

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.