PDA

View Full Version : Solved: Enter a changing formula a massive worksheet



burley
03-12-2008, 10:28 AM
Excel 2003
Please forgive me if this is not explained very well, I am totally new to VBA but need to solve a problem..
I have to enter a large number of simple formulas on a worksheet (sheet2), the formula is dependant on where it's being entered. eg.
in Sheet 2 cell E6 the formula would need to be =+Sheet1!$A6*Sheet1!E6 and in Sheet 2 cell h9 it would be =+Sheet1!$A9*Sheet1!H9 and Sheet 2 cell bd53 would be =+Sheet1!$A53*Sheet1!bd53 and so on...
However, I don't want that formula in every cell, only in cells where there is a number in the corrasponding cell in Sheet 1.
I have no idea if that makes sense, but the reason I can't do it by hand is the sheet is massive C3:IV2000!! although most of the cells are blank.

Sorry if i'm asking too much or in the wrong way, but any help would be greatly appreciated

Paul

Bob Phillips
03-12-2008, 10:57 AM
Give this a whirl



Public Sub ProcessData()
Dim cell As Range

With Worksheets("Sheet1")

For Each cell In .Range("C3:IV2000").SpecialCells(xlCellTypeConstants)

If IsNumeric(cell.Value) Then

Worksheets("Sheet2").Range(cell.Address(False, False)).Formula = _
"=sheet1!A" & cell.Row & "*Sheet1!" & cell.Address(False, False)
End If
Next cell
End With

End Sub

burley
03-12-2008, 11:01 AM
XLD,
you are a legend...

Thank you