使用VBA将一定范围的列从文本转换为数字格式 [英] Converting a certain range of columns from text to number format with VBA

问题描述

我每周都会收到一个电子表格，由于种种原因，超出了我的控制范围，一些列作为文本存储为数字而不是数字.我需要将它们转换为数字，以便以后在代码中发生的事情.

I get sent a spreadsheet weekly, and for various reasons beyond my control some columns come out as text stored as numbers rather than numbers. I need to convert them to numbers for stuff that happens with them later in the code.

我正在通过以下方式将它们转换为数字:

I am converting them to numbers at the moment by doing this:

Dim rng As Range

For Each rng In Range("A:D").Columns
   rng.TextToColumns
Next rng

是否有更好(即更有效)的方法?

Is there a better (i.e. more efficient) way of doing this?

我玩过NumberFormat，但似乎没有用.

I played around with NumberFormat and it didn't seem to work.

在此先感谢您(如果我已经错过了解决方案，我们深表歉意.我进行了搜索但没有找到它.)

Thanks in advance (and apologies if I have missed a solution already here - I did a search and didn't find it).

推荐答案

Excel :

• 复制一个空单元格
• 选择您需要更改格式的范围
• 选择PasteSpecial，然后在Operation下选择Add

• Copy an empty cell
• Select the range in which you need to change the format
• Select PasteSpecial, and under Operation select Add

VBA

(更改Sheet1因此，假设A100000为空):

(change Sheet1 Accordingly, assuming that A100000 is empty):

Sheet1.Range("A100000").Copy
Sheet1.UsedRange.PasteSpecial , xlPasteSpecialOperationAdd

如果将以上内容放入Workbook_Open事件，则每次打开工作簿时它将自动执行转换.

If you place the above into the Workbook_Open event, it will perform the conversion automatically every time you open the workbook.

使用此方法，可以保留公式.

With this method, formulas are preserved.

我希望能对您有所帮助！

I hope that helps!

这篇关于使用VBA将一定范围的列从文本转换为数字格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！