Hi,
I have the following code which I've adopted from a different thread to meet my needs:
I don't need to paste the whole array into a worksheet, only to match a line in the first column of this array and return the relevant line in column 2.
EG the array returns the below and I just need to match/lookup "ABCD" and have "1234" returned:
<tbody>
</tbody>
Thanks in advance!
I have the following code which I've adopted from a different thread to meet my needs:
Code:
Dim data As Variant
Dim i As Long
Dim countDict As Variant
Dim category As Variant
Dim value As Variant
Set countDict = CreateObject("Scripting.Dictionary")
data = Sheets("Active").Range("prod_name_count")
'Populate the dictionary: key = category / Item = count
For i = LBound(data, 1) To UBound(data, 1)
category = data(i, 1)
value = data(i, 2)
If countDict.exists(category) Then
countDict(category) = countDict(category) + value 'if we have already seen that category, add to the total
Else
countDict(category) = value 'first time we find that category, create it
End If
Next i
'Copy dictionary into an array
ReDim data(1 To countDict.Count, 1 To 2) As Variant
Dim d As Variant
i = 1
For Each d In countDict
data(i, 1) = d
data(i, 2) = countDict(d)
i = i + 1
Next d
'Puts the result back in the sheet in column D/E, including headers
With ActiveSheet
.Range("A1").Resize(UBound(data, 1), UBound(data, 2)) = data
End With
Dim test As String
Dim c As Integer
Dim line As Variant
test = Range("B2")
line = Split(test, "|")
For c = 0 To UBound(line)
Cells(1, c + 1).Offset(1, 2).value = line(c)
Next c
End Sub
I don't need to paste the whole array into a worksheet, only to match a line in the first column of this array and return the relevant line in column 2.
EG the array returns the below and I just need to match/lookup "ABCD" and have "1234" returned:
ABCD | 1234 |
DEFG | 5678 |
HIJK | 9123 |
<tbody>
</tbody>
Thanks in advance!