Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
index function in vba
Hi,
I have a problem finding data in a matrix. I have a cell that is filled with stuff like "18 - 20" I have a matrix on another sheet in the range worksheets("staffels").range("A4:CS53") Now i would like to do the following: take the first two items ("18") of the cell minus the number 14 and look this ip in column 1 of the matrix and then i need the result from column number -58 + var1 + var2 Does anybody have a hint how to do this in VBA ? Thanks, Pierre |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
index function in vba
Dmi var1 as Long, Var2 as Long
Dim rng as Range, res as Variant Dim rw as Long, result as Variant var1 = ? Var2 = ? set rng = worksheets("Data").Range("A4:A53") res = Application.Match(clng(left(activecell,2)),rng,0) if not iserror(res) then rw = rng(res).row result = cells(rw,-58+var1+var2) msgbox result End if Assumes column 1 contains numbers stored as numbers -- Regards, Tom Ogilvy "Jean-Pierre" wrote in message ... Hi, I have a problem finding data in a matrix. I have a cell that is filled with stuff like "18 - 20" I have a matrix on another sheet in the range worksheets("staffels").range("A4:CS53") Now i would like to do the following: take the first two items ("18") of the cell minus the number 14 and look this ip in column 1 of the matrix and then i need the result from column number -58 + var1 + var2 Does anybody have a hint how to do this in VBA ? Thanks, Pierre |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
index function in vba
Sub GetVal()
Dim var1 As Long Dim Var2 As Long Dim rng As Range Dim cell As Range Dim res As Variant Dim rw As Long, result As Variant var1 = 1 Var2 = 2 Set rng = Worksheets("Data").Range("A4:CS53") res = InStr(1, ActiveCell.Value, " -") If res 0 Then Set cell = rng.Find(CLng(Left(ActiveCell, res - 1))) If Not cel Is Nothing Then If cell.Column (58 - var1 - Var2) Then result = Cells(cell.Row, cell.Column - 58 + var1 + Var2) MsgBox result End If End If End If End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Jean-Pierre" wrote in message ... Hi, I have a problem finding data in a matrix. I have a cell that is filled with stuff like "18 - 20" I have a matrix on another sheet in the range worksheets("staffels").range("A4:CS53") Now i would like to do the following: take the first two items ("18") of the cell minus the number 14 and look this ip in column 1 of the matrix and then i need the result from column number -58 + var1 + var2 Does anybody have a hint how to do this in VBA ? Thanks, Pierre |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using INDIRECT Function and INDEX Function | Excel Discussion (Misc queries) | |||
INDEX function | Excel Worksheet Functions | |||
INDEX FUNCTION | New Users to Excel | |||
Index Function/Match Function | Excel Discussion (Misc queries) | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions |