JazzSP8
Well-known Member
- Joined
- Sep 30, 2005
- Messages
- 1,227
- Office Version
- 2013
- 2010
- Platform
- Windows
Hey Guys
I'm trying to teach myself about working with Arrays after seeing them used more and more in code and them helping solve a problem I had the other week thanks to a member of this Forum.
I want to revisit some of my old code and change them to work with Arrays instead of accessing Cells in a Worksheet as I believe this speeds things up(?)
I've got this code below which is nearly working, but I need one of the dimensions of the Array not to be fixed, but I can't work out how to add in a row of data :/
I need the first dimenstion of FullCodes to be dynamic as I want to add to it as I go along, I thought ReDim would do it but I keep getting an "Array already dimensioned" error when I try to use it.
I've think I've confused myself with it all now so if anyone can help nudge me on what I need to do now I'd be most appreciative.
Thanks
(Also if anyone knows of a better way to do the "Count" then I'd appreciate that as well as I couldn't work out that either LOL)
I'm trying to teach myself about working with Arrays after seeing them used more and more in code and them helping solve a problem I had the other week thanks to a member of this Forum.
I want to revisit some of my old code and change them to work with Arrays instead of accessing Cells in a Worksheet as I believe this speeds things up(?)
I've got this code below which is nearly working, but I need one of the dimensions of the Array not to be fixed, but I can't work out how to add in a row of data :/
Code:
Sub Organise()
Application.ScreenUpdating = False
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Dim FullCodes(1, 2) As Variant
AllCodes = Range("A1:B" & LastRow)
For x = 2 To 25 'LastRow
Application.StatusBar = "Working on row " & x & " of " & LastRow
PurchaseID = AllCodes(x, 1)
CountID = Application.WorksheetFunction.CountIf(Range("A2:A" & LastRow), PurchaseID)
If CountID > 1 Then
PrimarySKU = AllCodes(x, 2)
For xx = x + 1 To x + (CountID - 1)
SecondarySKU = AllCodes(xx, 2)
If AllCodes(xx, 1) = PurchaseID Then
FullCodes(Prix, 0) = PrimarySKU
FullCodes(Prix, 1) = SecondarySKU
Prix = Prix + 1
'ReDim FullCodes(Prix, 2) As Variant
End If
Next xx
End If
Next x
Range("D2:E" & Secx).Value = FullCodes
Application.StatusBar = ""
Application.ScreenUpdating = True
MsgBox "Done"
End Sub
I need the first dimenstion of FullCodes to be dynamic as I want to add to it as I go along, I thought ReDim would do it but I keep getting an "Array already dimensioned" error when I try to use it.
I've think I've confused myself with it all now so if anyone can help nudge me on what I need to do now I'd be most appreciative.
Thanks
(Also if anyone knows of a better way to do the "Count" then I'd appreciate that as well as I couldn't work out that either LOL)