VBA find method and named range not working

[frankienap]

So I am trying to keep track of my budget using excel. Each month I paste various information, including expense descriptions, into a worksheet and assign the value in the descriptions category to an expense category and subcategory.

I am trying to find the matching description in a named range on Sheet8("Match") and then return the offset (1 column and 2 columns to the right of the match) to automatically populate the category and subcategory information.

I've put together the below code from some bits and pieces I've found online. However, the code only works ONCE. It returns the matching expense category and subcategory for the FIRST matching description in Range G13:G15, i.e., subsequent descriptions each have the same expense category/sub-category as the first item.

Any ideas on what I'm doing wrong?

Please Login or Register  to view this content.

[6StringJazzer]

I'm not sure I understand what you're trying to do, especially without seeing your file. It would help to see how your data is laid out.

First, Find is only going to find the value in G13. It isn't going to look for matches across multiple cells that match the entire range G13:G18. I can't tell what your intention is there, but you may be misunderstanding how Find works.

The coding for Find and FindNext seem to be fine, but I notice that you are putting the results into the same two cells each time. sFind never changes, and so when you assign a value to sFind.Offset(0, 1).Value, it is always the same cell (BTW it will always be H13). I don't think that explains the problem you are describing but it might not be what you intend to do.

Oh, and we can't see how Match is defined so we can't confirm that the named range is what you want it to be. That could certainly be a problem, too.

[frankienap]

6StringJazzer,

You have partially clarified my problem.
Range G13:G15 currently looks like this:

T-Mobile
CVS
Home Depot

So after I run the code I get:

T-Mobile Bills/Utilities Mobile Phone
CVS Bills/Utilities Mobile Phone
Home Depot Bills/Utilities Mobile Phone

So I think the problem I have is that find works on G13 and doesn't do a new find for G14 and G15. It it possible to Loop through all cells in a range and perform a find on each?

[6StringJazzer]

It's hard for me to understand the code you posted without seeing the context of how it's called. You can certainly loop through cells in a range but I don't know if the loop should be in FindCategory or the code that calls it. Can you post your whole file?

Here is how you loop through all cells in a range:

Please Login or Register  to view this content.

[frankienap]

Whew, I just got it working. Thanks for all the help.

I've changed things slightly since the first iteration. The idea now is to select the expense description and then the code will
1) checking for each cell within the selection
2) find a match (if one exists) in the named range "Match"
3) and return the offsets from match to the cell's right (returning both the expense category and subcategory)

I've posted the code below for anyone else who might be interested. 6String, please let me know if you see any issues/areas for improvement. And thanks again.

Please Login or Register  to view this content.