How to select ranges with variables
Posted by Brent Frame on July 06, 2000 1:54 PM
I need to write some VB code to run through and select varying ranges.
Take for example:
A B C
1 1 xxx xxx
2 2 xxx xxx
3 2 xxx xxx
4 2 xxx xxx
5 1 xxx xxx
6 2 xxx xxx
7 2 xxx xxx
8 2 xxx xxx
9 2 xxx xxx
10 2 xxx xxx
11 2 xxx xxx
12 1 xxx xxx
13 2 xxx xxx
14 2 xxx xxx
15 2 xxx xxx
I need code that will select a new range when column A = 1. So the first range in this example would be A1:C4. At that time I will use the range in a vLookup function on a separate worksheet. Then the range has to move on to the next set, A5:C11. This continues through the end of the list.
I know this has to be in a Loop, but I cant figure out how to write the code for a range with variables.
Thanks,
Brent
Posted by Ryan on July 06, 0100 4:58 PM
Hi Brent,
This will take care of the first part of your problem. Before the "Next x" line you will have to enter in the rest of the code you need to set up the VLookup. If you need somemore help let me know. Tell me how it works out!
Ryan
Sub FindOne()
Dim x As Long
Dim FindWhat As Variant
Dim FirstRow As Long
Dim LastRow As Long
Dim EndRow As Long
FindWhat = 1
EndRow = Range("A65536").End(xlUp).Row
For x = 1 To EndRow
LastRow = 0
If Cells(x, 1).Value = FindWhat Then
FirstRow = x
Do While LastRow = 0
x = x + 1
If Cells(x, 1).Value = FindWhat Then LastRow = x - 1
Loop
End If
Range("A" & FirstRow & ":C" & LastRow).Select
Next x
End Sub
