Apply formula Calculation by VBA Value ="to Forumula"

**DocAElstein** · 04-18-2020, 02:07 AM

Hi
Your macro is very good.
You have written good coding.
It is almost perfect.

There is just one very small problem. It is just because VBA is confused when quotes are inside quotes. VBA does not like to see quotes inside other quotes. It get confused.

If VBA see this: …_

Code:

    "     "     "

_.. VBA does not know what it is. VBA thinks maybe it is either this

Code:

    "     "     "

or this

Code:

    "     "     "

VBA does not like to see inside quotes 1 quote or 3 quotes or 5 quotes … etc

VBA must always see 2 quotes "" ( or 4 quotes m"""", or 6 quotes """""" )etc..
We must do a trick , like this

Code:

    "     ""    "

Or this:

Code:

    "     ""    ""    "

If you do this trick, then VBA will see for "" just one quote.

So in your macro , like this is the problem: "________"_D is equal to H_"____"

Code:

"=IF(H2>D2,1/100*H2,IF(H2<D2,1/100*H2,"D is equal to H"))"

We must do trick: "________""_D is equal to H_""____"

Code:

"=IF(H2>D2,1/100*H2,IF(H2<D2,1/100*H2,""D is equal to H""))"

So in your macro,
change the single quotes inside the outermost enclosing quotes to double quotes
like this:
______________Ws1.Range(" 2: " & Lr1 & "").Value =__"__________""_D is equal to H_""____"

Code:

Sub STEP8()
  Dim Wb1 As Workbook, Ws1 As Worksheet, Lr1 As Long
  Set Wb1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls")
  Set Ws1 = Wb1.Worksheets.Item(1)
  Let Lr1 = Ws1.Range("H" & Ws1.Rows.Count).End(xlUp).Row
   Ws1.Range("J2:J" & Lr1 & "").Value = "=IF(H2>D2,1/100*H2,IF(H2<D2,1/100*H2,""D is equal to H""))"
   Ws1.Range("J2:J" & Lr1 & "").Value = Ws1.Range("J2:J" & Lr1 & "").Value
   Ws1.Range("K2:K" & Lr1 & "").Value = "=IF(H2>D2,H2-J2,IF(H2<D2,H2+J2,""D is equal to H""))"
   Ws1.Range("K2:K" & Lr1 & "").Value = Ws1.Range("K2:K" & Lr1 & "").Value
   Wb1.Save
   Wb1.Close
End Sub