Offset value as ".Text" use

[Aussiebear]

The following code was provided as a solution to this issue.


Ideally what I would like is to look at column D and if it is not blank "" then look at column J and take the first 5 digits and put them into column O




Sub Test()
Dim c As Range
With Sheets("Sheet1") 'amend as appropriate
For Each c In .Range("D2:D" & .Cells(Rows.Count, "D").End(xlUp).Row) 'assumed header in row 1
If Not IsEmpty(c.Value) Then
c.Offset(, 11).Value = Left$(c.Offset(, 6).Text, 5)
End If
Next c
End With
End Sub


What does the use of the .Text in line 6 provide, given that all we are seeking is the first 5 characters of the cell value?

[June7]

I've never seen .Text used in Excel VBA code. Does this help with understanding https://learn.microsoft.com/en-us/office/vba/api/Excel.Range.Text

[Aussiebear]

I guess it defines the string as text.... but like normal I could be wrong.

[snb]

Avoid sloppy code:
Avoid any redundant interaction with the worksheet (once for reading & once for writing suffices)

.Value = Value
.Text = Formatted Value


Sub M_snb()
sn= Sheet1.cells(1).currentregion.resize(,15).offset(1) ' reading values

for j= 1 to ubound(sn) ' processing values in the array
sn(j,1) = sn(j,15)
if sn(j,4)<>"" then sn(j,1)=left(sn(j,10),5)
next

sheet1.cells(2,15).resize(ubound(sn))=sn ' writing values
End Sub

[Paul_Hossler]

Ideally what I would like is to look at column D and if it is not blank "" then look at column J and take the first 5 digits and put them into column O

I took this to mean that if the cell is a Double with a lot of digits only grab the first 5, i.e. 12345678.99 gives "12345"

.Text really means (to me) 'As Displayed', i.e. Number formatting, overflow (#######), etc.

So values displayed like this in the worksheet ...

31436


... has these results


31437


Numbers (except Date) all have 12341234.56 as a .Value, but .Text picks up the display

[Aussiebear]

Thank you Paul.