vba problem lookup in a matrix
 

Hi,

I have the following problem:

on sheet1 i have cells A20:A30 filled with stuff like

18-20
20-25
25-30
etc
furthermore i have two variables var1 and var2 which are both numbers

on sheet2 i have a matrix in the range ("A4:CS53")
in colmn on i have a list of numers ranging from 1 to 65

I would like to do the following
1. take the first 2 characters in A20, sheet1 and look his number up in the
first column of the matrix (sheet2) to determine the row and then..
2. i need column number -58 + var1 + var2 to lookup the cell i need and
return this value
3. i need to put this value onsheet1 in cell "C20"
3. I need to have the same thing done for the cells A21 through A30 on sheet1

Please help me with the code tot do this !
Thanks,
Pierre


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200509/1

vba problem lookup in a matrix
 

Tom and I have already tried to help you with this problem. What is wrong
with the offerings so far?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jean-Pierre D via OfficeKB.com" wrote in message
...
Hi,

I have the following problem:

on sheet1 i have cells A20:A30 filled with stuff like

18-20
20-25
25-30
etc
furthermore i have two variables var1 and var2 which are both numbers

on sheet2 i have a matrix in the range ("A4:CS53")
in colmn on i have a list of numers ranging from 1 to 65

I would like to do the following
1. take the first 2 characters in A20, sheet1 and look his number up in
the
first column of the matrix (sheet2) to determine the row and then..
2. i need column number -58 + var1 + var2 to lookup the cell i need and
return this value
3. i need to put this value onsheet1 in cell "C20"
3. I need to have the same thing done for the cells A21 through A30 on
sheet1

Please help me with the code tot do this !
Thanks,
Pierre


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200509/1

vba problem lookup in a matrix
 

Hi Bob,

Sorry,

I tried to use the code but it all got mixed up.
Probably because i didn't use it in the right way. I got an error message
each time i tried the code, spent hours to try to adjust it but it did not
work at all.
Part of the problem, i think was the activecell part in your code, because
cell A20 is not the active cell when the code starts to run.
furthermore i get an error message "wrong procedure call or invalid argument"
at the 'set cell' line of your code.
Also i forgot to tell you that i needed the code to run several times (A20:
A30) and put the result of each calculation in (c20:c30)

Sorry to bother you again with more or less the same question but i just
cannot seem to get i right.
Please help again ?

Pierre

Bob Phillips wrote:
Tom and I have already tried to help you with this problem. What is wrong
with the offerings so far?

Hi,

[quoted text clipped - 22 lines]
Thanks,
Pierre


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200509/1

vba problem lookup in a matrix
 

So is this closer

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 = 31 'replace with real value
Var2 = 32 'replace with real value
For Each rng In Range("A20:A30")
res = InStr(1, rng.Value, " -")
If res 0 Then
Set cell = Worksheets("Data").Range("A4:A53"). _
Find(CLng(Left(rng.Value, res - 1)))
If Not cell Is Nothing Then
result = Worksheets("Data").Cells(cell.Row, cell.Column - 58
+ var1 + Var2)
rng.Offset(0, 2).Value = result
End If
End If
Next rng
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jean-Pierre D via OfficeKB.com" wrote in message
...
Hi Bob,

Sorry,

I tried to use the code but it all got mixed up.
Probably because i didn't use it in the right way. I got an error message
each time i tried the code, spent hours to try to adjust it but it did not
work at all.
Part of the problem, i think was the activecell part in your code, because
cell A20 is not the active cell when the code starts to run.
furthermore i get an error message "wrong procedure call or invalid
argument"
at the 'set cell' line of your code.
Also i forgot to tell you that i needed the code to run several times
(A20:
A30) and put the result of each calculation in (c20:c30)

Sorry to bother you again with more or less the same question but i just
cannot seem to get i right.
Please help again ?

Pierre

Bob Phillips wrote:
Tom and I have already tried to help you with this problem. What is wrong
with the offerings so far?

Hi,

[quoted text clipped - 22 lines]
Thanks,
Pierre


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200509/1

vba problem lookup in a matrix
 

Thanks Bob, it works like a charm !!!!
much obliged,
Jean-Pierre

Bob Phillips wrote:
So is this closer

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 = 31 'replace with real value
Var2 = 32 'replace with real value
For Each rng In Range("A20:A30")
res = InStr(1, rng.Value, " -")
If res 0 Then
Set cell = Worksheets("Data").Range("A4:A53"). _
Find(CLng(Left(rng.Value, res - 1)))
If Not cell Is Nothing Then
result = Worksheets("Data").Cells(cell.Row, cell.Column - 58
+ var1 + Var2)
rng.Offset(0, 2).Value = result
End If
End If
Next rng
End Sub

Hi Bob,

[quoted text clipped - 25 lines]
Thanks,
Pierre


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200509/1

vba problem lookup in a matrix
 

hi Bob,

Just one more question.
i have several txt boxes in the userform.
Each time a txt box is changed i need to execute this code on exit of the txt
box
do i need to call the sub at each txtbox or is there a better way to do this?
thanks,
Jean-Pierre

Bob Phillips wrote:
So is this closer

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 = 31 'replace with real value
Var2 = 32 'replace with real value
For Each rng In Range("A20:A30")
res = InStr(1, rng.Value, " -")
If res 0 Then
Set cell = Worksheets("Data").Range("A4:A53"). _
Find(CLng(Left(rng.Value, res - 1)))
If Not cell Is Nothing Then
result = Worksheets("Data").Cells(cell.Row, cell.Column - 58
+ var1 + Var2)
rng.Offset(0, 2).Value = result
End If
End If
Next rng
End Sub

Hi Bob,

[quoted text clipped - 25 lines]
Thanks,
Pierre


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200509/1

vba problem lookup in a matrix
 

There is another way, but it requires a bit of setup, and it doesn't support
the exit event. If you call a macro, it is just one line.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jean-Pierre D via OfficeKB.com" wrote in message
...
hi Bob,

Just one more question.
i have several txt boxes in the userform.
Each time a txt box is changed i need to execute this code on exit of the
txt
box
do i need to call the sub at each txtbox or is there a better way to do
this?
thanks,
Jean-Pierre

Bob Phillips wrote:
So is this closer

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 = 31 'replace with real value
Var2 = 32 'replace with real value
For Each rng In Range("A20:A30")
res = InStr(1, rng.Value, " -")
If res 0 Then
Set cell = Worksheets("Data").Range("A4:A53"). _
Find(CLng(Left(rng.Value, res - 1)))
If Not cell Is Nothing Then
result = Worksheets("Data").Cells(cell.Row, cell.Column -
58
+ var1 + Var2)
rng.Offset(0, 2).Value = result
End If
End If
Next rng
End Sub

Hi Bob,

[quoted text clipped - 25 lines]
Thanks,
Pierre


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200509/1