I have the following list of data in column A:
1
2
1
3
1
4
1
5
1
6

The following code will select the values that I want:
Sub SELECTVALUES()
Dim c As Range
Dim d As Range
Dim FirstAddress As String
Dim myFindString As String

myFindString = "1"
With ActiveSheet.Range("A:A")
Set c = .Find(myFindString, LookIn:=xlValues, lookAt:=xlWhole)

If Not c Is Nothing Then
Set d = c
FirstAddress = c.Address
End If

Set c = .FindNext(c)
If Not c Is Nothing And c.Address < FirstAddress Then
Do
Set d = Union(d, c)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < FirstAddress
End If
End With

d.Select

End Sub

I need help adjusting the code to selcect the data that is in columns B,C
and D instead of column A.

Judd,

Change

With ActiveSheet.Range("A:A")

to

With ActiveSheet.Range("B:D")

HTH,
Bernie
MS Excel MVP

"Judd Jones" wrote in message
...
I have the following list of data in column A:
1
2
1
3
1
4
1
5
1
6

The following code will select the values that I want:
Sub SELECTVALUES()
Dim c As Range
Dim d As Range
Dim FirstAddress As String
Dim myFindString As String

myFindString = "1"
With ActiveSheet.Range("A:A")
Set c = .Find(myFindString, LookIn:=xlValues, lookAt:=xlWhole)

If Not c Is Nothing Then
Set d = c
FirstAddress = c.Address
End If

Set c = .FindNext(c)
If Not c Is Nothing And c.Address < FirstAddress Then
Do
Set d = Union(d, c)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < FirstAddress
End If
End With

d.Select

End Sub

I need help adjusting the code to selcect the data that is in columns B,C
and D instead of column A.

I don't need the search range changed. I still want to search in column A but
select the info in columns B,C and D that are on the same row as the found
search.

"Bernie Deitrick" wrote:

Judd,

Change

With ActiveSheet.Range("A:A")

to

With ActiveSheet.Range("B:D")

HTH,
Bernie
MS Excel MVP

"Judd Jones" wrote in message
...
I have the following list of data in column A:
1
2
1
3
1
4
1
5
1
6

The following code will select the values that I want:
Sub SELECTVALUES()
Dim c As Range
Dim d As Range
Dim FirstAddress As String
Dim myFindString As String

myFindString = "1"
With ActiveSheet.Range("A:A")
Set c = .Find(myFindString, LookIn:=xlValues, lookAt:=xlWhole)

If Not c Is Nothing Then
Set d = c
FirstAddress = c.Address
End If

Set c = .FindNext(c)
If Not c Is Nothing And c.Address < FirstAddress Then
Do
Set d = Union(d, c)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < FirstAddress
End If
End With

d.Select

End Sub

I need help adjusting the code to selcect the data that is in columns B,C
and D instead of column A.

Judd,

Sorry for the misunderstanding.

With ActiveSheet.Range("A:A")
Set c = .Find(myFindString, LookIn:=xlValues, lookAt:=xlWhole)

If Not c Is Nothing Then
Set d = c.Offset(0,1).Resize(1,3)
FirstAddress = c.Address
End If

Set c = .FindNext(c)
If Not c Is Nothing And c.Address < FirstAddress Then
Do
Set d = Union(d, c.Offset(0,1).Resize(1,3))
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < FirstAddress
End If
End With

Should do it.

HTH,
Bernie
MS Excel MVP


"Judd Jones" wrote in message
...
I don't need the search range changed. I still want to search in column A
but
select the info in columns B,C and D that are on the same row as the found
search.

"Bernie Deitrick" wrote:

Judd,

Change

With ActiveSheet.Range("A:A")

to

With ActiveSheet.Range("B:D")

HTH,
Bernie
MS Excel MVP

"Judd Jones" wrote in message
...
I have the following list of data in column A:
1
2
1
3
1
4
1
5
1
6

The following code will select the values that I want:
Sub SELECTVALUES()
Dim c As Range
Dim d As Range
Dim FirstAddress As String
Dim myFindString As String

myFindString = "1"
With ActiveSheet.Range("A:A")
Set c = .Find(myFindString, LookIn:=xlValues, lookAt:=xlWhole)

If Not c Is Nothing Then
Set d = c
FirstAddress = c.Address
End If

Set c = .FindNext(c)
If Not c Is Nothing And c.Address < FirstAddress Then
Do
Set d = Union(d, c)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < FirstAddress
End If
End With

d.Select

End Sub

I need help adjusting the code to selcect the data that is in columns
B,C
and D instead of column A.

Bernie,

Thanks for all the help. One last question.

How do I get the code to exit out of the With statement if the value (1) is
never found?

Thanks,
Judd

"Bernie Deitrick" wrote:

Judd,

Sorry for the misunderstanding.

With ActiveSheet.Range("A:A")
Set c = .Find(myFindString, LookIn:=xlValues, lookAt:=xlWhole)

If Not c Is Nothing Then
Set d = c.Offset(0,1).Resize(1,3)
FirstAddress = c.Address
End If

Set c = .FindNext(c)
If Not c Is Nothing And c.Address < FirstAddress Then
Do
Set d = Union(d, c.Offset(0,1).Resize(1,3))
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < FirstAddress
End If
End With

Should do it.

HTH,
Bernie
MS Excel MVP

Judd,

If Not c Is Nothing Then
Set d = c.Offset(0,1).Resize(1,3)
FirstAddress = c.Address
Else
Exit Sub ' Or use Goto to skip to the next section of code
End If

HTH,
Bernie
MS Excel MVP


"Judd Jones" wrote in message
...
Bernie,

Thanks for all the help. One last question.

How do I get the code to exit out of the With statement if the value (1)
is
never found?

Thanks,
Judd

"Bernie Deitrick" wrote:

Judd,

Sorry for the misunderstanding.

With ActiveSheet.Range("A:A")
Set c = .Find(myFindString, LookIn:=xlValues, lookAt:=xlWhole)

If Not c Is Nothing Then
Set d = c.Offset(0,1).Resize(1,3)
FirstAddress = c.Address
End If

Set c = .FindNext(c)
If Not c Is Nothing And c.Address < FirstAddress Then
Do
Set d = Union(d, c.Offset(0,1).Resize(1,3))
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < FirstAddress
End If
End With

Should do it.

HTH,
Bernie
MS Excel MVP