CODEWITHSUNDEEP
vbaexcel
Morlo4
Morlo4

Reputation: 1

VBA, Finding cell adresses and using as range for another find

I'm currently tearing my hair out trying to work out whats going on, I'm trying to use two seperate search functions in VBA to find the start and end of a range of cells and then set that as the range for another search. It's probably something really simple but I can't for the life of me figure out, I've included my current code below.

Truecheck is a global variable that stores the name to be searched for within the spreadhseet. the start and end of the range I want to define will have the same name so truecheck should work for both.

Does anybody have any ideas how to make this work ?

At the moment it is throwing up a object required error, highlighting the part where I set "Firstrow = Range ....". I also think that there is more than one problem going on here however

Edit: Potatoes.value and Textboxinput.value are text boxes on a userform

Private Sub optionselect()
    Dim LastLocation As String
    Dim FirstLocation As String
    Dim FirstRow As Long
    Dim LastRow As Long
    Dim SearchVal As String

    FirstLocation = Range("B:B").Find(truecheck,_
    LookIn:=xlValues,LookAt:=xlWhole, SearchOrder:=xlByRows)
    LastLocation = Range("B:B").Find(truecheck, LookIn:=xlValues,_ 
    LookAt:=xlWhole, SearchOrder:=xlByRows, searchdirection:=xlPrevious)

    FirstRow = Range(FirstLocation).Row
    LastRow = Range(LastLocation).Row

    Potatoes.Value = Application.WorksheetFunction.VLookup(LengthInputText.Value,_
    Range(Cells(FirstRow, 8), Cells(LastRow, 8)), 6, False)

End Sub

Upvotes: 0

Views: 46

Answers (2)

Kerry White
Kerry White

Reputation: 416

It looks like the return type for Range.Find is a range not a string. Change your code to look like this and try it:

Private Sub optionselect()
Dim LastLocation As Range
Dim FirstLocation As Range
Dim FirstRow As Long
Dim LastRow As Long
Dim SearchVal As String

Set FirstLocation = Range("B:B").Find("It", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows)
Set LastLocation = Range("B:B").Find(truecheck, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, searchdirection:=xlPrevious)

FirstRow = FirstLocation.Row
LastRow = LastLocation.Row

Potatoes.Value = Application.WorksheetFunction.VLookup(LengthInputText.Value,_
Range(Cells(FirstRow, 8), Cells(LastRow, 8)), 6, False)

End Sub

FYI: https://msdn.microsoft.com/en-us/library/office/ff839746.aspx

Upvotes: 0

Scott Holtzman
Scott Holtzman

Reputation: 27249

FirstLocation and LastLocation are both defined asStringyet you are assigning them to aRangeobject in the way the statement is written, as theFindmethod returns the cell (orRange` object) of the found cell.

The simplest way to get this to work would be to add the Address property to the call.

FirstLocation = Range("B:B").Find(truecheck,_
    LookIn:=xlValues,LookAt:=xlWhole, SearchOrder:=xlByRows).Address
LastLocation = Range("B:B").Find(truecheck, LookIn:=xlValues,_ 
    LookAt:=xlWhole, SearchOrder:=xlByRows, searchdirection:=xlPrevious).Address

However, this can also be done:

Dim FirstLocation as Range, LastLocation as Range

Set FirstLocation = Range("B:B").Find(truecheck,_
    LookIn:=xlValues,LookAt:=xlWhole, SearchOrder:=xlByRows)
Set LastLocation = Range("B:B").Find(truecheck, LookIn:=xlValues,_ 
    LookAt:=xlWhole, SearchOrder:=xlByRows, searchdirection:=xlPrevious)

FirstRow = FirstLocation.Row
LastRow = LastLocation.Row

Upvotes: 1

Related Questions