Lotus Macro in Excel 97 Upgraded to Excel 2003

R

richvj22

Hi All,

I need help here big time....I have a macro that is in a Excel 97
spreadsheet that was written in Lotus. The macro is triggered by
hitting ctrl-c. You can see the problem this is causing now trying to
run this macro in Excel 2003. My question is how do I change the key
command that runs this macro? Below is the syntax....Thanks...

\C /WGPD
/RVag21~Ag6~
EUR {IF
ah5<>0}/RVam1~am3~/RVal2~al4~{GOTO}ah5~0~{GOTO}ai5~1~{GOTO}aj5~1~{GOTO}ai2~1~{GOTO}ai3~1~/Cah5..aj5~ah6..aj34~
{goto}ai2~1~{goto}ai3~1~/RVam1~am3~
YEN {IF
ap5<>0}/RVau1~au3~/RVat2~at4~{GOTO}ap5~0~{GOTO}aq5~100~{GOTO}ar5~1~{GOTO}aq2~100~{GOTO}aq3~100~/Cap5..ar5~ap6..ar34~
{GOTO}aq2~100~{GOTO}aq3~100~/RVau1~au3~
SFR {IF
ax5<>0}/RVbc1~bc3~/RVbb2~bb4~{GOTO}ax5~0~{GOTO}ay5~1~{GOTO}az5~1~{GOTO}ay2~1~{GOTO}ay3~1~/Cax5..az5~ax6..az34~
{GOTO}ay2~1~{GOTO}ay3~1~/RVbc1~bc3~
GBP {IF
bf5<>0}/RVbk1~bk3~/RVbj2~bj4~{GOTO}bf5~0~{GOTO}bg5~1~{GOTO}bh5~1~{GOTO}bg2~1~{GOTO}bg3~1~/Cbf5..bh5~bf6..bh34~
{GOTO}bg2~1~{GOTO}bg3~1~/RVbk1~bk3~
CAD {IF
bn5<>0}/RVbs1~bs3~/RVbr2~br4~{GOTO}bn5~0~{GOTO}bo5~1~{GOTO}bp5~1~{GOTO}bo2~1~{GOTO}bo3~1~/Cbn5..bp5~bn6..bp34~
{GOTO}bo2~1~{GOTO}bo3~1~/RVbs1~bs3~
AUD {IF
bv5<>0}/RVca1~ca3~/RVbz2~bz4~{GOTO}bv5~0~{GOTO}bw5~1~{GOTO}bx5~1~{GOTO}bw2~1~{GOTO}bw3~1~/Cbv5..bx5~bv6..bx18~
{GOTO}bw2~1~{GOTO}bw3~1~/RVca1~ca3~
NZD {IF
bv25<>0}/RVca21~ca23~/RVbz22~bz24~{GOTO}bv25~0~{GOTO}bw25~1~{GOTO}bx25~1~{GOTO}bw22~1~{GOTO}bw23~1~/Cbv25..bx25~bv26..bx35~
{GOTO}bw22~1~{GOTO}bw23~1~/RVca21~ca23~
DEM {IF
cd5<>0}/RVci1~ci3~/RVch2~ch4~{GOTO}cd5~0~{GOTO}ce5~1~{GOTO}cf5~1~{GOTO}ce2~1~{GOTO}ce3~1~/Ccd5..cf5~cd6..cf21~
{GOTO}ce2~1~{GOTO}ce3~1~/RVci1~ci3~
STG
YEN /RVci25~ci27~/RVch26~ch28~{GOTO}cd29~0~{GOTO}ce29~1~{GOTO}cf29~1~/Ccd29..cf29~cd30..cf35~
EUR
YEN /RVdg1~dg3~/RVdf2~df4~{GOTO}db5~0~{GOTO}dc5~1~{GOTO}dd5~1~/Cdb5..dd5~db6..dd10~
STG/DEM
1 /RVdg13~dg15~/RVdf14~df16~{GOTO}db17~0~{GOTO}dc17~1~{GOTO}dd17~1~/Cdb17..dd17~db18..dd22~
EUR
STG /RVdg25~dg27~/RVdf26~df28~{GOTO}db29~0~{GOTO}dc29~1~{GOTO}dd29~1~/Cdb29..dd29~db30..dd35~
DEM/SF1 /RVdo1~do3~/RVdn2~dn4~{GOTO}dj5~0~{GOTO}dk5~1~{GOTO}dl5~1~/Cdj5..dl5~dj6..dl10~
EUR2 {IF
ah47<>0}/RVam43~am45~/RVal44~al46~{GOTO}ah47~0~{GOTO}ai47~1~{GOTO}aj47~1~/Cah47..aj47~ah48..aj76~
/RVam43~am45~
YEN2 {IF
ap47<>0}/RVau43~au45~/RVat44~at46~{GOTO}ap47~0~{GOTO}aq47~100~{GOTO}ar47~1~/Cap47..ar47~ap48..ar76~
/RVau43~au45~
SFR2 {IF
ax47<>0}/RVbc43~bc45~/RVbb44~bb46~{GOTO}ax47~0~{GOTO}ay47~1~{GOTO}az47~1~/Cax47..az47~ax48..az76~
/RVbc43~bc45~
GBP2 {IF
bf47<>0}/RVbk43~bk45~/RVbj44~bj46~{GOTO}bf47~0~{GOTO}bg47~1~{GOTO}bh47~1~/Cbf47..bh47~bf48..bh76~
/RVbk43~bk45~
CAD2 {IF
bn47<>0}/RVbs43~bs45~/RVbr44~br46~{GOTO}bn47~0~{GOTO}bo47~1~{GOTO}bp47~1~/Cbn47..bp47~bn48..bp76~
/RVbs43~bs45~
AUD2 {IF
bv47<>0}/RVca43~ca45~/RVbz44~bz46~{GOTO}bv47~0~{GOTO}bw47~1~{GOTO}bx47~1~/Cbv47..bx47~bv48..bx62~
/RVca43~ca45~
NZD2 {IF
bv70<>0}/RVca66~ca68~/RVbz67~bz69~{GOTO}bv70~0~{GOTO}bw70~1~{GOTO}bx70~1~/Cbv70..bx70~bv71..bx77~
/RVca66~ca68~
DEM2 /RVci43~ci45~/RVch44~ch46~{GOTO}cd47~0~{GOTO}ce47~1~{GOTO}cf47~1~/Ccd47..cf47~cd48..cf63~
/RVci43~ci45~
GOLD {IF
cl5<>0}/RVcq1~cq3~/RVcp2~cp4~{GOTO}cl5~0~{GOTO}cm5~100~{GOTO}cn5~100~{GOTO}cm2~100~{GOTO}cm3~100~/Ccl5..cn5~cl6..cn19~
{GOTO}cm2~100~{GOTO}cm3~100~/RVcq1~cq3~
SILV 1 {IF
ct5<>0}/RVcy1~cy3~/RVcx2~cx4~{GOTO}ct5~0~{GOTO}cu5~1~{GOTO}cv5~1~{GOTO}cu2~1~{GOTO}cu3~1~/Cct5..cv5~ct6..cv20~
{GOTO}cu2~1~{GOTO}cu3~1~/RVcy1~cy3~
PLAT {IF
cl27<>0}/RVcq23~cq25~/RVcp24~cp26~{GOTO}cl27~0~{GOTO}cm27~100~{GOTO}cn27~100~{GOTO}cm24~100~{GOTO}cm25~100~/Ccl27..cn27~cl28..cn34~
{GOTO}cm24~100~{GOTO}cm25~100~/RVcq23~cq25~
PALL {IF
ct27<>0}/RVcy23~cy25~/RVcx24~cx26~{GOTO}ct27~0~{GOTO}cu27~100~{GOTO}cv27~100~{GOTO}cu24~100~{GOTO}cu25~100~/Cct27..cv27~ct28..cv34~
{GOTO}cu24~100~{GOTO}cu25~100~/RVcy23~cy25~
EUR
YEN /rvdg43~dg45~/RVdf44~df46~{GOTO}db47~0~{GOTO}dc47~1~{GOTO}dd47~1~/Cdb47..dd47~db48..dd52~
STG/DEM
2 /rvdg55~dg57~/RVdf56~df58~{GOTO}db59~0~{GOTO}dc59~1~{GOTO}dd59~1~/Cdb59..dd59~db60..dd64~
DEM/ITL
2 /rvdg67~dg69~/RVdf68~df70~{GOTO}db71~0~{GOTO}dc71~1~{GOTO}dd71~1~/Cdb71..dd71~db72..dd77~
DEM/FRF
2 /RVci67~ci69~/RVCH68~CH70~{GOTO}cd71~0~{GOTO}ce71~1~{GOTO}cf71~1~/Ccd71..cf71~cd72..cf77~
DEM/SF
2 /RVdo13~do15~/RVdn14~dn16~{GOTO}dj17~0~{GOTO}dk17~1~{GOTO}dl17~1~/Cdj17..dl17~dj18..dl22~
EUR2 {IF
ds47<>0}/RVdx43~dx45~/RVdw44~dw46~{GOTO}ds47~0~{GOTO}dt47~1~{GOTO}du47~1~/Cds47..du47~ds48.dux76~
/RVdx43~dx45~
SILV
2 /RVcy43~cy45~/RVcx44~cx46~{GOTO}ct47~0~{GOTO}cu47~100~{GOTO}cv47~100~{GOTO}cu44~1~{GOTO}cu45~1~/Cct47..cv47~ct48..cv76~
GOLD
2 /RVcq43~cq45~/RVcp44~cp46~{GOTO}cl47~0~{GOTO}cm47~100~{GOTO}cn47~100~/Ccl47..cn47~cl48..cn63~
PLAT
2 /RVcq67~cq69~/RVcp68~cp70~{GOTO}cl71~0~{GOTO}cm71~100~{GOTO}cn71~100~{GOTO}cm68~100~{GOTO}cm69~100~/Ccl71..cn71~cl72..cn78~
/REaa9..af11~
/REag14..ag15~
/REaB16..aB22~
EUR {IF
DS5<>0}/RVDX1~DX3~/RVdw2~dw4~{GOTO}ds5~0~{GOTO}dt5~1~{GOTO}du5~1~{GOTO}dt2~1~{GOTO}dt3~1~/Cds5..du5~ds6..du30~
{GOTO}dt2~1~{GOTO}dt3~1~/RVdx1~dx3~
/WGPE
{GOTO}DEM~
 
H

Harlan Grove

I need help here big time....I have a macro that is in a Excel 97
spreadsheet that was written in Lotus. The macro is triggered by
hitting ctrl-c. You can see the problem this is causing now trying to
run this macro in Excel 2003. My question is how do I change the key
command that runs this macro? Below is the syntax....Thanks...
Click to expand...
....

The /WGPD unprotects the active worksheet, and the /WGPE reprotects it, both
without passwords. The VBA equivalents would be

ActiveSheet.Unprotect

and

ActiveSheet.Protect

All these operations appear to take place in the same worksheet, the active
worksheet, so your VBA macro should bracket all other statements inside a
With block as follows.

With ActiveSheet
.Unprotect
'other statements
.Protect
End With

The rest of my VBA suggestions assume the statements are entered within such
a With block.

The /RVag21~Ag6~ and similar statements copy the VALUES in the first range,
in this case AG21, to the second range, in this case AG6. Formats are also
copied with /RV, but assuming you only need values copied, the VBA
equivalent would be

..Range("AG6").Value = .Range("AG21").Value

The {IF ah5<>0} terms introduce conditional statements in 123 macros, and
whatever else follows on the same line is run if the condition in the {IF}
expression is true.

Terms like {GOTO}ah5~0~ move the cell pointer to a given cell, in this case
AH5, and enter the following value, in this case 0, into the cell.

Terms like /Cah5..aj5~ah6..aj34~ copy the first range, in this case AH5:AJ5,
to the second range, in this case AH6:AJ34.

So the statement

EUR {IF ah5<>0}/RVam1~am3~/RVal2~al4~{GOTO}ah5~0~ <continued>
{GOTO}ai5~1~{GOTO}aj5~1~{GOTO}ai2~1~{GOTO}ai3~1~/Cah5..aj5~ah6..aj34~

would have VBA equivalent

If .Range("AH5").Value <> 0 Then
.Range("AM3").Value = .Range("AM1").Value
.Range("AL4").Value = ActiveSheet.Range("AL2").Value
.Range("AH5").Value = 0
.Range("AI5").Value = 1
.Range("AJ5").Value = 1
.Range("AI2").Value = 1
.Range("AI3").Value = 1
.Range("AH5:AJ5").Copy Destination:=.Range("AH6:AJ34")
End If

I'm assuming the terms like

EUR

at the beginning of lines are actually range names in a separate column from
macro statements in the adjacent column to the right. However, the following
lines are a mystery.

STG
EUR
STG/DEM
1 /RVdg13~dg15~/RVdf14~df16~{GOTO}db17~0~ <continued>
{GOTO}dc17~1~{GOTO}dd17~1~/Cdb17..dd17~db18..dd22~
EUR
EUR
SILV
2 /RVcy43~cy45~/RVcx44~cx46~{GOTO}ct47~0~{GOTO}cu47~100~ <continued>
{GOTO}cv47~100~{GOTO}cu44~1~{GOTO}cu45~1~/Cct47..cv47~ct48..cv76~
GOLD
2 /RVcq43~cq45~/RVcp44~cp46~{GOTO}cl47~0~{GOTO}cm47~100~ <continued>
{GOTO}cn47~100~/Ccl47..cn47~cl48..cn63~
PLAT
2 /RVcq67~cq69~/RVcp68~cp70~{GOTO}cl71~0~{GOTO}cm71~100~ <continued>
{GOTO}cn71~100~{GOTO}cm68~100~{GOTO}cm69~100~/Ccl71..cn71~cl72..cn78~

Are 'STG', 'EUR', 'STG/DEM', 'EUR', 'EUR', 'SILV', 'GOLD', 'PLAT' on lines
by themselves? If so, if they're in the column to the left of the macro
statements in the other lines with the column of macro statements blank,
then these should cause the macro to stop. Presumably they don't, but
they're not valid macro statements either, so it's unlcear where they fit in
with the other lines.

The 'STG/DEM' line might be a 123R2 range name (Lotus made the mistake of
allowing any character to be used in range names in R2). If so, same comment
as above. If not, it's unclear what it is, so you need to be more careful in
placing it in context.

Then there are the lines that begin with '2 '. Are the 2s in the column to
the left of the macro statements? If so, then the remainder of those lines
is like other lines, and I've already dealt with how to translate the
statements in them. If not, they should be syntax errors.

Finally, statements like /REaa9..af11~ erase the contents of the given
range, in this case AA9:AF11. The VBA equivalent would be

..Range("AA9:AF11").ClearContents
 
Top