Convert from lotus to excel

[Frank Situmorang]

Hi,

I are moving to excel now, and we do not want to use lotus anymore. Excel
can read lotus. wk, and we save it in excel, all the data is ok, except the
macro can not work.

The use of macro in lotus is as follows

We have a sheet which we called data sheet with the following columns:
Client JobNo Invoice,Amount Payment.. payment date
This consitsts of almost 5000 lines.

In another sheet, we have the macro by clincking the button, can filter only
certain job number, and have the total amount by colum for the selected job.

I tried to use filter in excell and subtotal fuction in order to replace the
dead macro, but it can only work true if the lines is no more than 1000
records.

So I want to use the macro also just by hitting the button.
Can anyone help me?, This is the Lotus macro script, which is not working in
excell;

Sub Click(Source As Buttoncontrol)
.MacroRunText
|/DQI{NAME}$DATA~C{NAME}$JOB~O{NAME}$DJOB~E~Q/DSRQ/DSD{NAME}$SJOB~P{NAME}$S2JOB~~S{NAME}$S1JOB~A~G|
End Sub

All idea is highly appreciated

Frank
Jakarta, Indonesia

 

[Niek Otten]

Hi Frank,

You will have to create a new macro.

Look here:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi,
|
| I are moving to excel now, and we do not want to use lotus anymore. Excel
| can read lotus. wk, and we save it in excel, all the data is ok, except the
| macro can not work.
|
| The use of macro in lotus is as follows
|
| We have a sheet which we called data sheet with the following columns:
| Client JobNo Invoice,Amount Payment.. payment date
| This consitsts of almost 5000 lines.
|
| In another sheet, we have the macro by clincking the button, can filter only
| certain job number, and have the total amount by colum for the selected job.
|
| I tried to use filter in excell and subtotal fuction in order to replace the
| dead macro, but it can only work true if the lines is no more than 1000
| records.
|
| So I want to use the macro also just by hitting the button.
| Can anyone help me?, This is the Lotus macro script, which is not working in
| excell;
|
| Sub Click(Source As Buttoncontrol)
| .MacroRunText
||/DQI{NAME}$DATA~C{NAME}$JOB~O{NAME}$DJOB~E~Q/DSRQ/DSD{NAME}$SJOB~P{NAME}$S2JOB~~S{NAME}$S1JOB~A~G|
| End Sub
|
| All idea is highly appreciated
|
| Frank
| Jakarta, Indonesia
|

 

[Jim Rech]

While it's true that Data, Filter, AutoFilter can only list 1000 unique
items in a field, you can use Data, Filter, Advanced Filter to get the
result you want. The criteria range that the Advanced Filter dialog wants
would be a two cell range like A1:A2, where A1 would have the field's
heading ('JobNo') and A2 the job code.

The Excel macro language (VBA) is completely different than 1-2-3's and
there are no macro translators. So, as Niek says, you have to rewrite your
1-2-3 macro. You might start that process by recording a macro as you use
the Advanced Filter feature. Of course time invested in learning VBA would
be well rewarded down the line.

--
Jim
| Hi,
|
| I are moving to excel now, and we do not want to use lotus anymore. Excel
| can read lotus. wk, and we save it in excel, all the data is ok, except
the
| macro can not work.
|
| The use of macro in lotus is as follows
|
| We have a sheet which we called data sheet with the following columns:
| Client JobNo Invoice,Amount Payment.. payment date
| This consitsts of almost 5000 lines.
|
| In another sheet, we have the macro by clincking the button, can filter
only
| certain job number, and have the total amount by colum for the selected
job.
|
| I tried to use filter in excell and subtotal fuction in order to replace
the
| dead macro, but it can only work true if the lines is no more than 1000
| records.
|
| So I want to use the macro also just by hitting the button.
| Can anyone help me?, This is the Lotus macro script, which is not working
in
| excell;
|
| Sub Click(Source As Buttoncontrol)
| .MacroRunText
||/DQI{NAME}$DATA~C{NAME}$JOB~O{NAME}$DJOB~E~Q/DSRQ/DSD{NAME}$SJOB~P{NAME}$S2JOB~~S{NAME}$S1JOB~A~G|
| End Sub
|
| All idea is highly appreciated
|
| Frank
| Jakarta, Indonesia
|

 

[Frank Situmorang]

Thanks to all of you Jim and Niek, I will try my best. First I w'd create a
macro by starting recording macro using advanced filter, then I wil try to
modify it on how to copy the result to another sheet. I do not know if it
will work.

Frank

 

[Frank Situmorang]

Jim,

I have written the VBA similar to the sample on website you gave. My
question is how can I assign this macro to the recatangle because I want it
to work, after we fill in the criteria and then click "OK", the letter on the
rectangle, the macro will work and showing us the result

XlFilterCopy, what is xl stands for

This is my VBA script:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 5 And Target.Column = 4 Then
'calculate criteria cell in case calculation mode is manual
Sheets("data").Range("Criteria").Calculate
Worksheets("data").Range("database") _
.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("database").Range("criteria"), _
CopyToRange:=Range("B10:v10"), Unique:=False
'calculate summary total in case calculation mode is manual
Sheets("JOB No").Range("D8").Calculate
End If
End Sub



Many Thanks,

Frank