Excel VBA formula using a changing cell 's value and a constant

[JerryKlmns]

Hi everyone.

In Excel VBA this

.Range("H" & iRow).Formula = "=G" & iRow & "- S" & iRow

works fine and at line 2 results in H2:=G2-S2
and also this

.Range("H" & iRow).Formula = "=G" & iRow & "- 0.77"

returns the same result (where S2=0.77)

When I change to this

.Range("H" & iRow).Formula = "=G" & iRow & "-" .Range("S"& iRow).Value

I expect H2:=G2-0.77 but I get an Application Error..

Why can't I use a cell's value inside a formula? Am I setting it the wrong way?

 

[combo]

You miss "&":

.Range("H" & iRow).Formula = "=G" & iRow & "-" [!]&[/!] .Range("S"& iRow).Value

If you still have application error reported, there may be a problem with regional settings. In this case use rather FormulaLocal:

.Range("H" & iRow).FormulaLocal = "=G" & iRow & "-" & .Range("S" & iRow).Value

combo

 

[osx99]

Also if cell .Range("S" & iRow) is empty then you will only have half a formula and will throw the Application error. One of many possible ways around it could be

If IsEmpty(.Range("S" & irow).Value) Then
.Range("H" & irow).Formula = "=G" & irow & "-" & "0"
Else
.Range("H" & irow).Formula = "=G" & irow & "-" & .Range("S" & irow).Value
End If

 

[JerryKlmns]

Thanx combo. I saw that, after the completion of the project...