Page 1 of 1

[Solved] Can't run the macro

PostPosted: Tue Nov 05, 2019 9:00 am
by beyfendi
The macro I wrote below didn't work, it gave an error, I can't find where I'm giving errors or I'm making a mistake, can you please help me. thank you.


Code: Select all   Expand viewCollapse view
Sub Frmlkpyl()
ActiveSheet.Unprotect Password:="8453"
Range("G1").Select
Selection.FormulaR1C1 = "=ADDRESS(7,MATCH(""ÜCRETLİ GÜN TOP."",R[5],0)-1)"
Range("G2").Select
Selection.FormulaR1C1 = "=ADDRESS(101,MATCH(""ÜCRETLİ GÜN TOP."",R[4],0)-1)"
Range("A7:" & [G1].Value).Select
Selection.Copy
Selection.AutoFill Destination:=Range("A7:" & [G2].Value)
Range("G1:G2").Value = ""
Range("b6").Select
ActiveSheet.Protect Password:="8453"
End Sub

Re: PLEASE HELP I CAN'T RUN THE MACRO

PostPosted: Tue Nov 05, 2019 10:56 am
by RoryOF
What error message do you get, and whereabouts in the code does it occur?

Re: PLEASE HELP I CAN'T RUN THE MACRO

PostPosted: Tue Nov 05, 2019 11:09 am
by Zizi64
_______________________
OpenOffice 3.2


Your code seems as an MS VBA macro. The OpenOffice 3.2 is a very old and obsolete version.

The OpenOffice and the newer AOO are not compatible with MS VBA macros. You must rewrite them based on the StarBasic and the AOO API. (API: Application Programming Interface)

The LibreOffice can run some MS VBA macros by usage the compatibility option switch:
Code: Select all   Expand viewCollapse view
Option VBASupport 1

in the first line of the code.
But LO has not 100% compatibility with the VBA macros, therefore it always better to use the native tools for controlling the office suite:
the supported programming languages and the API.

Re: PLEASE HELP I CAN'T RUN THE MACRO

PostPosted: Tue Nov 05, 2019 12:01 pm
by JeJe
Putting Option VBASupport 1 at the top as suggested and running on a blank document:

In my OpenOffice it runs with no error - this will be because it skips lines it knows are VBA and can't run

In LibreOffice the first error is given on this line:

Range("A7:" & [G1].Value).Select

Its generally better to use OOBasic not VBA... a macro recorder is available to help you, though learning the API is better.

Edit: corrected mistake about Openoffice not running Range("G1:G2").Value = "", it does.

Re: Can't run the macro

PostPosted: Tue Nov 05, 2019 2:20 pm
by Lupp
As already mentioned you need to set Option VBAsupport 1 as the first line (except REM) of the module you put the macro in.

Your formulas use the comma as the parameter ("Function") separator (on the Calc level). Running any AOO you need to replace them by semicolons.
[[In LibreOffice you can set the alternative character under Options (Not recommended. Stick to the semicolon.)]]
Otherwise the formulas you write into the helper range G1:G2 will not be parsed correctly.

The mentioned formulas also contain all-column-range addressing which is not supported in AOO, and only in rather recent LibO.

[.[The macro is very strange. Do you actually need it? Why don't you rewrite it to AOO/LibO Basic and API?]]
(AOO V 4.1.5 was not able to convert RC-addressing.)

The relative RC-style row addresses ("R[5]", "R[4]" respectively) should anywaybe replaced by their absolute equivalents ("R6" in both cases). After all the cells they have to work in are identified by their names. No need to reference relative!

Re: Can't run the macro

PostPosted: Tue Nov 05, 2019 4:02 pm
by Lupp
Are you ("beyfendi" Why did you omit the middle "e"?) aware of the fact that a second run of your Sub (if assumed it's rectified) would NOT erase the additionally filled columns to the right if the keyword was moved to the left since the first run? Is this actually what you want?

Re: Can't run the macro

PostPosted: Fri Nov 08, 2019 4:40 pm
by beyfendi
first of all thank you for your help and the version I am using office 4.1.6

What I want to do is have the formulas in the cells between A7 and H7 and I want to select A7 to H7 downward and make H100 formula refresh.

Re: Can't run the macro

PostPosted: Fri Nov 08, 2019 5:25 pm
by Zizi64
first of all thank you for your help and the version I am using office 4.1.6


Please update your signature in this forum:

User Control Panel - Profile TAB - Edit signature subTAB

Re: Can't run the macro

PostPosted: Fri Nov 08, 2019 6:35 pm
by Lupp
For what the MATCH() calls if you already know you want to include columns up to H (A7:H7)?

Well. I don't know anything about Excel-VBA, and the code you posted surely was recorded without much of considerations.

Insisting on doing such a strange thing by "macro" in AOO, and the question from the first line above aside, you may use the following code:
Code: Select all   Expand viewCollapse view
Sub sameInAoo_BasicAndApi()      REM Avoiding strange detours
doc      = ThisComponent
currC    = doc.CurrentController
sheet    = currC.ActiveSheet     REM Doubtable! Why not get the sheet by its name?
ultC     = 1023                  REM Column index 0-based
endR     = 100                   REM Row index 0-based
lookupRg = sheet.GetCellRangeByPosition(0, 5, ultC, 5)
           REM That's A6:AMJ6 (aka 6:6 in full-row-syntax).
lookupDA = lookupRg.getDataArray
findStr  = "ÜCRETLİ GÜN TOP."
For c=0 To ultC                  REM This works instead of the doubled MATCH calls
                                 REM in the original Sub without formula hokum.
  If lookupDA(0)(c)=findStr Then Exit For
Next c
If c>ultC Then
  Print "No match!"                REM Find a better Exit!
  Exit Sub
End If
rgToFill = sheet.getCellRangeByPosition(0, 6, c, endR)
contCell = sheet.getCellRangeByName("B6")
sheet.Unprotect("8453")
rgToFill.fillAuto(0, 1) REM Assuming that's what MS Excel AutoFill does. You may need
                        REM rgToFill.fillSeries(0, 0, 0, 0, 0) (constant series).
currC.select(contCell)
sheet.protect("8453")
End Sub
It looks longer, but is basically clearer, and it avoids the intricate usage of a Calc formula.

Re: Can't run the macro

PostPosted: Mon Nov 11, 2019 8:02 am
by beyfendi
Lupp wrote:For what the MATCH() calls if you already know you want to include columns up to H (A7:H7)?

Well. I don't know anything about Excel-VBA, and the code you posted surely was recorded without much of considerations.

Insisting on doing such a strange thing by "macro" in AOO, and the question from the first line above aside, you may use the following code:
Code: Select all   Expand viewCollapse view
Sub sameInAoo_BasicAndApi()      REM Avoiding strange detours
doc      = ThisComponent
currC    = doc.CurrentController
sheet    = currC.ActiveSheet     REM Doubtable! Why not get the sheet by its name?
ultC     = 1023                  REM Column index 0-based
endR     = 100                   REM Row index 0-based
lookupRg = sheet.GetCellRangeByPosition(0, 5, ultC, 5)
           REM That's A6:AMJ6 (aka 6:6 in full-row-syntax).
lookupDA = lookupRg.getDataArray
findStr  = "ÜCRETLİ GÜN TOP."
For c=0 To ultC                  REM This works instead of the doubled MATCH calls
                                 REM in the original Sub without formula hokum.
  If lookupDA(0)(c)=findStr Then Exit For
Next c
If c>ultC Then
  Print "No match!"                REM Find a better Exit!
  Exit Sub
End If
rgToFill = sheet.getCellRangeByPosition(0, 6, c, endR)
contCell = sheet.getCellRangeByName("B6")
sheet.Unprotect("8453")
rgToFill.fillAuto(0, 1) REM Assuming that's what MS Excel AutoFill does. You may need
                        REM rgToFill.fillSeries(0, 0, 0, 0, 0) (constant series).
currC.select(contCell)
sheet.protect("8453")
End Sub
It looks longer, but is basically clearer, and it avoids the intricate usage of a Calc formula.



was, very very thank you.