Error for using cells() for string in vba?

Question

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.

vacip · Accepted Answer · 2015-07-18 09:48:46Z

1

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

answered Jul 18, 2015 at 9:48

vacip

5,4563 gold badges29 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

hpca01 Over a year ago

haha thank you very much...you solved all my problems right there, i was counting them from 0!

user4039065 Over a year ago

+ Good catch. I completely missed the zero-based cell rows.

score 0 · Accepted Answer · 2015-07-18 09:54:46Z

0

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.

edited Jul 18, 2015 at 9:54

answered Jul 18, 2015 at 9:46

user4039065

Collectives™ on Stack Overflow

Error for using cells() for string in vba?

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related