Lookup?

[David W]

I am using
With Worksheets(1).Range("b5:b100")
Set c = .Find("YES", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
MsgBox c.Value & "found",vbOkOnly,"Found"
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
to find the "yes" value in column "B"
that works fine,

is there a way to get the value in the same row only in column d?

I am wanting to use the value in column d in the message instead of the text
"YES".

[Simon Shaw]

try:

MsgBox c.Value & " - "& c.cells(1,2).value & "found",vbOkOnly,"Found"


"David W" wrote:

> I am using
> With Worksheets(1).Range("b5:b100")
> Set c = .Find("YES", LookIn:=xlValues)
> If Not c Is Nothing Then
> firstAddress = c.Address
> Do
> MsgBox c.Value & "found",vbOkOnly,"Found"
> Set c = .FindNext(c)
> Loop While Not c Is Nothing And c.Address <> firstAddress
> End If
> End With
> to find the "yes" value in column "B"
> that works fine,
>
> is there a way to get the value in the same row only in column d?
>
> I am wanting to use the value in column d in the message instead of the text
> "YES".
>
>
>

[Jack]

David, this is how I would accomplish this task:

Dim Cell As Range

With Sheets(1)
For Each Cell In .Range("B5:B100")
If Cell.Value = "YES" Then
MsgBox .Range("D" & Cell.Row) & " Found!", vbOkOnly, "Found..."
End If
Next Cell
End With

Set Cell = Nothing


-Jack



"David W" wrote:

> I am using
> With Worksheets(1).Range("b5:b100")
> Set c = .Find("YES", LookIn:=xlValues)
> If Not c Is Nothing Then
> firstAddress = c.Address
> Do
> MsgBox c.Value & "found",vbOkOnly,"Found"
> Set c = .FindNext(c)
> Loop While Not c Is Nothing And c.Address <> firstAddress
> End If
> End With
> to find the "yes" value in column "B"
> that works fine,
>
> is there a way to get the value in the same row only in column d?
>
> I am wanting to use the value in column d in the message instead of the text
> "YES".
>
>
>

[Alan Beban]

Simon Shaw wrote:
> try:
>
> MsgBox c.Value & " - "& c.cells(1,2).value & "found",vbOkOnly,"Found"

Slightly more efficient:

MsgBox c.Value & " - " & c(1, 3).Value & "found", vbOKOnly, "Found"

The .cells is superfluous.

Alan Beban
>
>
> "David W" wrote:
>
>
>>I am using
>>With Worksheets(1).Range("b5:b100")
>> Set c = .Find("YES", LookIn:=xlValues)
>> If Not c Is Nothing Then
>> firstAddress = c.Address
>> Do
>> MsgBox c.Value & "found",vbOkOnly,"Found"
>> Set c = .FindNext(c)
>> Loop While Not c Is Nothing And c.Address <> firstAddress
>> End If
>>End With
>>to find the "yes" value in column "B"
>>that works fine,
>>
>>is there a way to get the value in the same row only in column d?
>>
>>I am wanting to use the value in column d in the message instead of the text
>>"YES".
>>
>>
>>