Add code to row using VBA

KCressy

New Member
Joined
Jan 10, 2008
Messages
5
Hello All,

I have a 10 formulas that operate along an Excel row. Initially I had
copied the formulas into all rows in a spreadsheet up to row 3000.

However this makes my spreadsheet unnecessarily large. What I would
therefore like to do is copy the formulas ONLY if data is added into
the first column.

Is this possible using VBA?

For example if I have the formula =A1+B1 operating in cell C1 how
could I copy the formula ONLY when data is added to cell s in column
‘A’?

Regards,

Kevin
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

iggydarsa

Well-known Member
Joined
Jun 28, 2005
Messages
1,647
right clik on the tab where the sheet name is and select View Code on the new window paste this code

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
    Range("C" & Target.Row).Formula = "=A" & Target.Row & "+B" & Target.Row
End If
End Sub
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
try
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Long
If Intersect(Target, Tange("a:b")) Is Nothing Then Exit Sub
x = Target.Row
Application.EnableEvents = False
With Rows(x)
    .Cells(3).Value = Application.Sum(.Cells(1), .Cells(2))
End With
Application.EnableEvents = True
End Sub
 

KCressy

New Member
Joined
Jan 10, 2008
Messages
5
Thanks for that.

How would I add formulas for the other cells?

Here is an example of one of the more complex formulas I currently have in the sheet:

=IF(T10="",CONCATENATE(Q10,R10,S10," ",U10,V10,W10),CONCATENATE(Q10,R10,S10,T10," ",U10,V10,W10))

Would I be best to build this formula in VBA (something that I have not done before) and add to your example? OR is it best for me to save an example of the query in a hidden cell in the sheet and then reference this in VBA using a variable to replace the row number (i.e. row 10 in the above formula).

As I have quite a liot of formulas I am a bit put off in trying to build all of these again in VBA! However I am not sure what would be the most simple and also the best in terms of performance.

Either way I’ll need help on both of these! And would be most grateful!

Regards

Kevin
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995

ADVERTISEMENT

However this makes my spreadsheet unnecessarily large.
I thought you didn't want to leave the formula in the cell...
What is your ultimate purpose ?
 

KCressy

New Member
Joined
Jan 10, 2008
Messages
5
Hi jindon,

I think I got a little confused, sorry about that. I was thinking that I could reference the formula in an excel spreadsheet – but I think I am just getting confused. Its seems putting all of my formulas into the VBA code is the best idea.

Can you show me how I could incorporate the longer “=IF” statement formula into your example VBA code?

Regards,

Kevin
 

Boller

Banned
Joined
Apr 11, 2006
Messages
2,328
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, cel As Range
Set rng = Intersect(Target, [A:A])
If Not rng Is Nothing Then
    Application.EnableEvents = False
    On Error Resume Next
    For Each cel In rng
        If cel = "" Then
            cel(1, 3).Resize(, 254).ClearContents
        Else
            [C2:IV2].Copy cel(1, 3)
            cel(1, 3).Resize(, 254).SpecialCells(xlCellTypeConstants).ClearContents
        End If
    Next
    On Error GoTo 0
    Application.EnableEvents = True
End If
End Sub
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi jindon,

I think I got a little confused, sorry about that. I was thinking that I could reference the formula in an excel spreadsheet – but I think I am just getting confused. Its seems putting all of my formulas into the VBA code is the best idea.

Can you show me how I could incorporate the longer “=IF” statement formula into your example VBA code?

Regards,

Kevin
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns(1)) Is Nothing Then Exit Sub
Application.EnableEvents = False
With Range("a2", Range("a" & Rows.Count).End(xlUp)).Offset(,2)
    .Formula = _
    "=CONCATENATE(Q2,R2,S2,If(T2="""","""",T2),"" "",U2,V2,W2)"
End With
Application.EnableEvents = True
End Sub
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,672
Messages
5,597,488
Members
414,146
Latest member
marginmakerb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top