CODEWITHSUNDEEP
excelvba
hpca01
hpca01

Reputation: 378

Error for using cells() for string in vba?

So I am trying to use the Cells object to store a string and I keep getting an application error:

Sub analyze()
Dim rC As Integer
Dim rtData As Worksheet
Set rtData = ThisWorkbook.Sheets("RTN Data")
Dim finalSht As Worksheet
Set finalSht = ThisWorkbook.Sheets("Final")
Sheets("Final").Cells(1, 1).Text = "order#"
finalSht.Cells(0, 2) = "Nurse"
finalSht.Cells(0, 3) = "Message"

rC = rtData.Cells(rows.Count, 1).End(xlUp).Row



End Sub

Regardless of the different ways i keep trying to reference it

Please help.

Upvotes: 2

Views: 303

Answers (2)

user4039065
user4039065

Reputation:

The Range.Text property is the displayed text as it is formatted in a cell and you cannot assign it. Think of it as read only¹. Assign your string to the cell's Range.Value property or (since .Value) is the default) just put it into the cell.

Sheets("Final").Cells(1, 1) = "order#"
Sheets("Final").Cells(1, 1).Value = "order#"

This can easily be demonstrated with dates. A cells might have a formatted .Text property of 18-Jul-2015, a date .Value of 07/18/2015 and a raw Range .Value2 of 42203.

¹The official documentation for the .Text property is contradictory.

Returns or sets the text for the specified object. Read-only String.

Either it is read only or you can set it. Not both.

Upvotes: 0

vacip
vacip

Reputation: 5416

.text is a read only property. Try assigning your value to .value

Also, cells are counted from 1, so cells(0,2) makes no sense.

Sheets("Final").Cells(1, 1).Value = "order#"
finalSht.Cells(1, 2) = "Nurse"
finalSht.Cells(1, 3) = "Message"

Upvotes: 1

Related Questions