Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
OFFSET | Excel Worksheet Functions | |||
Compare Cell Values, Offset(-1,0), Offset(-1,-1), and xlFillDefaul | Excel Worksheet Functions | |||
Offset Help | Excel Worksheet Functions | |||
OFFSET PLEASE HELP! | Excel Discussion (Misc queries) | |||
Problem with Range.Cells.Offset and Range.Cells( row + offset, column) | Excel Programming |