Page 1 of 1
[Solved] Can't run the macro
Posted: 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
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
Posted: 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
Posted: 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:
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
Posted: 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
Posted: 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
Posted: 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
Posted: 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
Posted: 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
Posted: 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
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
Posted: 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
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.