Consulting

Results 1 to 6 of 6

Thread: Offset value as ".Text" use

  1. #1
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,232
    Location

    Offset value as ".Text" use

    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?
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  2. #2
    VBAX Mentor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    372
    Location
    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
    How to attach file: Reading and Posting Messages (vbaexpress.com), click Go Advanced below post edit window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,232
    Location
    I guess it defines the string as text.... but like normal I could be wrong.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  4. #4
    snb
    Guest
    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

  5. #5
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,785
    Location
    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
    Last edited by Paul_Hossler; 03-23-2024 at 01:24 PM.
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  6. #6
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,232
    Location
    Thank you Paul.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •