How to incorporate anformula into vba script

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:41 PM GMT

Question

- I have a VBA script which removes a formula from a cell after running and would like to bring the formula back.  Below is a portion of the script where it copies and paste the formula from another cell but I would like to incorporate the actual formula into the VBA script (if possible)  Thanks Frank

If Len(Target.Value) = 7 Then

           Target = Left(Target, 2) & "-" & Target.Offset(0, -2).Value & Right(Target, 5)
           Range("d7").Select
          Selection.Copy
          Target.Offset(0, 1).Select
          ActiveSheet.Paste
          Application.CutCopyMode = False 'Target = Target.Offset(0, -1).Value
          ActiveCell.Offset(0, -1).Select
        
           ElseIf Len(Target.Value) = 8 Then
           Target = Left(Target, 2) & "-" & Target.Offset(0, -2).Value & Right(Target, 6)
           Range("d7").Select
        Selection.Copy
         Target.Offset(0, 1).Select
         ActiveSheet.Paste
          Application.CutCopyMode = False
          ActiveCell.Offset(0, -1).Select
                  
           ElseIf Len(Target.Value) = 9 Then
           Target = Left(Target, 2) & "-" & Target.Offset(0, -2).Value & Right(Target, 7)
           Range("d7").Select
          Selection.Copy
          Target.Offset(0, 1).Select
          ActiveSheet.Paste
          Application.CutCopyMode = False
        ActiveCell.Offset(0, -1).Select

Answer

Frank,

Range("B9").formula = "=Sum(A1:A100)"

if you had a formula like

=if(A1="","",Sum(A1:A100))

then in VBA it would be


Range("B9").formula = "=If(A1="""","""",Sum(A1:A100))"

so any embedded double quotes must be doubled.

Hope that answers the question. If not, then post a followup with an example of the type of answer you would want.

Advertisement

©2017 eLuminary LLC. All rights reserved.