Adding formulas to cells dependant on data in another cell.

[KevinC]

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

 

[cush]

In the left panel of your VB Editor there is a list of your Worksheets under
a heading Microsoft Excel Objects. Click on the sheet where you want to
apply this action. then in the right panel, paste the following code.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A:A")) Is Nothing _
And Target.Offset(0, 1) <> "" Then 'see if col B has data
'copy the formulas in col C:L down one row
Range(Target.Offset(0, 2), Target.Offset(0, 12)).FillDown

End If
End Sub

You may need to adjust the Range("A:A") so to something like Range("A3"A3000")
Be sure that you do not have other data or formulas in the col C:L that
would get overwritten. It is best to keep this worksheet "dedicated" to this
one task, without cluttering it up. The macro will run EVERY time you enter
something in any cell in this worksheet, hence the first line of code in the
macro.

 

[KevinC]

Hi cush,

That seems to work.

There is one limitation with the code however (for my purposes that
is).

If the user does not enter any data in column A then no formulas are
copied to the following row. In my spreadsheet it is possible that
they user may not need to/or may forget to add data into column A.

No that I think about it I guess I need some code that says something
along the lines of:

"If there is any data in a row apply the following formulas:
- in colum C find the result of A*B
- in colum E find the result of A/B
- etc..."

I think what I was hoping I might be able to do is to write the
formulas in VBA and then apply them if there is data in the row.

Does this make sense? any idea if it is possible?

Thanks,

Kevin

 

[KevinC]

Still got no where with this, so if anyone can help I would be
greatful!