1

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")
Community
  • 1
  • 1
Krishn
  • 813
  • 3
  • 14
  • 28
  • Could you share what error you get. Since an error could be so many of things. – Luuklag Jul 27 '15 at 12:21
  • What error do you get? Do you want to insert array formula? Because Excel function TEXT doesn't accept range as a first parameter (unless it is array formula). – mielk Jul 27 '15 at 12:22
  • Run-time error '13' Type Mismatch – Krishn Jul 27 '15 at 12:23
  • You can read more about.worksheetfunction.text here: https://msdn.microsoft.com/en-us/library/office/ff841121.aspx – Luuklag Jul 27 '15 at 12:24

4 Answers4

4

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

Like below?

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

The macro recorder gave me similar to above with some optimisations

Using formula

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

Or something a little bit more dynamic

For Each cell In Range("C2:C6")
    cell.FormulaR1C1 = "=TEXT(RC[-2],""000000"")"
Next
99moorem
  • 1,955
  • 1
  • 15
  • 27
  • Hi, I currently use formula's however my preference is to use the text function if possible – Krishn Jul 27 '15 at 12:35
  • I have amended above code. The last one of the three is probably the best one to use, as you can change the range easily. If you wanted to go further with this you could work out the last row in A and then use that for the last range row number in the loop – 99moorem Jul 27 '15 at 12:40
3

One line code

Sub Sample()
    [C2:C6] = [INDEX("'" & TEXT(A2:A6,"000000"),)]
End Sub

If you want explanation on this then see This

Community
  • 1
  • 1
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • Hi, thanks for the answer. In noticed that in both your answer and in Rory's answer you guys put an apostrophe (although in slightly different places). The code seems to yield the same results if the apostrophe isn't there. Is there a reason it was added? – tjsmith Aug 28 '16 at 16:45
  • 2
    If the cells are not formatted as text and you have long numbers then you will have to add `'` else you will see the numbers in scientific notation. :) For example in any new excel worksheet type `100000000000` in Cell A1. What do you see? `1E+11` now try `'100000000000` – Siddharth Rout Aug 29 '16 at 08:15
2

If you really want to avoid loops you can use Evaluate:

Range("c2:c6").Value = Range("c2:c6").Worksheet.Evaluate("INDEX(TEXT(A2:A6,""'000000""),)")
Rory
  • 32,730
  • 5
  • 32
  • 35
1

Alternatively:

Sub dural()
    Range("C1:C6").Formula = "=Text(A1,""000000"")"
End Sub
Gary's Student
  • 95,722
  • 10
  • 59
  • 99