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

- 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

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

©2019 eLuminary LLC. All rights reserved.