vba courses london - Looping macro to find chunks of data

The UK's most regular instructor-led training courses.
Training information: vba courses london · Excel london

Resolved · Low Priority · Version Standard

Looping macro to find chunks of data

Robert has attended:
Excel VBA course
Excel VBA course

by - delegate Robert [1 post] (2007 May 18 Fri, 15:03) Reply

I have a selection of reports which place one large chunk of data I need to select in random places.
I thought maybe a loop with the offset command to find it would work but cant think of how to write this.

any ideas?

RE: Looping macro to find chunks of data

by - trainer Garry gold contributer [188 posts] (2007 May 18 Fri, 16:06) Reply

Private Sub CommandButton1_Click()
Dim Cell As Range, SelectedRange As Range, X As String
X = InputBox("enter Name")

Set SelectedRange = Range("A1").CurrentRegion
For Each Cell In SelectedRange
If Cell = X Then
Cell.Font.ColorIndex = 3
End If
Next Cell

End Sub

· Company Roles in Visual Basic Training
· What Is Microsoft Excel VBA and How Can It Help You Work Smarter?

Vba tip:

Use GoTo to Select A Cell With VBA

To select a certain cell on a Worksheet you can use:

Application.Goto Reference:=Range("V300")

or more simply

Range("V300").Select

If, on the other hand, you want the selected cell to be the top/left cell on the screen you use:

Application.Goto Reference:=Range("V300"), Scroll=True

View all VBA hints and tips