burley
03-13-2008, 03:14 AM
Hi, XLD on this forum was kind enough to help me with some code to tackle a problem I had and it worked perfectly, but now I need to change it slightly and i'm not sure how.
The code below enters a formula on the Sheet "cost" if there is a corrasponding number on the sheet "Build".
The formula it puts in is =Build!B33*Build!U33 or =Build!B110*Build!AF110 etc.
Basically it holds the Build!B in the first part of the formula where ever it enters it.
On my next sheet called "Usage" I would like to use the same code to enter the same formula but now Hold the Row (6) but let the Column change, so now the same formula would read Build!U6*Build!U33 or Build!AF6*Build!AF110 etc...
Assuming my poor explanation can be understood, Can anyone help again?
Thank you so much
Public Sub ProcessData()
Dim cell As Range
With Worksheets("Build")
For Each cell In .Range("C7:IV2214").SpecialCells(xlCellTypeConstant s)
If IsNumeric(cell.Value) Then
Worksheets("cost").Range(cell.Address(False, False)).Formula = _
"=build!B" & cell.Row & "*build!" & cell.Address(False, False)
End If
Next cell
End With
End Sub
The code below enters a formula on the Sheet "cost" if there is a corrasponding number on the sheet "Build".
The formula it puts in is =Build!B33*Build!U33 or =Build!B110*Build!AF110 etc.
Basically it holds the Build!B in the first part of the formula where ever it enters it.
On my next sheet called "Usage" I would like to use the same code to enter the same formula but now Hold the Row (6) but let the Column change, so now the same formula would read Build!U6*Build!U33 or Build!AF6*Build!AF110 etc...
Assuming my poor explanation can be understood, Can anyone help again?
Thank you so much
Public Sub ProcessData()
Dim cell As Range
With Worksheets("Build")
For Each cell In .Range("C7:IV2214").SpecialCells(xlCellTypeConstant s)
If IsNumeric(cell.Value) Then
Worksheets("cost").Range(cell.Address(False, False)).Formula = _
"=build!B" & cell.Row & "*build!" & cell.Address(False, False)
End If
Next cell
End With
End Sub