.Text or .Value

[abenitez77]

I have a macro that runs and extracts data from the cells. I noticed that sometimes I have to use .Text and other times .Value. I started out with .Value but I had some problems with data that was being read so I had to use .Text. Is there a rule or best practice for using these 2? I am inserting the results into a SQL Server Table.

iSheetA.Offset(2, 6).Value
iSheetA.Offset(2, 6).Text

Thanks,
Alex

 

[Scott Huish]

I use .Text if I want to return the value as it is displayed in the cell.
To illustrate this, put the value 25 in cell A1, and format it as currency and run this macro:

Sub test()
MsgBox Range("A1").Value
MsgBox Range("A1").Text
End Sub

.Value would return 25
.Text will return $ 25.00

I also use .Text if it what you are feeding this value into inherently requires a string.

 

[abenitez77]

The problem I ran into is that sometimes it returned #### when I did .Text

 

[MARK858]

That means normally that your columns aren't wide enough.