# Last 2 rows in column that contain a word



## B-Man (Dec 21, 2022)

I'm trying to grab some data out of another spreadsheet that has the data in a table.

I have a table in B2:P36 ( will get bigger)

B2:B36 has the numbers from 1 to 33
Column F has data to row 15

I want to look up column F and find the last 2 rows that are filled with "teddy" so I can extract the data from the cells in column B and I and put them into my userform.

I have the code to open the sheet
And code to put the data into my userform but just missing getting the data. 

Cheers.


----------



## B-Man (Dec 22, 2022)

So this is what I come up with. I couldn't get find next working so used the address I had after the first search to offset by 1 row and search above it.
Its not pretty but works.
If someone can suggest a nicer/ cleaner way im all ears.


```
Set findcell = wsDest.Range("F:F").Find(What:="teddy", LookIn:=xlValues, SearchDirection:=xlPrevious)

 Set findcell2 = wsDest.Range("F4:" & findcell.Offset(-1, 0).Address).Find(What:="teddy", LookIn:=xlValues, SearchDirection:=xlPrevious)
```


----------



## B-Man (Dec 22, 2022)

ok so it sort of works...
once I implemented it into my code it doesnt display in my userform

```
With wsDest.Parent
                .Close True
            End With
Application.ScreenUpdating = True
```
after running this code (the close of the workbook opened to get the data) turns the formulas below that display the correct number when hovered over to "object variable or with block variable not set"
If i dont close the workbook it loads fine

```
Teddy.TextBox1.Value = findcell2.Offset(0, -4).Value
        Teddy.TextBox2.Value = findcell2.Offset(0, 3).Value * 100
```

I have worked around this by adding  the top line and moving the open userform above the close other sheet. surely there is a better way

```
ThisWorkbook.Activate


Application.ScreenUpdating = True

'Opens Userform
Teddy.Show

Application.ScreenUpdating = False
            With wsDest.Parent
                .Close True
            End With
Application.ScreenUpdating = True
```


----------

