Using VBA, select from activecell to a certain value below?

[xluserg]

Hi Good day

I've been trying to work this out, and then googled with no luck.

How do I highlight "Select" from activecell, down to a cell below that has the value "Total" using VBA code.

Sometimes there is a different number of cells in between these two values.

Thanks

Graham

 

[machopicho]

Try this:

Sub Macro1()
n = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row
For r = ActiveCell.Row To n
If Cells(r, ActiveCell.Column) = "Total" Then
Range(ActiveCell, Cells(r, ActiveCell.Column)).Select
End If
Next
End Sub

 

[Richard Schollar]

One way:

Dim rngFound As Range

Set rngFound = Activecell.EntireColumn.Find(What:="Total",After:=Activecell,SearchDirection:=xlNext,LookAt:=xlWhole,Lookin:=xlValues)

If Not rngFound Is Nothing Then
  Range(Activecell,rngFound).Select
Else
 MsgBox "Total not found below active cell!"
End if

 

[xluserg]

hi Machopicho
Your code selects from activecell to the 8th occurrence of Totol "activecell A15 to A160" I don't know why that is?


Hi Richard
Your code works fine


Thanks guys for the replies


Also what if I wanted to do the same to find a word in a string of words and numbers: "date 1990001 Total 09837" (could be any length of words and numbers)

Thanks again guys

Graham

 

[Weaver]

To answer the second question, see Richard's solution

LookAt:=xlWhole

change to

LookAt:=xlPart

Machopicho's solution does what you have identified because it doesn't stop after it finds the first 'total' but rather after it has checked all the data in the column.

 

[xluserg]

Thanks Weaver

Solved!

Graham

 

[Weaver]

No problem. Thanks for the feedback.

W