VBA Code to return matched line from array

maystery

New Member
Joined
Jun 3, 2013
Messages
8
Hi,

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:

ABCD1234
DEFG5678
HIJK9123

<tbody>
</tbody>






Thanks in advance!
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
If I read this right, you're collecting your data in a dictionary, then passing it to an array which you want to search for a specific term. But that term is available in the dictionary, so you should be able to refer to it directly from there, without using the array at all

I'm a little rusty with dictionaries as I don't use them often, but what does this do, added before you even copy the dictionary to the array?
Code:
msgbox countdict("ABCD")
 
Upvote 0
Hi,

Taking this one step further, is it possible to have the value I want returned as a 'dynamic' field?
In other words, I don't always want to only return "ABCD", this could change depending on the search.
Could I possibly used a named range here as I have multiple cells that form ABCD from within the workbook.


Many thanks!
 
Upvote 0
Of course, almost anything is possible, so long as you can theorise it

In VBA whenever you see a text string being used - in this case "ABCD" - then you have the option to create that text string in any way you want. So Range("my named range ABCD").value is just as valid, as is "AB" & "CD" or range("my named range AB") & "CD"

To keep things simple, it's often useful to create your text string separately and then use it when you need to. It allows you to then start looping through different text values easily

So you could use
Code:
dim strMyText as string: strMyText = range("my range").value
msgbox countdict(strMyText)
 
Upvote 0
Hi,

Hope you had a happy holiday season.

Picking this one up again, I've tried using the named range as suggested above and receive a "Method 'Range' of object '_Global' failed" error.

Any idea why this might be the case?


TIA
 
Upvote 0
Looks like you've not set the named range up correctly, or are not referencing it correctly

You can set up a named range by selecting the cell or cells you want to name, and typing the name into the name box directly above cell A1 / to the left of the formula bar. You can also create / amend named ranges using the name manager found on the Formulas tab of the ribbon. A range name must be a single string of text, and can contain letters, numbers, underscore, but can't start with a number

Say you name your cell or cells "rangeName1", then you can refer to it in VBA as range("rangeName1")

Warning: named ranges by default are applicable across the whole workbook and can be referred to from anywhere. However, if you duplicate a worksheet using move/copy, then named ranges can also be duplicated, and the duplicated name is applicable only within that worksheet. This can affect which value is being referred to from other worksheets and within VBA code, where you would also need to make reference to the worksheet name. Don't get hung up on this but be aware of it
 
Upvote 0
Thanks for all your help so far!

Would a space in the string invalidate the "single string of text"?
The named range references non-adjacent cells and will look something like "C-B16-017-VExample HereWORD", which works when I say =Named_Range in any excel cell but not when I use it in the vba.
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,109
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top