Offset

[Syed Haider Ali]

Hi Freinds,

I have a Table with 2 Fields

A B
1000 ABC
2000 DEF
3000 GHI
4000 JKL


I want to Find "GHI" in col B and if found then shows the corresponding value of Col "A" i,e 3000.

In simple the Formula is

@offset(b1,match("GHI,b1:b4,0)-1,-1)

I neet the code in VBA-Excel

Thanks

Syed Haider Ali

[Jim Thomlinson]

This code assumes that you only want to find the first instacne of GHI but it
is easily modified to find all of the instances if you need...

Sub test()
Call FindStuff("GHI")
End Sub

Public Sub FindStuff(ByVal ThingToFind As Variant)
Dim wks As Worksheet
Dim rngToSearch As Range
Dim rngCurrent As Range

Set wks = ActiveSheet
Set rngToSearch = wks.Columns(2)
Set rngCurrent = rngToSearch.Find(ThingToFind)
If rngCurrent Is Nothing Then
MsgBox ThingToFind & " was not found."
Else
MsgBox rngCurrent.Offset(0, -1).Value
End If

End Sub

--
HTH...

Jim Thomlinson


"Syed Haider Ali" wrote:

>
> Hi Freinds,
>
> I have a Table with 2 Fields
>
> A B
> 1000 ABC
> 2000 DEF
> 3000 GHI
> 4000 JKL
>
>
> I want to Find "GHI" in col B and if found then shows the corresponding
> value of Col "A" i,e 3000.
>
> In simple the Formula is
>
> @offset(b1,match("GHI,b1:b4,0)-1,-1)
>
> I neet the code in VBA-Excel
>
> Thanks
>
> Syed Haider Ali
>
>
> --
> Syed Haider Ali
> ------------------------------------------------------------------------
> Syed Haider Ali's Profile: http://www.excelforum.com/member.php...o&userid=21994
> View this thread: http://www.excelforum.com/showthread...hreadid=400053
>
>

[JE McGimpsey]

One way:

Dim rFound As Range
Set rFound = Range("B:B").Find( _
What:="GHI", _
LookIn:=xlValues, _
LookAt:=xlWhole, _
MatchCase:=False)
If Not rFound Is Nothing Then _
MsgBox rFound.Offset(0, -1)

BTW - your XL formula would be a bit more efficient as

=INDEX(A1:A4,MATCH("GHI",B1:B4,FALSE))



In article
<[email protected]>,
Syed Haider Ali
<[email protected]> wrote:

> Hi Freinds,
>
> I have a Table with 2 Fields
>
> A B
> 1000 ABC
> 2000 DEF
> 3000 GHI
> 4000 JKL
>
>
> I want to Find "GHI" in col B and if found then shows the corresponding
> value of Col "A" i,e 3000.
>
> In simple the Formula is
>
> @offset(b1,match("GHI,b1:b4,0)-1,-1)
>
> I neet the code in VBA-Excel
>
> Thanks
>
> Syed Haider Ali