Unable to Run Macro for Excel Sheet to Format

[LucianoPena2]

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]

A bit of a guess; try removing the
, FormulaVersion:=xlReplaceFormula2

[LucianoPena2]

Still didn't work. Did it work for you PMp45cal


A bit of a guess; try removing the
, FormulaVersion:=xlReplaceFormula2

:banghead:

[Paul_Hossler]

"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]

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]

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]

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]

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.

[snb]

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]

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]

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]

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]

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]

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]

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]

...... 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