PDA

View Full Version : Solved: hi code tweeking



khalid79m
03-27-2008, 09:10 AM
Range("H3:H" & LastRow).FormulaR1C1 = _
"=SUMPRODUCT(--(Stage2!R3C3:R59999C3=RC3)*(Stage2!R3C5:R59999C5=RC5)*(Stage2!R3C6:R59999C6 =RC6)*(Stage2!R3C9:R59999C9<>""0""))"
Range("H3:H" & LastRow).Select
Run "PasteValues"

Hi I need help with above code

I need to change the way it operates in a bid to speed up my worksheets

I need it to look down column F starting at row 3 and to the lastrow,

If colmun f row 3 has a value in then in column H3 perform the following formula
FormulaR1C1 = _
"=SUMPRODUCT(--(Stage2!R3C3:R59999C3=RC3)*(Stage2!R3C5:R59999C5=RC5)*(Stage2!R3C6:R59999C6 =RC6)*(Stage2!R3C9:R59999C9<>""0""))"
and then copy and paste value. in h3

then go onto column f row 4 and repeat and so on until you hit lastrow.

Thanks for your help in advance

Bob Phillips
03-27-2008, 09:35 AM
Just add another condition to the SP.

And better to calculate the last row than to always assume 59999.

khalid79m
03-27-2008, 09:37 AM
thats fine but I want it copy and paste value on each calculation

for example h3 answers is 50, i want it copy that and paste as values
then go on to the f4 and so on..

khalid79m
03-27-2008, 09:51 AM
Sub zzz()
Dim LastRow As Long
Dim StartAt As Long
Dim i As Long

With Activesheets

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
StartAt = h3
For i = 4 To LastRow

ActiveCell.FormulaR1C1 = _
"=SUMPRODUCT(--(Stage2!R3C3:R59999C3=RC3)*(Stage2!R3C5:R59999C5=RC5)*(Stage2!R3C6:R59999C6 =RC6)*(Stage2!R3C9:R59999C9<>""0""))"
ActiveCell.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Next i
End With
End Sub

something like this maybe ??? but how do I select column H FOR the formula:whip

Bob Phillips
03-27-2008, 09:58 AM
Just load all the SP formulae and do a .Value = .Value o the whole range. Minimal change, totall effective.

khalid79m
03-27-2008, 09:59 AM
Sorry im fairly new to vba, so what you have said doesnt seem to make any sense for me ? Could explain or show example ?

Bob Phillips
03-27-2008, 10:12 AM
Looking at your code, it doesn't make sense to me.

You say you want to look down Column F starting at row 3, and check if it has a value, if so load H. But what are you trying to put in H, that formul you have is lookin g up columns C, E, F and I, and doing a calculation on all of it, that is just one result, not many.

khalid79m
03-27-2008, 10:30 AM
Dim LastRow As Long
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
Range("H3:H" & LastRow).FormulaR1C1 = _
"=SUMPRODUCT(--(Stage2!R3C3:R59999C3=RC3)*(Stage2!R3C5:R59999C5=RC5)*(Stage2!R3C6:R59999C6 =RC6)*(Stage2!R3C9:R59999C9<>""0""))"
Range("H3:H" & LastRow).Select
Run "PasteValues"


This is the code, i want to tweak. It currently copies the formula mentioned into cell h3 and all the way down to the lastrow in coloumn H. This bit is fine. All i want it to do is , once it has calculated H3 copy H3 and paste in H3 just the value, then move onto H4 , H5 until it hits the last row

khalid79m
03-27-2008, 11:13 AM
this does not work

Trevor
03-27-2008, 11:21 AM
Khaid79m, you lost me on your last post copy H3 past to H3
so if I read this right it sounds like H3 has a formula , but then you wont to caculate that formula in H3 and , remove the formula, from H3 and replace it with the answer for that fromula?

mdmackillop
03-27-2008, 01:07 PM
With Range("H3:H" & LastRow)
.Value = .Value
End With

khalid79m
03-27-2008, 02:21 PM
Dim LastRow As Long
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
Range("H3:H" & LastRow).FormulaR1C1 = _
"=SUMPRODUCT(--(Stage2!R3C3:R59999C3=RC3)*(Stage2!R3C5:R59999C5=RC5)*(Stage2!R3C6:R59999C6 =RC6)*(Stage2!R3C9:R59999C9<>""0""))"
Range("H3:H" & LastRow).Select
Run "PasteValues"

all i want to do is speed up this code as it takes 10 mins to run and i though if i pasted as values after each calculation it would be speed up but it doesnt can anyone help ?


Below is what I have been asking for , for anyone reading this thread!



With Range("H3:H" & LastRow)
.FormulaR1C1 = _
"=SUMPRODUCT(--(Stage2!R3C3:R59999C3=RC3)*(Stage2!R3C5:R59999C5=RC5)*(Stage2!R3C6:R59999C6 =RC6)*(Stage2!R3C9:R59999C9<>""0""))"

.Value = .Value

End With


anyone know how to speed up my code, ? i am using 30000 cells across 10 columns,