Add code to row using VBA

[KCressy]

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

 

[iggydarsa]

right clik on the tab where the sheet name is and select View Code on the new window paste this 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]

try

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]

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]

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]

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]

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]

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

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