How to Fit Any Formula in Formula Line in VBA

[Prasad K]

how can i fit any type of formula in formula line in vba code

here i have applied simple vba to apply formula based on cell selection from running vba macro and it's working perfectly when i placing formula into formulaR1C1 and here what i want how can apply only formula to formula line code without converting formula to formulaR1C1

here what the problem is when i placing only formula in formula line of code of vba then that line of code is changed to red color

Sub Multi()
    Dim Formula As String
    
    Formula = "=VALUE(LEFT(G10,FIND(" ",G10))*VALUE(LEFT(H10,FIND(" ",H10)-1)))"
    
    With Selection
        .Formula = Formula
        .Value = .Value
    End With
End Sub

 

[bebo021999]

What is selection range should be?
For example, if you select I10:I100,
I10 formula wouls refer to G10 & H10
I11 formula wouls refer to G11 & H11
????

 

[Prasad K]

if you select I10:I100,

Yes Correct Sir

if i place Formula =VALUE(LEFT(G10,FIND(" ",G10))*VALUE(LEFT(H10,FIND(" ",H10)-1))) to

In VBA Line Formula Then This Line of Code is changing to Red Color Why

Formula = "=VALUE(LEFT(G10,FIND(" ",G10))*VALUE(LEFT(H10,FIND(" ",H10)-1)))"

 

[bebo021999]

Post a screenshot/image please

 

[Prasad K]

Post a screenshot/image please

Post a screenshot/image please

 

[bebo021999]

This image does not help much (VBA window).
Try to post worksheet.

 

[Peter_SSs]

1. Not a good idea to use a variable the same as a word that vba already uses as part of its language (Formula)
2. When you want to include quote marks in a string (eg formula string) you need to double the quote marks.

Sub Multi()
    Dim sFormula As String
   
    sFormula = "=VALUE(LEFT(G10,FIND("" "",G10))*VALUE(LEFT(H10,FIND("" "",H10)-1)))"
   
    With Selection
        .Formula = sFormula
        .Value = .Value
    End With
End Sub

 

[Prasad K]

1. Not a good idea to use a variable the same as a word that vba already uses as part of its language (Formula)
2. When you want to include quote marks in a string (eg formula string) you need to double the quote marks.

Sub Multi()
    Dim sFormula As String
  
    sFormula = "=VALUE(LEFT(G10,FIND("" "",G10))*VALUE(LEFT(H10,FIND("" "",H10)-1)))"
  
    With Selection
        .Formula = sFormula
        .Value = .Value
    End With
End Sub

Thank you so much @Peter

i don't know how to fit formula within double the quote marks in VBA

 

[Peter_SSs]

i don't know how to fit formula within double the quote marks in VBA

I'm not sure what you mean by that.
I gave you an example in the code above.

Your normal formula:
=VALUE(LEFT(G10,FIND(" ",G10))*VALUE(LEFT(H10,FIND(" ",H10)-1)))

When applying it with vba:
=VALUE(LEFT(G10,FIND("" "",G10))*VALUE(LEFT(H10,FIND("" "",H10)-1)))

 

[Prasad K]

I'm not sure what you mean by that.

Peter I said Thank you for how to write Formula in VBA line

Once again thank you so much