kroz
10-27-2010, 01:08 AM
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.
'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
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.
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.
'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
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.