ReDim Preserve Array Error, Dynamic Array

[mkvarious]

hello,

I have a column with data that I work with (let's call it A).
Based on A I get data from AS400 PCCOM IBM emulator (let's call it B).
What I need to check on is whether B data is unique or not.
If B is a duplicate, then I need to check whether C column (value known from the beginning) ins'nt unique.
If C is unique, then I musn't do anything with it, as I can only do a change for B when B is unique or when B isn't unique but then C must be a duplicate, only.
When B isn't unique and when C is unique is a scenario which I cannot follow up.

But not too complicate things, let's just focus on two-dimension array, so pls disregard I have mentioned C, for now.
So my macro goes though A items and is getting B data each time ralated A item is being worked with, so I cannot have a static array with B items as they are not know from the beginning.
So I am trying to build a dynamic array with B items.

I cannot figure it out why ReDim Preserve does't work for me.
Can someone please take a peek into the code below and advise???
thanks

Sub testowe()
Dim cell 
Dim aTabl()
Dim rSOjeden, rSHIPTOjeden, rSOkolejny, rSHIPTOkolejny
Dim lKtóry As Long
For Each cell In Selection
lKtóry = lKtóry + 1
Select Case lKtóry
    Case Is = 1
        rSOjeden = cell.Offset(0, 6).Value
        rSHIPTOjeden = cell.Offset(0, 4).Value
        aTabl = Array(rSOjeden, rSHIPTOjeden)
    Case Else
        rSOkolejny = cell.Offset(0, 6)
        rSHIPTOkolejny = cell.Offset(0, 4)
        ReDim Preserve aTabl(rSOjeden to rSOkolejny, rSHIPTOjeden to rSHIPTOkolejny)
End Select
Next cell
End Sub

 

[p45cal]

You can only change the last (rightmost) member of the array with redim Preserve.
From Help:

If you use the Preserve keyword, you can resize only the last array dimension and you can't change the number of dimensions at all. For example, if your array has only one dimension, you can resize that dimension because it is the last and only dimension. However, if your array has two or more dimensions, you can change the size of only the last dimension and still preserve the contents of the array. The following example shows how you can increase the size of the last dimension of a dynamic array without erasing any existing data contained in the array.

<code>ReDim X(10, 10, 10) . . . ReDim Preserve X(10, 10, 15) </code>

<tbody>
</tbody>

Similarly, when you use Preserve, you can change the size of the array only by changing the upper bound; changing the lower bound causes an error.

 

[mkvarious]

hello p45cal,

the thing is that with Case 1 I only declare LBound:

aTabl = Array(rSOjeden, rSHIPTOjeden)

and then with Case Else I then declare UBounds, for both arrays at the same time:

ReDim Preserve aTabl(rSOjeden to rSOkolejny, rSHIPTOjeden to rSHIPTOkolejny)

.
So why it does not work?

 

[p45cal]

With
aTabl = Array(rSOjeden, rSHIPTOjeden)
you are putting the values of rSOjeden and rSHIPTOjeden into aTabl and it has the dimensions atabl(0 to 1).

With
ReDim Preserve aTabl(rSOjeden To rSOkolejny, rSHIPTOjeden To rSHIPTOkolejny)
you using the values of rSOjeden and rSHIPTOjeden to set the dimensions of the array atabl.

What are you trying to do (in words)?

 

[mkvarious]

Each time when I find SO number I need to add it to 1st array (SOarray that I try to declare with rSOjeden To rSOkolejny) and then I need to add SHIP-TO number to the 2nd array (SHIP-TO array that I try to declare with rSHIPTOjeden To rSHIPTOkolejny).

Each time my code loops through selected items it then gets SO number from AS400 that I need to add to table array.

thanks

 

[p45cal]

Try either:

Sub testowe()
Dim cell
Dim aTabl()
ReDim aTabl(1 To 2, 1 To 1)
Dim lKtóry As Long
For Each cell In Selection
    lKtóry = lKtóry + 1
    If lKtóry > 1 Then ReDim Preserve aTabl(1 To 2, 1 To lKtóry)
    aTabl(1, lKtóry) = cell.Offset(0, 6).Value
    aTabl(2, lKtóry) = cell.Offset(0, 4).Value
Next cell
End Sub

where you would refer to the values in the array by the likes of:
atabl(1,9) for SO
atabl(2,9) for SHIPTO

or:

Sub testowe2()
Dim cell
Dim lKtóry As Long
Dim aTabl()
ReDim aTabl(1 To 1)
For Each cell In Selection
    lKtóry = lKtóry + 1
    If lKtóry > 1 Then ReDim Preserve aTabl(1 To lKtóry)
    aTabl(lKtóry) = Array(cell.Offset(0, 6).Value, cell.Offset(0, 4).Value)
Next cell
End Sub

where you would refer to the values in the array by the likes of:
atabl(9)(0) for SO
atabl(9)(1) for SHIPTO

 

[mkvarious]

where you would refer to the values in the array by the likes of:
atabl(1,9) for SO
atabl(2,9) for SHIPTO

what does '9' stand for? it displays nothing and only if I decrease the '9' number to lower withing lKtóry range then it shows some values.
Basically I think I know better understand it - I somehow wanted to always increment both arrays whereas it seems I can from the beginning state it is two fields (SO and SHIPTO) and only increment the second value which is a current SO and SHIPTO that is being added to the arrays, correct?

I think the first code seems more natural to me in the situation I try to work out and it is hard for me to understand the second code. It seems as if it only had one array??? If one just have a '1' (one), then how come we can write to data SO and SHIPTO to just one array?

totally confused with the way the second code is supposed to work.

thank you, though. I can now see a light in the tunnel

mkvarious

 

[p45cal]

The 9 would be the 9th SO number found, if there was one. I chose 9 because it was sufficiently far away from 0,1 and 2 and so avoid confusing examples like
atabl(1)(1)
atabl(2)(1)
atabl(1,2)
atabl(2,1)
It just makes it easier to read the examples.
The second uses a one dimension array, but each member of that array is an array of 2.

The second method makes it easier to sort/delete related values, referring to a pair with the likes of atabl(9)

 

[mkvarious]

thanks,

the first one below does not work, though.
the second only displays value of 1 from the array.

    MsgBox aTabl(lKtóry)
    MsgBox aTabl(lKtóry)(0 & 1)

only these has worked for me but maybe I have mistyped anything?

    MsgBox aTabl(lKtóry)(0) & ", " & aTabl(lKtóry)(1)
    MsgBox aTabl(lKtóry)(0)
    MsgBox aTabl(lKtóry)(1)

this is not a problem in fact as I can use the the one below to generate a concatenated string of SO&SHIP-TO but simply am trying to understand why your suggestion aTabl(9) is not working...

MsgBox aTabl(lKtóry)(0) & aTabl(lKtóry)(1)

Can you recommend now how should I be checking whether SO&SHIP-TO is duplicate or not? Any easy way? I think I could use conditional formatting rules to check that but I would like to learn how to take advantage of the Array, you have helped out me with.

hope am not being a nuisance...

mkvarious

 

[p45cal]

See comments in code below:

Sub testowe2()
Dim cell
Dim lKtóry As Long
Dim aTabl()
ReDim aTabl(1 To 1)
For Each cell In Selection
  If lKtóry > 0 Then
    DuplicateFound = False
    For i = LBound(aTabl) To UBound(aTabl) 'the i loop checks for duplicates and if found does not add it to the array.
      If cell.Offset(0, 6).Value = aTabl(i)(0) And cell.Offset(0, 4).Value = aTabl(i)(1) Then
        DuplicateFound = True
        Exit For
      End If
    Next i
  End If
  If Not DuplicateFound Then
    lKtóry = lKtóry + 1
    If lKtóry > 1 Then ReDim Preserve aTabl(1 To lKtóry)
    aTabl(lKtóry) = Array(cell.Offset(0, 6).Value, cell.Offset(0, 4).Value)
  End If
Next cell

'some experimentation:
lKtóry = Application.RoundUp(UBound(aTabl) / 2, 0)  'take a value in the middle somewhere
MsgBox aTabl(lKtóry) 'this will error, you're trying to display both values of an array in a message box, however:
Range("A1:B1") = aTabl(lKtóry) 'where you write the 2-member array to a couple of cells on a sheet will show both values.
MsgBox aTabl(lKtóry)(0 & 1) ' here the 0 & 1 become "01" which is coerced into a number 1, so it should be the same as MsgBox aTabl(lKtóry)(1).

MsgBox aTabl(lKtóry)(0) & ", " & aTabl(lKtóry)(1) ' this is fine
MsgBox aTabl(lKtóry)(0) 'so is this
MsgBox aTabl(lKtóry)(1) ' as is this
MsgBox aTabl(lKtóry)(0) & aTabl(lKtóry)(1) 'and this too.
End Sub