Trying to change Excel Column to Text-CodePudding

I am trying to format all Excel cells starting at "A3" using the TEXT function. I am able to do one cell(see Example 1) but I am having issues trying to do whole column, I keep getting a mismatch error(see Example 2).

CODE

Dim ws As Worksheet
Set ws = Worksheets("Sheet1")

Dim lastRow As Integer
LastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row

Example 1

ws.Range("H3") = Application.WorksheetFunction.Text(ws.Range("A3"), "00000.0000")

Example 2

ws.Range("H3:H" & LastRow) = Application.WorksheetFunction.Text(ws.Range("A3:A" & LastRow), "00000.0000")

Error: Mismatch error runtime error 13

CodePudding user response：

Use Application.Text, which is the late-bound version of WorksheetFunction.Text and can accept and return an array.

Also change Integer to Long to avoid a potential Overflow error. Excel has more rows than Integer can handle.

Dim ws As Worksheet
Set ws = Worksheets("Sheet1")

Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

With ws.Range("H3:H" & lastRow)
    .NumberFormat = "@"
    .Value = Application.Text(ws.Range("A3:A" & lastRow), "00000.0000")
End With