Solved: Multiple small issues

[kroz]

Hey all,

I have a few issues that i can't figure out with my code. I have a long code that opens and closes some files from where it extracts info and in the end it stumbles.

I'm trying to transpose to VBA two rather simple formulas:
In cell f9 i have =A9 & "/" B9 & "/" C9
In cell G9 i have =SUMPRODUCT(--($A$9:$A9=$A9);--($B$9:$B9=$B9);--($C$9:$C9=$C9))<2
The second formula gave me an error so i have it with comments

Here's the part where the code doesn't work as it should.

[vba]
'adding extra formulas to the Final document

Nrow = Range("A1").End(xlDown).Row + 8
For Each oneCell In Range("f9", "f268").Cells
If Not (IsError(Cells(oneCell.Row, 4))) Then
oneCell.Value = Cells(oneCell.Row, 2).Value & "/" & Cells(oneCell.Row, 3).Value & "/" & Cells(oneCell.Row, 4).Value
'Cells(oneCell.Row, 7).Value = Application.WorksheetFunction.SumProduct(Range("A9", Range("A" & oneCell.Row)) = Range("A" & oneCell.Row), Range("b9", Range("b" & oneCell.Row)) = Range("b" & oneCell.Row), Range("c9", Range("c" & oneCell.Row)) = Range("c" & oneCell.Row))
Else
oneCell.Value = "Error in MID"
On Error Resume Next
End If
Next
[/vba]

running the code like this will take an unusual long period of time so i'm guessing that i did something wrong.

What i really need from it is this: instead of "f268" i want to use Cells(Nrow,6) but using that will only run the code for 6 lines.

[Bob Phillips]

[vba]
'adding extra formulas to the Final document

Nrow = Range("A1").End(xlDown).Row + 8
For Each oneCell In Range("f9", "f268").Cells
If Not (IsError(Cells(oneCell.Row, 4))) Then
oneCell.Value = Cells(oneCell.Row, 2).Value & "/" & Cells(oneCell.Row, 3).Value & "/" & Cells(oneCell.Row, 4).Value
sFormula = "SUMPRODUCT(--(A9:A" & oneCell.Row & "=A" & oneCell.Row & ")," & _
"--(B9:B" & oneCell.Row & "B" & oneCell.Row & ")," & _
"--(C9:C" & oneCell.Row & "C" & oneCell.Row & "))"
Cells(oneCell.Row, 7).Value = Application.Evaluate(sFormula)
Else
oneCell.Value = "Error in MID"
On Error Resume Next
End If
Next
[/vba]

[kroz]

The sumproduct now works, thank you (by the way, you missed =B and =C, i've added it to my formula).
Any idea on why a simple concatenate would add so much lag to my code? I have #n/a in my columns, perhaps that's the reason?

[kroz]

Any idea on why a simple concatenate would add so much lag to my code? I have #n/a in my columns, perhaps that's the reason?

I forgot to mention this. When i run the code with this
[vba]
For Each oneCell In Range("f9", Cells(Nrow, 6)).Cells
[/vba]
the execution will stop at row 9+5 (i only get 5 values).

Do I run after memory or what ?

[Bob Phillips]

Hard to help in any way as we have no context, it could be anything.

[kroz]

sigh, i found the problem.
Somehow my memory gets cluttered. The file on which i'm applying this formula is basically built from 3 files. I extract, modify and process info in those files with VBA and then i paste it to my final file. However, in this final file if i apply more formulas it will run very slow.
I tried copying the info into a new file (manually that is) and ran the VBA routine. It works like a charm. So i have a file problem.
Thanks for all the help xld