array values to worksheet

[mcranmoss]

I have checked that the array is populated but the following code is failing to tranfer the array values to column A of the 'Category' sheet.

Any advice welcome.

For Each x In tmpArray
 
 Cells(Sheets("Category").Range("A2").End(xlDown).Row, 1).Value = x
 
Next x

thanks
mark

 

[Jon von der Heyden]

If you want to unload the values of an array, one would typically do so in one hit.

For a 1D array:

Sheets("Category").Range("A2").Resize(Ubound(tmpArray)-Lbound(tmpArray)+1).Value = tmpArray

 

[njimack]

Try the below code instead:
Sheets("Category").Range("A2").End(xlDown).Offset(1,0).Value = x

 

[mcranmoss]

Perfect. Thanks chaps.

Mark

 

[mcranmoss]

Now that the array is being successfully dumped in the worksheet it has become apparent that my attempt to resize my 2D array to a 1D array hasn't worked.

The code that was supposed to achieve this is:

'Resize the 1D array
ReDim ProdCode(1 To UBound(tmpArray, 1))
 
 
...
 
 
For i = 1 To UBound(ProdCode, 1)
ProdCode(i) = tmpArray(i, 1)

Any thoughts?

Mark

PS. Could I perform a dedupe the values at the same time?

 

[Jon von der Heyden]

Do you mean you want to populate an additional array with only tmpArray's 1st dimension?

If so you can use the Index WorksheetFunction:

ProdCode = Application.Index(tmpArray,0,1)

No need to set the dimension bounds before hand either.

 

[mcranmoss]

Its being a while since I looked at this code and I'm confusing myself.

I am creating a multidimensional array from a 2d range from a worksheet called 'Matrix Codes'. I am then wanting to convert the 2D array to a 1D array (possibly deduping?) and then transferring the 1D array to a worksheet column in the 'Category' worksheet.

The code I have so far is:

Sub DedupeProducts()

Dim tmpArray As Variant
Dim ProdCode As Variant
'Dump the range into a 2D array
LastRow = Sheets("Matrix Codes").Range("A2").End(xlDown).Row
LastCol = Sheets("Matrix Codes").Range("A2").End(xlToRight).Column
catLastCell = Sheets("Category").Range("A2").End(xlDown).Row

With Sheets("Matrix Codes")
  tmpArray = .Range(.Cells(2, 1), .Cells(LastRow, LastCol))
End With

'Resize the 1D array
ReDim ProdCode(1 To UBound(tmpArray, 1))
'Convert 2D to 1D
For i = 1 To UBound(ProdCode, 1)
ProdCode(i) = tmpArray(i, 1)
Next

Sheets("Category").Range("A2").Resize(UBound(tmpArray) - LBound(tmpArray) + 1).Value = tmpArray
 

End Sub

 

[mcranmoss]

It looks like I'm not using the ProdCode array in a meaningful way...

 

[Jon von der Heyden]

Something like:

Sub DedupeProducts()

Dim tmpArray As Variant
Dim ProdCode As Variant
'Dump the range into a 2D array
LastRow = Sheets("Matrix Codes").Range("A2").End(xlDown).Row
LastCol = Sheets("Matrix Codes").Range("A2").End(xlToRight).Column
catLastCell = Sheets("Category").Range("A2").End(xlDown).Row

With Sheets("Matrix Codes")
  tmpArray = .Range(.Cells(2, 1), .Cells(LastRow, LastCol))[highlight].Value[/highlight]
End With

ProdCode = Application.Index(tmpArray,0,1)

Sheets("Category").Range("A2").Resize(LastRow-1).Value = ProdCode

End Sub

Now, about the dupes. How many rows are we looking at dealing with here?

 

[mcranmoss]

I amended the final line, but this appears to populate the Category column with many instances of the first value in the ProdCode.

Sheets("Category").Range("A2").Resize(UBound(ProdCode) - LBound(ProdCode) + 1).Value = ProdCode

A problem I now have is that when I examine the values in ProdCode much of the array appears blank. These missing values correspond to numerical values in the source range.

mark