• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Problem with code running and not saving changes when saved????

Hamish

Member
I have created some code to convert code from text to numbers. I run the code without any issues however after saving and closing the file an VBA changes that were implemented by the code are not saved??? This also happens after manual deletion of rows. I delete the rows, save the workbook and close it, only to find upon re-opening that the changes have not been saved???

Code as follows;

Option Explicit

Sub ConvertToNumbers()

Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.ScreenUpdating = False

Do Until ActiveCell.Value = ""
ActiveCell.Value = CLng(ActiveCell.Value)
ActiveCell.Offset(1, 0).Select
Loop

Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.ScreenUpdating = True
ActiveWorkbook.Save


End Sub
 
Hi Hamish

Your code will need to process every cell one at a time. Why not just process all cells at once? No need for all the calculations on off etc.
Something simple like this:
Code:
Sub ConvertNum()
  [b1] = 1: [b1].Copy
    Range("A2", Range("a" & Rows.Count).End(xlUp)).PasteSpecial xlPasteAll, xlMultiply
    [b1].Clear
    ActiveWorkbook.Save
End Sub
I have tested this there is no problem with save. File attached to show workings.
Smallman
 

Attachments

  • Num.xlsm
    19.1 KB · Views: 3
Hi Smallman,

I ran the code without a hitch, however when I reopened the workbook none of the changes saved? It opened showing the changes but updated itself and they were gone?
 
Hi Hamish

The activeworkbook.save is gold it saves all changes. As I said I ran the code and it worked saving the changes perfectly.

At a guess you could be running the process in Read only mode.

What I would do is to run the code in my file above and see if it works. Remember that file is a perfect test environment. If it works there is something wrong with your other file. If it does not work then there is something wrong with Excel on your machine.

Lets be very clear, the code above is accurate. The problem for your to solve is to isolate what part does not work at your end.

Take care

Smallman
 
Hi Smallman,

I think I solved the problem. It seemed to be to do with a workbook connection I was unaware of. I broke the connection and it all worked fine.

Thanks for the more efficient code and your help!

Cheers,
Hamish
 
Hi @Hamish

Thanks for posting back. It is really nice to hear when someone gets over the line successfully. Well done and good luck with your project.

Take care

Smallman
 
Hi Hamish
Your code will need to process every cell one at a time. Why not just process all cells at once? No need for all the calculations on off etc.
Something simple like this:
Code:
Sub ConvertNum()
  [b1] = 1: [b1].Copy
    Range("A2", Range("a" & Rows.Count).End(xlUp)).PasteSpecial xlPasteAll, xlMultiply
    [b1].Clear
    ActiveWorkbook.Save
End Sub
I have tested this there is no problem with save. File attached to show workings.
Smallman

Hi Smallman,

Can you please explaint your steps of this coding.
it would be a good learning for me.

Regards,
Gaurav Kr Gautam
 
Sure

If you multiply numbers stored as text by 1 they become numbers again. This is done by using the specialcells multiply command.

You can try it yourself. Copy a cell with the number 1 in it, now highlight some other numbers formatted as text. Choose PasteSpecial and select Multiply

Muliply.JPG

Click OK. You will see your numbers instantly change.

On a side not this is a very fast way to multipy or divide numbers by 1000. It avoids looping in Code and I am all for that.

Hope that helps.

Take care

Smallman
 
Back
Top