Excel VBA - Storing Find result in a range variable

Question

I am trying to store ranges in range variables startID, endID, and endDest, but I keep getting runtime error 1004 "Method Range of object _Global failed."

I have a list of loans on the Control Page of my workbook, and they all have multiple lines (anywhere from 2 to 6 lines) like this:

A | B | C
loan 1 | stuff | 1.0
loan 1 | stuff | 1.1
loan 2 | stuff | 2.0
loan 2 | stuff | 2.1
loan 2 | stuff | 2.2

The whole purpose is to move the loan where the activecell is down to the bottom of the list. Here's what I'm having trouble with (so far): Say the activecell is B2 in the little bulleted list makeshift worksheet above--the row of the activecell along with a fixed column index of 3 indicates that I'm on line 1.1. I then "fix" 1.1 to 1 and store that integer in currentID. Now I can Find the first occurrence of currentID in Column C, then store the address of the range where currentID appears in the range variable startID. In this case, I should be storing C1 in startID.

I have read that Find returns a range. So I tried Set startID = .Range("C:C").Find(what:=currentID), but that just stores the currentID of 1 in startID. So then I tried Set startID = Range(.Range("C:C").Find(what:=currentID)) (which is what appears below in my code block), but that causes the runtime error. I have also tried adding and removing periods in case I was messing up the With statement, but that didn't help. Any thoughts?

If there is some obvious thing I'm missing, I apologize. I have been using VBA for about three weeks now, so I typically have to google every single thing I need until I start remembering recurring themes.

Sub ExpireLoan()
With Workbooks("1908 AUS IC Loans Recon.xlsm").Worksheets("Control Page")
Dim currentID As Integer
Dim startID As Range
Dim endID As Range
Dim endDest As Range
Dim rowCount As Integer

'find the current loan range
    currentID = Fix(.Cells(ActiveCell.Row, 3).Value)
    Set startID = Range(.Range("C:C").Find(what:=currentID))
    Set endID = Range(.Range("C:C").Find(what:=currentID + 1))
    Set endID = Range(endID.Offset(-1, 0))

'find the last row
    Set endDest = .Range("A7").End(xlDown)
    Set endDest = endDest.Offset(-1, 0)

'copy the current loan and paste into the end of the table
    rowCount = .Range(startID, endID).Count
    .Range(startID, endID).EntireRow.Copy
    .endDest.EntireRow.PasteSpecial

'set bottom border at new end of the table
    endDest.Select
    ActiveCell.Offset(0, 0).Range("A1:AF1").Borders(xlEdgeBottom).LineStyle = xlDouble

'delete rows from loan's original position above
    .Range(startID, endID).EntireRow.Delete

'set sort ID
    ActiveCell.Offset(0, 2).Value = Fix(ActiveCell.Offset(-1, 2).Value) + 1
    For i = 0 To rowCount - 2
        ActiveCell.Offset(i + 1, 2).Value = ActiveCell.Offset(i, 2).Value + 0.1
    Next

'delete loanTypeCode
    ActiveCell.Offset(0, 1).ClearContents
    For i = 1 To rowCount - 1
        ActiveCell.Offset(i, 1).ClearContents
    Next

'fix sortID
    Set sortIdRange = Range(startID, startID.End(xlDown))
    Call subtractOneFromCells

'make it red
    ActiveCell.Offset(0, 0).Range("A1:AF1").Interior.TintAndShade = 0.399975585192419
    For i = 1 To rowCount - 1
        ActiveCell.Offset(i, 17).Range("A1:P1").Interior.TintAndShade = 0.399975585192419
    Next

End With
End Sub

It could be that the function just cannot find the search criteria. — Walter Kloosterboer, Oct 14 '19 at 06:20
Change `Set startID = Range(.Range("C:C").Find(what:=currentID))` to `Set startID = .Range("C:C").Find(what:=currentID)` and then check if it returned something or not by using `If Not startID is Nothing` Also try and use full parameters. [This](http://www.siddharthrout.com/index.php/2018/01/05/find-and-findnext-in-excel-vba/) will help you on how to use `.Find` — Siddharth Rout, Oct 14 '19 at 06:38
If the suggestion above does not help, then have a look at [this](https://stackoverflow.com/q/58310151/9758194) question and answer. Using `.Find` with large numeric values (above 8 characters) will start to become buggy. Looking in `xlFormulas` does actually help! — JvdV, Oct 14 '19 at 07:38
@walter kloosterboer, The function should definitely be able to find the search criteria because I'm getting the search criteria from that column in the first place. — smcamero, Oct 14 '19 at 16:13
@Siddharth Rout, when I try your suggestion, the function returns currentID (which in the example above is 1. What I really wanted was the address of the first occurrence of 1, which would be C1. — smcamero, Oct 14 '19 at 16:17
@JvdV, I tried looking in xlFormulas, but that didn't help. Find is still returning the value of the cell instead of the cell address :( — smcamero, Oct 14 '19 at 16:17
If you would have visited the link which I gave above you would have realized how `.Find` works and how you can use `.Address` to find the address of the cell... — Siddharth Rout, Oct 15 '19 at 02:40

riskypenguin · Accepted Answer · 2019-10-15T07:01:18.620

In my experience .Find can act a little weird when looking for numbers. Using xlWhole usually fixes it for me and it also did when I tried it on your sample data. Otherwise, if you're looking for the currentID = 3 for example, .Find would return the fist cell that has a 3 in it as a sub-string. That means a cell with the value 1.3 could be returned as the first occurrence of 3. Try this line:

Set startID = .Range("C:C").Find(What:=currentID, LookAt:=xlWhole)

To debug this you might use F8 to step through the code line by line and use Debug.Print currentID and Debug.Print startID.Address to see what is happening through the immediate window.

A tip for shorter code (not necessesarily always more readable) is the following: You can consolidate two lines like these:

Set endDest = .Range("A7").End(xlDown)
Set endDest = endDest.Offset(-1, 0)

into one line like this:

Set endDest = .Range("A7").End(xlDown).Offset(-1, 0)

Since your first line returns a Range object there is no need to store it in a variable inbetween steps.

On another note, regarding these two lines:

endDest.Select
ActiveCell.Offset(0, 0).Range("A1:AF1").Borders(xlEdgeBottom).LineStyle = xlDouble

First of all it is recommended to avoid using .Select whenever possible as it is described here: How to avoid using Select in Excel VBA. Secondly, that second line is hard to understand. Try using Resize like this instead:

ActiveCell.Resize(1, 32).Borders(xlEdgeBottom).LineStyle = xlDouble

Thank you for the thoughtful answer! Unfortunately, `xlWhole` didn't work. However, you did help me figure it out by suggesting the debug thing in the immediate window. `startID.Address` did return the right address. So I ended up reverting to `Set startID = .Range("C:C").Find(what:=currentID, lookat:=xlWhole, LookIn:=xlFormulas)` which returns the cell value instead of the address. But later when I referred to the startID, I had to add `.Address` in `.Range(startID.Address, endID.Address).EntireRow.Copy`. And thank you for the other tips! — smcamero, Oct 14 '19 at 16:31

Excel VBA - Storing Find result in a range variable

1 Answers1