Formula syntax

[Ezguy4u]

I have 2 worksheets. On sheet 1 I have D5 in cell D5. On sheet 2 I have D6 and D7 in cells D6 and D7. When I step thru the program, I get D5 in cell A1. I get D6 in cell A2. What I would like to do is combine the 2 methods and get D7 in cell A3. What I get is a run time error. It seems reasonable that I should be able to do this. Any help would be appreciated. Thank you for your time.

Sub Formula1()

Range("A1").Formula = "=" & Cells(5, 4)

Range("A2").Formula = "=('" & Sheets(2).Name & "'!D6)"

Range("A3").Formula = "=('" & Sheets(2).Name & "' ""!"" '" & Cells(7, 4) & "')"

End Sub

 

[johnnyL]

I don't believe you can use 'cells(x,y)' in a formula.

You could do it like this:

Sub Formula1()

Range("A1").Formula = "=" & Cells(5, 4)

Range("A2").Formula = "=('" & Sheets(2).Name & "'!D6)"

Range("A3").FormulaR1C1 = "=('" & Sheets(2).Name & "'!R7C4)"

End Sub

 

[Peter_SSs]

I don't believe you can use 'cells(x,y)' in a formula.

You can if you use its Address property rather than the default property of Value.

@Ezguy4u
I'm not sure why, but you have included a heap more single & double quote marks in the last formula compared to the previous one. You also have parentheses in both the last two that are not required.
Try this for the last one.

Range("A3").Formula = "='" & Sheets(2).Name & "'!" & Cells(7, 4).Address(0,0)

BTW, not a good idea to name a procedure with a word that vba already uses as part of its nomenclature (eg Formula1).

 

[johnnyL]

You can if you use its Address property rather than the default property of Value.

Ahhh. Thank you for that info. Makes sense.

 

[Ezguy4u]

Johnny, Your line does work. I will keep it in my toolbox. Peter, Your solution works the best. My 3rd solution was just to give people an idea of what I wanted. It's lik loking at a centance thts has spling errors and stlll know what thy want. Good advice on the naming procedure.
Thank you all for your time

 

[johnnyL]

Yeah but my line of code requires less typing.

 

[Peter_SSs]

You're welcome.
(I didn't understand one of the sentences in your post though. )

 

[johnnyL]

My 3rd solution was just to give people an idea of what I wanted. It's lik loking at a centance thts has spling errors and stlll know what thy want.

Most probable conversion =:

My 3rd solution was just to give people an idea of what I wanted. It's like looking at a sentence that has spelling errors and stlll know what they want.

 

[Peter_SSs]

Most probable conversion =:

My comment was a joke.
(BTW, your conversion still has as a spelling error. )

 

[johnnyL]

You stlll know what I was saying though. ROTFLMAO