View Full Version : [SOLVED:] Unable to Run Macro for Excel Sheet to Format
LucianoPena2
04-12-2022, 03:35 PM
I'm working on a spreadsheet for a project I'm working on but I'm having issues debugging my Macro.
Call me rusty but I'm not sure as to why the syntax of my Macro won't run. I'm running Microsoft Office 2019 Professional Plus 64-Bit.
The cells that are empty should be replaced and all data should be combined but it's not working.
Can anyone assist me with debugging my Macro for Office 2019 Pro Plus?
My code is
Selection.Replace What:="artwork", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
File attached below.
29633
p45cal
04-12-2022, 04:20 PM
A bit of a guess; try removing the
, FormulaVersion:=xlReplaceFormula2
LucianoPena2
04-12-2022, 07:05 PM
Still didn't work. Did it work for you PMp45cal
A bit of a guess; try removing the
, FormulaVersion:=xlReplaceFormula2
:banghead:
Paul_Hossler
04-12-2022, 07:26 PM
"FormulaVersion:=xlReplaceFormula2" is not documented at all
Edit --
Going by ...
https://docs.microsoft.com/en-us/office/vba/excel/concepts/cells-and-ranges/range-formula-vs-formula2
I'm pretty sure that it has to do with whether the formula is a Dynamic Array type formula or the original style
DA are only available in later versions
MS did the same thing with .Sort when they came up with the Add2 := ... parameter. Older versions of Excel (which don't have DAs) just need the Add := and I have to remember to edit it
-------------------------------------------------------------------------------------------------------------------------
Couple suggestions:
Option Explicit
Sub Reformat_test_2_PHH()
Dim r As Range
Application.ScreenUpdating = False
With ActiveSheet
Set r = Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp))
End With
r.Columns(1).Replace What:="artwork", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
On Error Resume Next
r.Columns(1).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[1]C"
r.Columns(1).Value = r.Columns(1).Value
r.Columns(2).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
Application.ScreenUpdating = True
End Sub
LucianoPena2
04-12-2022, 08:10 PM
Thank you guys so much for taking the time to look into this for me.
I will try your suggestion Paul
Paul, with the new code you have there on your suggestion, were you able to see the document format change on your end?
I'm not sure why this Macro runs well on my PC that has Microsoft Office 365 and not on my other PC with Microsoft Office 2019. I really don't want to give Microsoft more money just for a syntax error. :banghead:
I want to also thank p45cal for looking into this for me.:bow:
I wish Microsoft would do some sort of back compatibility between Office software when it comes to Macro code. :crying::banghead:
"FormulaVersion:=xlReplaceFormula2" is not documented at all
Only thing I could find is a user comment:
Doubt if you need it, even if the macro recorder thinks you do
Couple suggestions:
Option Explicit
Sub Reformat_test_2_PHH()
Dim r As Range
Application.ScreenUpdating = False
With ActiveSheet
Set r = Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp))
End With
r.Columns(1).Replace What:="artwork", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
On Error Resume Next
r.Columns(1).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[1]C"
r.Columns(1).Value = r.Columns(1).Value
r.Columns(2).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
Application.ScreenUpdating = True
End Sub
How else can I show you guys appreciation?
Does anyone know which versions of Excel are Pre and Post DA versions?
Aussiebear
04-12-2022, 10:47 PM
Microsoft run a marketing ploy where the better resourced program is Office 365 rather than the stand alone version such as Office 2019. I'm assuming its because they foresee a better revenue return by being able to charge a yearly fee.
p45cal
04-12-2022, 11:42 PM
Still didn't work. Did it work for you PMp45calIt did work here but I'm using O365.
Does it stop with an error?
If you choose Debug what line is highlighted?
If push comes to shove we could do a brief TeamViewer session.
Later on today I'll fire up an old laptop with Excel 2003 on and see what happens
p45cal
04-13-2022, 01:29 AM
I tried both Reformat1 and Reformat_test_2 on an Excel 2003 machine, and they both errored on that line, but taking out the , FormulaVersion:=xlReplaceFormula2 allowed both to work properly.
Just sufficient:
Sub M_snb()
Sheet1.Columns(1).Replace "artwork", "", 2
Sheet1.Range("A2:A8000").Cut Sheet1.Cells(1)
Sheet1.Columns(1).SpecialCells(4).EntireRow.Delete
End Sub
together with:
Sub Timeformatfix()
Sheet2.Columns(5).TextToColumns , , , 0, 0, 0, 0, 0, 0, , Array(1, 1)
End Sub
Always clean macrorecorder code before using it.
Paul_Hossler
04-13-2022, 07:41 AM
I tried both Reformat1 and Reformat_test_2 on an Excel 2003 machine, and they both errored on that line, but taking out the , FormulaVersion:=xlReplaceFormula2 allowed both to work properly.
It's xlReplaceFormula without the 'one' and was added in 2019. Again, unless you're using Dynamic Array formulas, you can just leave out the FormulaVersion parameter
https://bettersolutions.com/excel/macros/enumerations-xlformulaversion.htm
xlFormulaVersion
Specifies how you want formulas replaced when using Range.Replace.
xlReplaceFormula
(0)
xlReplaceFormula2
(1)
REMARKS
* Added in 2019.
https://bettersolutions.com/excel/macros/enumerations.htm
Added in 2019
xlCategorySortOrder (https://bettersolutions.com/excel/macros/enumerations-xlcategorysortorder.htm)
Specifies the sort order for the category.
xlFormulaVersion (https://bettersolutions.com/excel/macros/enumerations-xlformulaversion.htm)
Specifies how you want formulas replaced when using Range.Replace.
xlGeoMappingLevel (https://bettersolutions.com/excel/macros/enumerations-xlgeomappinglevel.htm)
Specifies the geography mapping level for a series within a chart group.
xlGeoProjectionType (https://bettersolutions.com/excel/macros/enumerations-xlgeoprojectiontype.htm)
Specifies the geography projection type for a series within the chart group.
xlLinkedDataTypeState (https://bettersolutions.com/excel/macros/enumerations-xllinkeddatatypestate.htm)
Specifies the state of cells that may contain Linked data types such as Stocks or Geography.
xlRegionLabelOptions (https://bettersolutions.com/excel/macros/enumerations-xlregionlabeloptions.htm)
Specifies the region labelling behavior for a series within the chart group.
xlValueSortOrder (https://bettersolutions.com/excel/macros/enumerations-xlvaluesortorder.htm)
Specifies the sort order for the values.
LucianoPena2
04-13-2022, 09:35 AM
Microsoft run a marketing ploy where the better resourced program is Office 365 rather than the stand alone version such as Office 2019. I'm assuming its because they foresee a better revenue return by being able to charge a yearly fee.
Yeah, that's sounds like Microsoft alright. MO365 license already costs a ton of money since it's billed annually, it's a never-ending fee. :crying:
LucianoPena2
04-13-2022, 09:37 AM
Just sufficient:
Sub M_snb()
Sheet1.Columns(1).Replace "artwork", "", 2
Sheet1.Range("A2:A8000").Cut Sheet1.Cells(1)
Sheet1.Columns(1).SpecialCells(4).EntireRow.Delete
End Sub
together with:
Sub Timeformatfix()
Sheet2.Columns(5).TextToColumns , , , 0, 0, 0, 0, 0, 0, , Array(1, 1)
End Sub
Always clean macrorecorder code before using it. snd I ran this code, gave me an error too.
It's xlReplaceFormula without the 'one' and was added in 2019. Again, unless you're using Dynamic Array formulas, you can just leave out the FormulaVersion parameter
Let me give this a shot. Paul thank you so much!!
You guys have been a tremendous help! Thank you Paul_Hossler, snb, p45cal, and Aussiebear for taking the time to answer my thread. :bow::bow: Wherever you guys are, I hope you guys are having an amazing day
Paul_Hossler
04-13-2022, 09:42 AM
Thank you guys so much for taking the time to look into this for me.
Paul, with the new code you have there on your suggestion, were you able to see the document format change on your end?
I'm not sure why this Macro runs well on my PC that has Microsoft Office 365 and not on my other PC with Microsoft Office 2019. I really don't want to give Microsoft more money just for a syntax error. :banghead:
29636
I have 365 / 32 bits on my PC
Everything seemed to run OK and the format changed
I'm pretty sure that dynamic arrays are available on 365, but I don't think they were available on 2019
If you're interested, I've attached a workbook that I made to see how the dynamic functions work
Actually, even if you're not interested, I still attached it :rofl:
LucianoPena2
04-13-2022, 09:48 AM
29636
I have 365 / 32 bits on my PC
Everything seemed to run OK and the format changed
I'm pretty sure that dynamic arrays are available on 365, but I don't think they were available on 2019
If you're interested, I've attached a workbook that I made to see how the dynamic functions work
Actually, even if you're not interested, I still attached it :rofl:
Oh I'm interested alright! :friends:
In Microsoft Office 365 it runs, which is where I was first confused. The function xlReplaceFormula2 is rather tricky to replace for stand alone application M. Office 2019, which wasn't makes no sense. You, or any user would expect to have every document work seamlessly on every Microsoft Office software regardless.
LucianoPena2
04-13-2022, 09:59 AM
Paul
I owe you big time. You have made me see the light at the end of the tunnel. Calling this resolved. Followed through all of your suggestions and it worked!
Man! Wherever you are, you're my hero! :bow::bow:
Let's keep in touch if you'd like. Thanks once again!
DaveM
04-18-2022, 11:16 AM
...... it's a never-ending fee. :crying:
That's u$oft's business model; continually remove useful functions and features, add more call-home functions and charge you more. That started in 1994 when they began forcing regular upgrades of Office for all their business customers. I didn't like it than, I hate it now.
Ever notice how the colorful, visually interesting UI has become duller with each new upgrade? Laziness and greed, it seems.
Dave
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.