RockandGrohl
Well-known Member
- Joined
- Aug 1, 2018
- Messages
- 788
- Office Version
- 2010
- Platform
- Windows
Hi guys, this is the old code, don't spend too long looking at it I'll summarise:
We have the Advert Data file which groups each ctno (tour ref) together and they're ordered from top to bottom, so every "ABC123" is together and they're ordered oldest to newest.
The previous data guy had a fetish for loops in loops in loops but there's a more elegant formulaic solution which is basically:
MAX | IF | (Tour Ref Range = Tour Ref)*(Advert Name Range = Advert Name), Advertisement Date
This must be an array formula, and it can be applied across a column from 2 to like, 250 cells. Each line in the column has a different tour ref to test against.
So I'm thinking I can apply it to the top row in Formula format, and then use the Fill Down function to apply down the range. But believe it or not, this is slower than that nested Do Until loop mess.
Is there a faster way of doing what I want, without array formulas? Thanks!
VBA Code:
ahtemp.Activate
Range("A1").Value = "Tourno"
Range("B1").Value = "Last Used"
Range("A2").Activate
a.Activate '(This is advert data)
Range("A2").Activate
Do Until Cells(ActiveCell.Row, "A").Value = ""
If Cells(ActiveCell.Row, "Y").Value > 0 Then
ctno = Cells(ActiveCell.Row, "A").Value
luse = 0
Do Until Cells(ActiveCell.Row, "A").Value <> ctno
If Cells(ActiveCell.Row, "N").Value = PapNam And Cells(ActiveCell.Row, "L").Value > luse Then
luse = Cells(ActiveCell.Row, "L").Value
End If
ActiveCell.Offset(1, 0).Activate
Loop
ahtemp.Activate
Cells(ActiveCell.Row, "A").Value = ctno
If luse = 0 Then
Cells(ActiveCell.Row, "B").Value = "N/A"
Else
Cells(ActiveCell.Row, "B").Value = luse
End If
ActiveCell.Offset(1, 0).Activate
a.Activate
End If
ActiveCell.Offset(1, 0).Activate
Loop
We have the Advert Data file which groups each ctno (tour ref) together and they're ordered from top to bottom, so every "ABC123" is together and they're ordered oldest to newest.
The previous data guy had a fetish for loops in loops in loops but there's a more elegant formulaic solution which is basically:
MAX | IF | (Tour Ref Range = Tour Ref)*(Advert Name Range = Advert Name), Advertisement Date
This must be an array formula, and it can be applied across a column from 2 to like, 250 cells. Each line in the column has a different tour ref to test against.
So I'm thinking I can apply it to the top row in Formula format, and then use the Fill Down function to apply down the range. But believe it or not, this is slower than that nested Do Until loop mess.
Is there a faster way of doing what I want, without array formulas? Thanks!