Formula in VBA
 

Hi
I am a beginner of VBA and this is my first VBA project. I am workin
on this excel matrix (see below a portion of the matrix) and attempt t
call this matrix in excel sheet from VBA.

What I tried to do is select a cell based on the condtion on from an
to species (JP, SC, RN, RP and WP are speceis code). For example, i
the condition is RN (from) and JP (to), the VBA function should giv
-0.083 for a variable name A1.

I used a formula (=OFFSET(AB3,MATCH(E5,$AB$4:$AB$71,0),MATCH(C5,$A
$3:$CR$3,0))) in Exel sheet, it works fine but I tried to use the sam
in VBA in excel, but it does not work. Any help will be highl
appreciated.

Thanks
Bharat




From/To JP SC RN RP WP
JP 0 0 -0.083 -0.083 0
SC 0 0 -0.083 -0.083 0
RN 0.081 0.081 0 0 3.926
RP 0.081 0.081 0 0 3.926
WP 0 0 -4.094 -4.094

--
bharatPosted from http://www.pcreview.co.uk/ newsgroup acces

Formula in VBA
 

in the vbe type in match then touch the f1 key

--
Don Guillett
SalesAid Software

"bharat" <bharat.1q5xpa@ wrote in message
...

Hi
I am a beginner of VBA and this is my first VBA project. I am working
on this excel matrix (see below a portion of the matrix) and attempt to
call this matrix in excel sheet from VBA.

What I tried to do is select a cell based on the condtion on from and
to species (JP, SC, RN, RP and WP are speceis code). For example, if
the condition is RN (from) and JP (to), the VBA function should give
-0.083 for a variable name A1.

I used a formula (=OFFSET(AB3,MATCH(E5,$AB$4:$AB$71,0),MATCH(C5,$AC
$3:$CR$3,0))) in Exel sheet, it works fine but I tried to use the same
in VBA in excel, but it does not work. Any help will be highly
appreciated.

Thanks
Bharat




From/To JP SC RN RP WP
JP 0 0 -0.083 -0.083 0
SC 0 0 -0.083 -0.083 0
RN 0.081 0.081 0 0 3.926
RP 0.081 0.081 0 0 3.926
WP 0 0 -4.094 -4.094 0


--
bharatPosted from http://www.pcreview.co.uk/ newsgroup access

Formula in VBA
 

bharat:


Hi
I am a beginner of VBA and this is my first VBA project. I am working
on this excel matrix (see below a portion of the matrix) and attempt to
call this matrix in excel sheet from VBA.

What I tried to do is select a cell based on the condtion on from and
to species (JP, SC, RN, RP and WP are speceis code). For example, if
the condition is RN (from) and JP (to), the VBA function should give
-0.083 for a variable name A1.

I used a formula (=OFFSET(AB3,MATCH(E5,$AB$4:$AB$71,0),MATCH(C5,$AC
$3:$CR$3,0))) in Exel sheet, it works fine but I tried to use the same
in VBA in excel, but it does not work. Any help will be highly
appreciated.

Thanks
Bharat




From/To JP SC RN RP WP
JP 0 0 -0.083 -0.083 0
SC 0 0 -0.083 -0.083 0
RN 0.081 0.081 0 0 3.926
RP 0.081 0.081 0 0 3.926
WP 0 0 -4.094 -4.094 0


Sub bharat()
Dim tbl As Range
Dim r As Byte
Dim c As Byte

Set tbl = Range("AB3:CR71")

With Application.WorksheetFunction
r = .Match(E5, Range("$AB$3:$AB$71", 0))
c = .Match(C5, Range("$AB$3:$CR$3", 0))
End With

Range("A1").Value = tbl.Cells(r, c).Value
Set tbl = Nothing

End Sub



Attention! Insert in E5 and in C5 correct value!




( )---cucchiaino

Formula in VBA
 

Thanks a lot Cucchiaino,
It did not work. The value at column E and C are string. earlier
pull the example for only one cell, I am working for about 5000
entries in the database.

Those values at $AB$3:$AB$71" and ("$AB$3:$CR$3" are also string wit
two letter code.

The error message comes in E5 and C5. I also tried to select the shee
(Sheets("Macro").Select) in the workbook of bharat.

Based on the code in column c and E, this vba code should be able t
grab a value. Then this value will be used to as an coefficient 1 fo
the formula of Coeff1+Coeff2*Value from column D.

Sorry I may not be able to explain the problem.

Thanks a lot for the help.

cheers
Bharat

Thank you
Cheers
Bhara

--
bharatPosted from http://www.pcreview.co.uk/ newsgroup acces