Select range until cell with certain text in Excel with VBA

Question

Trying to select header / first row and data under it from columns C to O Until the first cell in column C has the word "Estimated" I came up with this VBA code based on another answer on stackoverflow, but it doesn't seem to be working

All help is deeply appreciated!!

Range("C1").Select
For i = 1 To 9999
If ISNUMBER(SEARCH(ActiveCell.Offset(0, i), "Estimated")) = TRUE Then Exit For
If ISNUMBER(SEARCH(ActiveCell.Offset(0, i), "Estimated")) = FALSE Then Exit For
Next
If ISNUMBER(SEARCH(ActiveCell.Offset(0, i), "Estimated")) = TRUE Then             
    Range(Cells(ActiveCell.Row, 15), Cells(ActiveCell.Row, ActiveCell.Column + i - 1)).Select

`

why not use the `Range.Find` method? Also, you [probably don't need to `Select` anything or use the `Activate/ActiveCell`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) (and it should generally be avoided). — David Zemens, Jul 12 '18 at 20:58

score 1 · Answer 1 · answered Jul 12 '18 at 21:01

1

Try,

range(cells(1, "C"), cells(application.match("Estimated", columns(3), 0)-1, "O")).select

Not sure from your narrative whether the row containing "Estimated" was to be included or not. The -1 tells it not to include the "Estimated" row.

answered Jul 12 '18 at 21:01

Thanks for the response. it is supposed to exclude it. When I ran it, I got "type mismatch" as an error. – Daren S Jul 18 '18 at 14:06

score 0 · Answer 2 · answered Jul 13 '18 at 13:57

0

you could use

Range("O1", Columns(3).Find("Estimated", , xlValues, xlPart)).Select ' to include row with "Estimated" in column C

or

Range("O1", Columns(3).Find("Estimated", , xlValues, xlPart).offset(-1)).Select ' to exclude row with "Estimated" in column C

answered Jul 13 '18 at 13:57

DisplayName

13,283
2
11
19

Thanks for having both of them. I used the second one, but when I ran it, I got "object variable or with block variable not set" as an error. – Daren S Jul 18 '18 at 14:06
Is “Estimated” possibly not there? – DisplayName Jul 19 '18 at 08:47

Select range until cell with certain text in Excel with VBA

2 Answers2