NumberFormat = "@" syntax



I understand that to convert a numeric Cell import source
to a destination cell String, I need to transit the cell value to a
String Variable
then, load the String Variable to the destination cell.
My Import Source Column A, has a mix of Data Type 1 and 2.
I want the Destination Cells to be in Data Type 2 only (String),
But I am still struggling with the following code :
Sub Convert()
Dim LastRow As Integer: Dim i As Integer
Dim wbA As Workbook: Dim wbB As Workbook
Dim myPath As String: Dim Temp As String
myPath = "C:\"
Set wbA = Workbooks.Open(myPath & "A.xls")
LastRow = Range("A65335").End(xlUp).Row
Set wbB = Workbooks.Open(myPath & "B.xls")
i = 2
Do While i <= LastRow
wbA.Cells(i, 1).NumberFormat = "@"
' Error on above line : Object does not support this property or
Temp = wbA.Cells(i, 1)
wbB.Cells(i, 1).Value = Temp
i = i + 1
End Sub
Help appreciated,

Chip Pearson

The Cells property belongs to a Worksheet, not a Workbook. Your code
Dim wbA As Workbook: Dim wbB As Workbook

declares both wbA and wbB as Workbooks, not Worksheets, and the Cells
method doesn't belong to Workbooks. You need to reference a worksheet
within wbA or wbB. E.g.,

wbA.Worksheets("Sheet Name").Cells.NumberFormat = "@"

Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
(email on web site)

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question