j.smith1981
02-09-2011, 03:20 AM
I keep getting the above error when running this code, can someone help please?
I will explain what it is I am trying to do, just going over old theory as it where.
I am wanting to say set a specific set of data, which going by the below code is going to be a multi dimension array, hence the col 7 of productsArray, which is where the categories is, ok?
We have say:
category1
category1
category2
category2
If it finds a value in the first row, of column 7 it checks to see if a value exists already in categoriesUnique if it doesnt add to categoriesUnique as a new row, if it does, exit the loop and go down to the next row of productsArray and keep going.
But it keeps coming up with:
Run-time error '13':
Type mismatch
This occurs when it checks the value of productsArray on row 3, as the categoriesCount value reaches 3, it sparks up with that error.
Any helps appreciated,
Jeremy.
Dim categoriesCount As Long
Dim productsArray As Variant
Dim categoriesUniqueCount As Long
ReDim categoriesUnique(1 To 1)
'productsArray
productsArray = ActiveSheet.UsedRange.Value
For categoriesCount = LBound(productsArray) To UBound(productsArray)
If Len(productsArray(categoriesCount, 7)) > 0 Then
'do whatever here
For categoriesUniqueCount = LBound(categoriesUnique) To UBound(categoriesUnique)
If categoriesUnique(categoriesUniqueCount) = productsArray(categoriesCount, 7) Then
Exit For
Else
'add to the array?
categoriesUnique(UBound(categoriesUnique)) = Array(productsArray(categoriesCount, 7))
'need to add 1 to the redmimed array:
ReDim Preserve categoriesUnique(1 To UBound(categoriesUnique) + 1)
End If
Next categoriesUniqueCount
End If
Next categoriesCount
I will explain what it is I am trying to do, just going over old theory as it where.
I am wanting to say set a specific set of data, which going by the below code is going to be a multi dimension array, hence the col 7 of productsArray, which is where the categories is, ok?
We have say:
category1
category1
category2
category2
If it finds a value in the first row, of column 7 it checks to see if a value exists already in categoriesUnique if it doesnt add to categoriesUnique as a new row, if it does, exit the loop and go down to the next row of productsArray and keep going.
But it keeps coming up with:
Run-time error '13':
Type mismatch
This occurs when it checks the value of productsArray on row 3, as the categoriesCount value reaches 3, it sparks up with that error.
Any helps appreciated,
Jeremy.
Dim categoriesCount As Long
Dim productsArray As Variant
Dim categoriesUniqueCount As Long
ReDim categoriesUnique(1 To 1)
'productsArray
productsArray = ActiveSheet.UsedRange.Value
For categoriesCount = LBound(productsArray) To UBound(productsArray)
If Len(productsArray(categoriesCount, 7)) > 0 Then
'do whatever here
For categoriesUniqueCount = LBound(categoriesUnique) To UBound(categoriesUnique)
If categoriesUnique(categoriesUniqueCount) = productsArray(categoriesCount, 7) Then
Exit For
Else
'add to the array?
categoriesUnique(UBound(categoriesUnique)) = Array(productsArray(categoriesCount, 7))
'need to add 1 to the redmimed array:
ReDim Preserve categoriesUnique(1 To UBound(categoriesUnique) + 1)
End If
Next categoriesUniqueCount
End If
Next categoriesCount