Using Find function to locate cell which contains a set value

Question

I'm trying to set the first and last rows of a For loop. The 'controlCell' and 'firstrow' work as intended but I can't get the 'lastrow' piece to work.

The code I'm using is this:

    Dim k As Integer
    Dim controlCell As Integer
    controlCell = Range("F9").Value
    Dim firstrow As Integer
        With Columns("A")
            .Find(what:="*", after:=.Cells(1, 1), LookIn:=xlFormulas).Activate
            firstrow = ActiveCell.Row
        End With
    Dim lastrow As Integer
        With Columns("E")
            .Find(what:=.Range(controlCell), after:=.Cells(18, 5), LookIn:=xlValues).Activate
            lastrow = ActiveCell.Row
        End With
        For k = firstrow To lastrow

For example, there will be a value of 22 in the controlCell, there are dates in Column A and in column E from row 18, there will be numbers 1 to another number. I need to call out where 22 is in this column and run the For loop between the firstrow and lastrow. The number in the controlCell is not always 22.

Running the code as is returns a Compile error: Syntax error on the line

.Find(what:=.Range(controlCell), after:=.Cells(18, 5), LookIn:=xlValues).Activate

Thanks in advance.

Does this answer your question? [Find last used cell in Excel VBA](https://stackoverflow.com/questions/11169445/find-last-used-cell-in-excel-vba) — cybernetic.nomad, May 31 '23 at 15:17
If there's a value of `22` in `controlCell`, then `.Range(controlCell)` is invalid. If you want to find `22`, then just use `What:=controlCell`. — BigBen, May 31 '23 at 15:20
FYI `Columns("E").Cells(18, 5)` is I18 - is that what you meant? — Tim Williams, May 31 '23 at 15:20
@BigBen Thanks. When I try this I get an error 'Type Mismatch' on the same piece of code. — Keith K, May 31 '23 at 15:30
@TimWilliams Oh, no. That is not what I'm trying to achieve. Should it be Columns("A") Cells(18, 5) if I want it to look in Col E? — Keith K, May 31 '23 at 15:33
It should be `.Cells(18)`. Also you don't need to `.Activate` — BigBen, May 31 '23 at 15:38
Thanks both. Using both anwers I fixed it by using "With Columns("E") .Find(what:=controlCell, after:=.Cells(18, 1)).Activate — Keith K, May 31 '23 at 15:40

Using Find function to locate cell which contains a set value

0 Answers0