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

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

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