Offset
 

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

Offset
 

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

Offset
 

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
<Syed.Haider.Ali.1uj46h_1125324326.0393@excelfor um-nospam.com,
Syed Haider Ali
<Syed.Haider.Ali.1uj46h_1125324326.0393@excelfor um-nospam.com 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