How to add a new row to an Array?

[JazzSP8]

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 :/

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)

 

[wideboydixon]

Firstly, you've explicitly set the size of the array when you declared it:

Dim FullCodes(2, 1) As Variant

Secondly, if you want to keep existing values then you need ReDim Preserve:

ReDim Preserve FullCodes(Prix, 2) As Variant

None of this matters, however, since you can't ReDim the first dimension. In your case, you'll need to create two arrays:

Dim FullCodesD As Variant
Dim FullCodesE As Variant
ReDim FullCodesD(1 To 1) As Variant
ReDim FullCodesE(1 To 1) As Variant

...

FullCodesD(Prix) = PrimarySKU
FullCodesE(Prix) = SecondarySKU

...

ReDim Preserve FullCodesD(1 To Prix) As Variant
ReDim Preserve FullCodesE(1 To Prix) As Variant

...

Range("D2:D" & Secx).Value = FullCodesD
Range("E2:E" & Secx).Value = FullCodesE

WBD

 

[JazzSP8]

AHA! - Right - Gotcha - Thanks for this!

But now I've another problem :-/

I changed the code to this;

Sub Organise()
Application.ScreenUpdating = False
LastRow = Cells(Rows.Count, "A").End(xlUp).Row

Dim FullCodesD As Variant
Dim FullCodesE As Variant
ReDim FullCodesD(1 To 1) As Variant
ReDim FullCodesE(1 To 1) As Variant

Prix = 1

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
                FullCodesD(Prix) = PrimarySKU
                FullCodesE(Prix) = SecondarySKU
                Prix = Prix + 1
                ReDim Preserve FullCodesD(1 To Prix) As Variant
                ReDim Preserve FullCodesE(1 To Prix) As Variant
            End If
        Next xx
    End If
Next x

Range("D2:D" & Prix).Value = FullCodesD
Range("E2:E" & Prix).Value = FullCodesE

Application.StatusBar = ""
Application.ScreenUpdating = True
MsgBox "Done"
End Sub

(The Secx variable in the section after the loop was a throw back from the old code I'm changing so I changed it to Prix, it was a last minute change I was trying before I posted for help - Sorry about that)

The problem is now that it just repeats the first two values of the array all the way down when it writes them out to the cells.

When I watch the FullCodesD and E in the Locals watch window it seems to be populating correctly so I don't know why it wouldn't write them back out properly when it was doing before?

 

[wideboydixon]

Maybe it's easier to use a Transpose and go back to your original idea:

Sub Organise()

Dim LastRow As Long
Dim Prix As Long
Dim AllCodes As Variant
Dim FullCodes() As Variant
Dim x As Long
Dim xx As Long
Dim PurchaseID As Variant
Dim PrimarySKU As Variant
Dim SecondarySKU As Variant
Dim CountID As Double

Application.ScreenUpdating = False

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Prix = 1
AllCodes = Range("A1:B" & LastRow)
ReDim FullCodes(1 To 2, 1 To 1) As Variant

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
                ReDim Preserve FullCodes(1 To 2, 1 To Prix) As Variant
                FullCodes(1, Prix) = PrimarySKU
                FullCodes(2, Prix) = SecondarySKU
                Prix = Prix + 1
            End If
        Next xx
    End If
    x = x + CountID - 1
Next x

Range("D2:E" & Prix).Value = Application.WorksheetFunction.Transpose(FullCodes)

Application.StatusBar = ""
Application.ScreenUpdating = True
MsgBox "Done"

End Sub

I added the missing Dim statements.

WBD

 

[JazzSP8]

Maybe it's easier to use a Transpose and go back to your original idea:

Yes, that has worked this time - Thanks for all your help, appreciated!

I added the missing Dim statements

Something else I need to get better at! :-/

Thanks again

 

[wideboydixon]

I always *force* myself to declare variables by using "Option Explicit"

WBD