0

For work, I'm working with arrays in which I store prices of parts. The idea is that I have an array with the different part numbers coded on several digits, I go in the excel workbook where all the parts and their prices are stored and search for these codes one by one. Once a code found, I just do an "offset" to get the price in Euro and in Dollards, and fill another array with these prices.

My problem is that when the array with the parts numbers got only one part number, I don't achieve to "ReDim" the array to be a 1x2 array...

See the comments in the following code (Yes, I chose to start the arrays at 1 and not 0, it's way more convenient in this case)

Dim ToolPrices() As String
ReDim ToolPrices(1 To UBound(ToolTab()), 1 To UBound(ToolTab()))     'Normally, if "ToolTab()" is a 1x1 array, TooLPrices should be a 1x2 array... But no, it's a 1x1

[Later on in the code]

Tempo = ActiveCell.Offset(0, 2)                           'gets the euro price

' the variable PositionPoint is the pointer for the "for" loop passing through all the arrays
If PositionPoint > UBound(ToolPrices(), 1) Then           'for avoiding to reduce the size of the Array and loosing data
   ReDim Preserve ToolPrices(1 To 2, 1 To PositionPoint)
End If

ToolPrices(1, PositionPoint) = Tempo
Tempo = ActiveCell.Offset(0, 5)                           'gets the dollard price

If PositionPoint > UBound(ToolPrices(), 2) Then           'for avoiding to reduce the size of the Array and loosing data
   ReDim Preserve ToolPrices(1 To 2, 1 To PositionPoint)
End If

ToolPrices(2, PositionPoint) = Tempo       'In case of a 1x1 array with the parts numbers, this bugs as it is a 1x1 array in which I try to put something in the second line

I've been searching for a while without success...

I guess either the ReDim ToolPrices(1 To UBound(ToolTab()), 1 To UBound(ToolTab())) got a problem... OR the If PositionPoint > UBound(ToolPrices(), 2) Then ReDim Preserve ToolPrices(1 To 2, 1 To PositionPoint) End If which isn't accessed as the UBound(ToolPrices(), 2) = 1 when I would expect a 0 or "Empty" or "" or error or anything except a 1 as the second dimension don't exist...

Thank's by advance for your help !

zepeu
  • 81
  • 2
  • 16
  • If Tooltab is 1x1 its ubound() value is 1. So you ask to create an array (1 to 1). Then it is still 1x1 length – EddiGordo Oct 01 '19 at 13:31
  • @EddiGordo well, in this line : "ToolPrices(1 To UBound(ToolTab()), 1 To UBound(ToolTab()))" I ask to create an array of 2 dimensions with 1 value in each when ToolTab = 1, no? Or I missunderstood something on the arrays creation? – zepeu Oct 01 '19 at 13:35
  • I mean, if I create an array like that : "RandomArray(1, 1)" It will be a 1x2 array, right? (I understood that this way, at least...) – zepeu Oct 01 '19 at 13:47
  • 1
    And don't forget that `Redim Prserve` can only affect the last dimension of a multi-dimension array. – Ron Rosenfeld Oct 01 '19 at 18:43
  • https://stackoverflow.com/questions/55268349/vba-error-with-redim-preserve-and-application-transpose-changing-array-base/55272851#55272851 – Guest Oct 01 '19 at 19:14

1 Answers1

0

Ok, I think I got it, thank's to this :

https://www.excel-easy.com/vba/array.html

So, first you define the number of lines, then the number of columns up to 60 columns

I have to redo everything :'(

zepeu
  • 81
  • 2
  • 16
  • 1
    You may want to look at the function at this link: https://stackoverflow.com/questions/55268349/vba-error-with-redim-preserve-and-application-transpose-changing-array-base/55272851#55272851 – Guest Oct 01 '19 at 14:02