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/of...cel.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 ...

Capture.JPG


... has these results


Capture2.JPG


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

[Aussiebear]

Thank you Paul.