substitute formula to VBA

Hey chony

what are you trying to do, and what is the problem.

Yotam

maybe this will help:
' function will look like this
Function calc() As String
    Dim s
    K3 = Sheets(1).Cells(3, 11)
    E3 = Sheets(1).Cells(3, 5)
    If K3 > 0 Then
        s = (K3 - E3) / E3 * 100
    Else
        s = ""
    End If
    calc = s
End Function

'and then calling it like follows
Sub test()
   Dim msg
   msg = calc()
   MsgBox msg
End Sub

hi yotam
tnx again for the help with the other code.

i do some calculation by functions, i like to replace them to calculation by code , the function is
=IF(K3>0,(K3-E3)/E3*100,"")
=IF(K4>0,(K4-E4)/E4*100,"") etc. the same function down to row 570  .
the meaning (in this case for row 3 )

if k3 is not empty then calculate the gape between k3 & E3 by percentage (K3-E3)/E3*100 if k3 empty do nothing.

hope i am clear (my english good as my Programming)

choni

saw your comment after my second post
two things
i need the same calculation  for row 4 down to row 570
how the results can be disply in colum R


tnx

In VBA use the IIF fucntion, which is IDENTICAL in form:


Dim K3 as Variant
Dim E3 as Variant
Dim Ans as String
    K3 = Sheets(1).Cells(3, 11)
    E3 = Sheets(1).Cells(3, 5)

Ans = IIF(K3>0,(K3-E3)/E3*100,"")

AW

hi yotam & Arthur

yotam the code work great , i was glad to read your explanation .

thank you

choni