PDA

View Full Version : Solved: Changing formula on massive worksheet needs to change again slightly



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

Simon Lloyd
03-13-2008, 03:31 AM
Probably...
change:
Worksheets("cost").Range(cell.Address(False, False)).Formula = _
"=build!B" & cell.Row & "*build!" & cell.Address(False, False)
For..

Worksheets("cost").Range(cell.Address(False, False)).Formula = _
"=build!B" & cell.Column & "*build!" & cell.Address(False, False)

burley
03-13-2008, 03:48 AM
Hi Simon,
Thank you for your post, but it does not seem to work the way I need it to. It's probably my lousy explanation, but yours increased the Row number as it entered formula along one row, and kept the Column as B. I need the formula to keep to Row 6 but allow it to move from B to C to D etc. depending which column it is putting the formula in...
ie. in Cell usage!G132 the formula should be =Build!G6*Build!G132
Sorry if that is even less clear
Thank you

Bob Phillips
03-13-2008, 03:54 AM
I don't think it is quite that straightforward SIMON.

burley, in the previous iteration, cost!C7 would end up with =B7*C7, cost!D7 would end up with =B7*D8. Should the new formula be

cost!C7: =C7*C7, C6*C7 or something else
cost!D7: =C7*D7, C6*D7 or something else

Bob Phillips
03-13-2008, 03:56 AM
Our posts crossed <g>



Public Sub ProcessData()
Dim cell As Range

With Worksheets("Build")

For Each cell In .Range("C7:IV2214").SpecialCells(xlCellTypeConstants)

If IsNumeric(cell.Value) Then

Worksheets("cost").Range(cell.Address(False, False)).FormulaR1C1 = _
"=build!R6C" & cell.Column & "*build!R" & cell.Row & "C" & cell.Column
End If
Next cell
End With

End Sub

Simon Lloyd
03-13-2008, 03:56 AM
I don't think it is quite that straightforward SIMON.
Please don't shout Bob, i'm trying! (my mum says!)

burley
03-13-2008, 04:16 AM
Thank you again XLD it works perfectly

Simon, I have read the Cross Posting info, I am new to this so apologies I did not realise. The cross posting info makes good sense and I will not do it again.

Thank you both

Burley

Simon Lloyd
03-13-2008, 04:31 AM
Burley, i didn't know you had cross posted but if you did you should really supply a link in all the forums you posted in!, the info about cross posting is always in my signature line and was not an instruction to you.

Glad you got sorted after my half witted attempt to help you!

Bob Phillips
03-13-2008, 05:39 AM
Please don't shout Bob, i'm trying! (my mum says!)

Your mum is right.