多个单元格的文字功能 [英] Text Function for multiple cells

问题描述

我想知道是否存在在多个单元格上使用文本函数的替代方法(而不是循环).

I wanted to know if there is an alternative method (than loops) for using a text function on multiple cells.

例如，以下代码可正确用于查找

For example, the below code work correctly for lookups

Range("c2:c6").Value = Application.WorksheetFunction.VLookup(Range("a2:a6"), Range("a2:b6"), 2, 0)

但是我在下面的文本函数中遇到了错误

However I get an error with the below for text functions

Range("c2:c6").Value = Application.WorksheetFunction.Text(Range("A2:A6"), "000000")

推荐答案

您要做的就是格式化单元格并复制值吗?

Surly all you want to do is format the cells and copy the values?

喜欢下面吗?

    With Range("C2:C6")
        .Value = Range("A2:A6").Value
        .NumberFormat = "000000"
    End With

宏记录器为我提供了与上面类似的一些优化

The macro recorder gave me similar to above with some optimisations

使用公式

Range("C2").FormulaR1C1 = "=TEXT(RC[-2],""000000"")"
Range("C3").FormulaR1C1 = "=TEXT(RC[-2],""000000"")"
Range("C4").FormulaR1C1 = "=TEXT(RC[-2],""000000"")"
Range("C5").FormulaR1C1 = "=TEXT(RC[-2],""000000"")"
Range("C6").FormulaR1C1 = "=TEXT(RC[-2],""000000"")"

或者更有活力的东西

For Each cell In Range("C2:C6")
    cell.FormulaR1C1 = "=TEXT(RC[-2],""000000"")"
Next

这篇关于多个单元格的文字功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！