Re: Translating spreadsheet formula to 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


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

[Mark Seger]

the thing to so is go to tools->macro->record new macro. Now enter your
formula into the spreadsheet and then tools->macro->stop recording.
Then, go into tools->macro->edit and see what was recorded. It's that easy.
-mark

bharat wrote:
> 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
>
>

[Don Wiss]

On Sun, 5 Jun 2005 17:41:15 +0100, bharat <bharat.1q5xpb@> wrote:

>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.

This is untested, as I don't know your ranges, but it would go something
like this:

=Range("AC4:CR71").Cells(WorksheetFunction.Match(Range("E5").Value,Range("AB4:AB71"),0),WorksheetFunction.Match(Range("C5").Value,Range("AC3:CR3"),0)).Value

The .Value inside the Match arguments may not be needed.

And better to give the ranges on the worksheet names, so you can
insert/delete rows/columns and move the cells around without crippling your
VBA code.

Don <www.donwiss.com> (e-mail link at home page bottom).