VBA code to insert a formula within a spreadsheet

legaltrends · May 10th 2006

I have this spreadsheet that has the following formulas copied down 4 columns starting in column 4 or "D"

D=SUM(1*(LEFT(C2,1)))
E=SUM(1*(MID($C2,2,1)))
F=SUM(1*(MID($C2,3,1)))
G=CONCATENATE((MIN(LEFT($C2),MID($C2,2,1),RIGHT($C2))),(MEDIAN(LEFT($C2),MID($C2,2,1),RIGHT($C2))),(MAX(LEFT($C2),MID($C2,2,1),RIGHT($C2))))

Is there any way that I can take these formulas & create a vba that will automatically insert this throughout my spreadsheet? or at least shorten my formulas?

I read that it would make spreadsheet smaller & thus calc faster. Is this true?

HaHoBe · May 10th 2006

Re: Is there a way to create code that will insert a formula within a spreadsheet?

Hi, legaltrends,

you could use the Worksheet_Change-Event of the worksheet and limit it to just column C. If anything is entered into a cell in column C the following code is triggered (sorry, didn´t get your formula in G to work - this part of the code is missing here):

Code

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Columns("C:C")) Is Nothing Then Exit Sub
Application.EnableEvents = False
With Target
  .Offset(0, 1).Value = Left(Target.Value, 1)
  .Offset(0, 2).Value = Mid(Target.Value, 2, 1)
  .Offset(0, 3).Value = Mid(Target.Value, 3, 1)
End With
Application.EnableEvents = True
End Sub

Display More

No formulas should make up a bit of speed...

Ciao,
Holger

VBA code to insert a formula within a spreadsheet

Participate now!

Share