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.

 

[elmer007]

I'm not the best VBA guru, so others feel free to offer better solutions, but here's my take on it... This finds a cell with "SS Acct #" in the first 5 columns up to the 21st row, and it tells how many congruent cells below it have values. I think you could use these (the location and the size of the range) in your existing code.

Dim ssAcctRange As Range
Dim myString As String
Dim xInt As Integer
Dim yInt As Integer
Set myRange = Range("A1")
myString = "SS Acct #"
xInt = 0
yInt = 0

Do Until (xInt > 4)
    xInt = xInt + 1
    Do Until (yInt > 20)
        yInt = yInt + 1
        If Cells(yInt, xInt).Value = myString Then
            Set myRange = Range(Cells(yInt, xInt).Address)
        End If
    Loop
    yInt = 0
Loop

Set ssAcctRange = myRange

yInt = 0
Do Until IsEmpty(myRange) And IsEmpty(myRange.Offset(1, 0))
    yInt = yInt + 1
    Set myRange = myRange.Offset(1, 0)
Loop
yInt = yInt - 1

MsgBox (yInt)
MsgBox (ssAcctRange.Address)

End Sub

With a workbook set up like the following, it returns yInt=5 (the number of "data" cells) and ssAcctRange=$E$12 (I can't get the table to post with normal formatting, but the cell addresses are typed into their corresponding cells):

a1 b1 c1 d1 e1
a2 b2 c2 d2 e2
a3 b3 c3 d3 e3
a4 b4 c4 d4 e4
a5 b5 c5 d5 e5
a6 b6 c6 d6 e6
a7 b7 c7 d7 e7
a8 b8 c8 d8 e8
a9 b9 c9 d9 e9
a10 b10 c10 d10 e10
a11 b11 c11 d11 e11
a12 b12 c12 d12 SS Acct #
a13 b13 c13 d13 data
a14 b14 c14 d14 data
a15 b15 c15 d15 data
a16 b16 c16 d16 data
a17 b17 c17 d17 data

 

[elmer007]

Actually, I think the loop at the bottom of my code in the previous post has an unnecessary "And" . I don't think you need to check for two empty cells- just the first empty cell should be fine, especially since I don't know what the format/layout of your spreadsheet is.

 

[lmoseley7]

The format of the report is almost like a letter with header information (Run by:, Run on:, Title, etc.) and then in the middle of the page is a "table of data" that shows account numbers and amounts. There should be no blank rows, but it looks like you were testing for one blank row with your And statement at the end. I may be wrong about whether or not that would be what that code does, but that's what it looked like to me.

Not picking your code apart, just trying to understand it, where you have "Set ssAcctRange = MyRange", what is the purpose of that? I don't see where you used ssAcctRange again.

Near the end when you are calculating "yInt", are you basically counting the loops until you find a blank cell and then subtracting 1 from that number to get the last non-blank cell? Pretty clever.

I'm working on a different method that I may be near completing. The Cells property of the Range is what saved me since I don't have to convert column numbers to letters and so forth when using Cells. I will post the resulting code once I get it finalized. Thanks for the response and I'm curious if others have input as well.

 

[MARK858]

can't get the table to post with normal formatting

Might be handy having s look at the links in my signature block for some ways to post a usable screenshot

 

[elmer007]

The format of the report is almost like a letter with header information (Run by:, Run on:, Title, etc.) and then in the middle of the page is a "table of data" that shows account numbers and amounts. There should be no blank rows, but it looks like you were testing for one blank row with your And statement at the end. I may be wrong about whether or not that would be what that code does, but that's what it looked like to me.

Not picking your code apart, just trying to understand it, where you have "Set ssAcctRange = MyRange", what is the purpose of that? I don't see where you used ssAcctRange again.

Near the end when you are calculating "yInt", are you basically counting the loops until you find a blank cell and then subtracting 1 from that number to get the last non-blank cell? Pretty clever.

I'm working on a different method that I may be near completing. The Cells property of the Range is what saved me since I don't have to convert column numbers to letters and so forth when using Cells. I will post the resulting code once I get it finalized. Thanks for the response and I'm curious if others have input as well.

Setting ssAcctRange equal to MyRange is storing where the data table begins (at the point when the "SS Acct #" cell is found), and then finding the last non-blank cell in the column (beginning at the header cell) tells you how far down the data goes. From this you should be able to extrapolate the location and size of your range (in my example, E12:E17 including the header cell), and both variables are known/displayed by the msgbox lines- although you'd probably rather use these variables instead of show them in a message box... just a shot in the dark haha

This may not be a good solution for your situation, but FWIW there it is. It could probably be tweaked for efficiency/consistency, but I'll leave that to the optimizing folks

Might be handy having s look at the links in my signature block for some ways to post a usable screenshot

Thanks! I'll take a look and hopefully have a more readable table in my next post

 

[lmoseley7]

I could only get the Borders-Copy-Paste page to pull up here at work of the first and last options. The two in the middle appear to require downloading something which is frowned upon here at work. I will try to figure this out though, so thanks for the suggestion.

can't get the table to post with normal formatting

Might be handy having s look at the links in my signature block for some ways to post a usable screenshot

 

[MARK858]

Other option is to click "Go Advanced" and there is a tables option but it is all manual entry

 

[MARK858]

Other option is to click "Go Advanced" and there is a tables option but it is all manual entry

Or if you are allowed to use macro's I can post when I get in the code Marcelo created which is what part of Rory's addin is based upon (but it will be a couple of hours).

 

[lmoseley7]

Ok, I was able to successfully find and name a range. Now I would like to reference that range variable from another macro that is in another module. I started by declaring all the variables as Public, but that didn't work. Then I moved the macro into the same module, renamed the original macro to prevent confusion and declared all the variables as public in the first module. Now I get a "Method 'Range' of object'_Gobal' failed" error on the following line:

Range(myRange).Name = "Pull_Data"

Here, myRange is the range that I set up in the called macro "Find_Range_Boundaries". Just before the errored code line, I have a message box that is supposed to display the range but it is blank. This seems to be indicating that the range variable did not pass into this macro.

Most examples I've seen of passing variables have been like the following: Run macro 1, Macro 1 sets a variable with a value, call macro 2 and use that value. This seems easier because you are passing the parameters when you call the macro; what I'm trying to do is retrieve information from the called macro, not pass information into it.

I should be able to make this work if I just put the code in the calling macro, but since I'm having to perform this process on a few reports, I wanted to try to use this code as a generic code that I could call multiple times without having to repeat the code in the main macro.

The code that creates the range is:

Sub Find_Range_Boundaries()

'MsgBox statements will be commented out as they were only used for construction of the macro.
'First we declare the variables we will use to determine the size of the range.
Dim LURAnchor As Range 'Look Up Range Anchor cell
Dim AnchorRow As Long 'First row of range
Dim Anchorcol As Long 'First col of range
Dim LastRow As Long 'Number of rows in the range, not the row number of the last row
Dim LURLCH As Range 'Look Up Range Last Column Header
Dim LastCol As Long 'Number of the last column in the range
Dim myRange As Range 'This is the range that we will return to the calling macro.

Set LURAnchor = Worksheets("Sheet").Range("B10:E20").Find("SS Acct #", lookat:=xlPart)
    'If Not LURAnchor Is Nothing Then
    '    MsgBox "Lookup range anchor cell is " & LURAnchor.Address
    'End If
'After determining the top-leftmost cell in the range, we now now the row that we will use _
to search for the last column in the range (headed by "Amount") and we also know the column _
in which we need to count the number of cells with data to determine the number of rows in _
our range.  
AnchorRow = LURAnchor.Row
    'MsgBox "Anchor row is #" & AnchorRow
Anchorcol = LURAnchor.Column
    'MsgBox "Anchor column is #" & AnchorCol
'Using the row from the LURAnchor (AnchorRow) we set up a range to search from columns C to M
Set LURLCH = Worksheets("Sheet").Range("C" & AnchorRow, "M" & AnchorRow).Find("Amount", lookat:=xlPart)
    'If Not LURLCH Is Nothing Then
    '    MsgBox "Lookup range last column header is " & LURLCH.Address
    'End If
LastCol = LURLCH.Column
    'MsgBox "Last column is #" & LastCol
LastRow = WorksheetFunction.CountA(Range(Cells(AnchorRow, Anchorcol), Cells(50, Anchorcol)))
    'MsgBox "Last row is " & LastRow - 1 & " rows down from anchor row."
'The following code creates a range using the variables created previously.  Since I will _
hopefully be using this code for multiple workbooks, I don't want the name to be static so _
I will include that line of code in the calling macro.
'    Range(LURAnchor, Cells((LastRow + AnchorRow - 1), LastCol)).Name = "Pull_Data"
    Set myRange = Range(LURAnchor, Cells((LastRow + AnchorRow - 1), LastCol))
End Sub

The related code from the calling macro is:

    ChDir PathName
    Workbooks.Open FileName:=PathName & PullWbkName, UpdateLinks:=3, ReadOnly:=True
'Naming the Range that contains the Pull Request data

        Find_Range_Boundaries
        MsgBox myRange
        Range(myRange).Name = "Pull_Data"

How do I use the variable that represents my range in my current macro to name the range?