Dynamically find a range within a sheet...help with VBA please!

[lmoseley7]

I have a macro that opens another worksheet and pulls information via VLOOKUP formulas from a range that I name within that sheet. Recently, for some reason the information has been showing up in different columns on the report and since I can't control how that report is produced, I need to be able to find a certain text string that will indicate the top left cell of my range. Then I can search for another text string on that same row to get the width of my range. Previously when the reports were consistently being produced I had the macro count the number of rows with text in the starting column of the range to determine the height of my range. Since the starting column is moving around, it sometimes resides in the same column as the header information, so I can't predict how many rows of text to exclude from my COUNTA calculation.

What I need is a macro that will create a named range, the size of which we will determine. First it will search for the phrase "SS Acct #"; this will be the top left cell of the range. I believe if we limit the possible range of finding that phrase to cells B10:E20 that will provide more than enough room to handle the inconsistency of this report. From that cell we need to count how many rows below it have text in them. That number will be the number of rows in our range. We will also search for "Amount" in the same row as we found SS Acct #. That will represent the last column in our range and will be the column we use in our VLOOKUP formula as the amount to return.

I've been able to find the top left cell. I'm not sure how to count the rows beneath that cell that have been used. The amount column has consistently been two columns over from the 1st column, but I don't want to assume that will remain. I'm having the most trouble converting row numbers into ranges and now I think I'm so lost in the weeds there must be a easier solution, that hopefully one of you will share.

An example of the data would be as follows:
C D E
12: SS Acct # Amount
13: ABF2 93,402.97
14: ABF5 5,643.07

The next time this report comes C12 might be D12 and E12 would now be F12.

Thanks in advance for any help.

 

[lmoseley7]

Update

I added the second macro code to the first just to see if I could make it work. The first thing I noticed was that to display the range in the message box I had to change my code to the following:

MsgBox "" & myRange.Address

That was nice to figure out, but not necessary for the code.
I would like to be able to reference this code by calling another macro, and at this point I don't care if it is in the same module or not, although it would be nice to know how to do it that way if possible.

I'm close, but I need help getting over the hump. How do I get this to work when myRange and the code that creates it is in another macro?

 

[MARK858]

What exactly was wrong with

Range(LURAnchor, Cells((LastRow + AnchorRow - 1), LastCol)).Name = "Pull_Data"

which you commented out in your last code?

 

[lmoseley7]

There was nothing wrong with that code, the only problem was that the name would change depending on the report. We have two reports that are very similar in structure, one is for money going out (push) and one is for money coming in (pull). I wanted to use this same code for both so I wanted to move the naming of the range back to the original macro, which is why I was trying to pass the range variable from another macro. Thanks for looking!

What exactly was wrong with

Range(LURAnchor, Cells((LastRow + AnchorRow - 1), LastCol)).Name = "Pull_Data"

which you commented out in your last code?

 

[lmoseley7]

Through further research, it looks like passing a range variable the way I was trying to do it is at least difficult. I was able to accomplish it by using a function as described in the following link, also from this forum http://www.mrexcel.com/forum/excel-...l-basic-applications-define-named-ranges.html. Thanks to mikerickson from way back in 2009 for that info. Thanks to everyone for their help.

 

[MARK858]

So basically the same as the line you commented out.
Happy you found a solution you can work with.