VBA：Conver Text - >数乘以1 [英] VBA: Conver Text -> Number multiplying by 1

问题描述

我有一个链接到SQL数据库的表，用于创建一些报告。问题是数字来自文本，我必须将它们每次转换成数字，
我已经写了一个宏来创建报告，但是我无法转换数字。我试图将整个表乘以一个（该表丢失了链接，但这不是一个问题，因为我只是将信息复制到另一个工作簿，并且它们关闭表w / o保存），并且它工作时，我手动，但是当我通过宏做它不会工作。我可以看到它乘以1，但数字仍然是文本。

I have a table linked to a SQL database that I use to create some reports. The problem is that the numbers come as text and I have to convert them to numbers everytime, I've wrote a macro to create the report but I'm having trouble to convert the numbers. I tried to multiply the whole table by one (the table lose the link but it's not an issue, since I'll just copy the infos to another workbook and them close the table w/o saving) and it worked when I did it manualy, but when I do it via macro it won't work. I can see it multiplying by 1 but the numbers are still texts.

由于我的表是巨大的，尝试转换每个单元格不是一个选项sice它将永远运行每个单元格并使用CStr函数。

Since my table is huge, trying to convert each cell is not an options sice it'd take forever to run each cell and use the CStr function.

我的代码：

Range("B2").Copy
Range("A4", Range("AC4").End(xlDown)).PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiply, _
    SkipBlanks:=False, Transpose:=False

任何建议？

推荐答案

xlDown 可能非常不可预测。不要使用实际上找到最后一行，然后创建你的范围，然后使用它来进行操作。

xlDown can be highly unpredictive. Don't use that. In fact find the last row and then create your range and then use that for your operations.

只是改变格式不会将它们转换成数字。您必须复制 F2-Enter 行为。

Also just changing the format will not convert those to number. You will have to replicate the F2-Enter behavior.

尝试这个（ TRIED AND TESTED ）

Try this (TRIED AND TESTED)

Sub Sample()
    Dim ws As Worksheet
    Dim Rng As Range, acell As Range
    Dim lRow As Long

    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        lRow = .Range("AC" & .Rows.Count).End(xlUp).Row

        Set Rng = .Range("A4:AC" & lRow)

        .Range("B2").Copy

        Rng.PasteSpecial Paste:=xlPasteValues, _
        Operation:=xlMultiply, SkipBlanks:=False, Transpose:=False

        Rng.NumberFormat = "0" ' OR "0.00" as applicable

        For Each acell In Rng
            acell.Formula = acell.Value
        Next
    End With
End Sub

SCREENSHOT

这篇关于VBA：Conver Text - >数乘以1的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！