Results 1 to 4 of 4

If result contains a letter, paste to a different cell

Threaded View

  1. #1
    Registered User
    Join Date
    03-23-2019
    Location
    Vancouver, BC
    MS-Off Ver
    2016
    Posts
    2

    If result contains a letter, paste to a different cell

    Hi All. So I have written some code that will split up the characters in a cell and paste them in adjacent cells. The last characters it pastes are some numbers. Sometimes though this number will contain one letter (from A-Z) at the end it. If this is the case I would like that letter to get pasted in the next cell adjacent to where the number will end up. Any thoughts would be appreciated.

    I have the code working to do this:
    82XX-103A Will get split up 82>X>X>103A (using ">" to illustrate next adjacent cell)
    82XXX-103A Will get split up 82>X>X>X>103A
    82XXXX-103A Will get split up 82>X>X>XX>103A

    I would rather:
    82XX-103A Will get split up 82>X>X>103>A
    82XXX-103A Will get split up 82>X>X>X>103>A
    82XXXX-103A Will get split up 82>X>X>XX>103>A

    Working Code (I suspect their may be some unnecessary things it took a bit to get this far):



    Sub FindCharacter()
    
    Dim s As String
    Dim Slash As Long
    Dim Plant As String
    Dim Inst As String
    Dim inst1 As String
    Dim tag As String
    Dim r As Range
    
    Range("C4").Select
    Do Until ActiveCell.Value = ""
    
    s = ActiveCell.Value
    
    Slash = InStr(1, s, "-")
    Plant = Left(s, 2)
    Inst = Right(s, Len(s) - 2)
    inst1 = InStr(1, Inst, "-")
    tag = Left(Inst, inst1 - 1)
    ActiveCell.Offset(0, 1).Value = Plant
    
    Select Case True
        Case Len(tag) = 4
            ActiveCell.End(xlToRight).Offset(0, 1).Value = Left(tag, 1)
            ActiveCell.End(xlToRight).Offset(0, 1).Value = Mid(tag & "", 2, 1)
            ActiveCell.End(xlToRight).Offset(0, 1).Value = Right(tag, 2)
        Case Len(tag) = 3
            ActiveCell.End(xlToRight).Offset(0, 1).Value = Left(tag, 1)
            ActiveCell.End(xlToRight).Offset(0, 1).Value = Mid(tag & "", 2, 1)
            ActiveCell.End(xlToRight).Offset(0, 1).Value = Right(tag, 1)
         Case Len(tag) = 2
            ActiveCell.End(xlToRight).Offset(0, 1).Value = Left(tag, 1)
            ActiveCell.End(xlToRight).Offset(0, 1).Value = Right(tag, 1)
                   
    End Select
    
    'Program will past the fourth letter identifier if present in the same cell as the 3rd letter
        
        If Len(tag) = 2 Then
            ActiveCell.Offset.End(xlToRight).Offset(0, 2).Value = Mid(s, Slash + 1)
          Else
            ActiveCell.Offset.End(xlToRight).Offset(0, 1).Value = Mid(s, Slash + 1)
        End If
    
    ActiveCell.Offset(1, 0).Select
    
    Loop
    
    End Sub
    Last edited by AliGW; 03-23-2019 at 01:52 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 8
    Last Post: 02-12-2020, 02:47 AM
  2. Learning
    By ShashiSuppi in forum The Water Cooler
    Replies: 2
    Last Post: 08-01-2017, 05:54 AM
  3. Learning
    By ShashiSuppi in forum Suggestions for Improvement
    Replies: 1
    Last Post: 07-31-2017, 03:32 AM
  4. Hi All Just learning here.
    By Windyz in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 12-01-2016, 01:38 AM
  5. VBA code advice/Excel advice for summing groups of numbers
    By paulblower in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-17-2014, 05:47 AM
  6. Tips/Advice for Learning VBA
    By amartino44 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-28-2012, 04:37 PM
  7. Learning VBA - Suggestion for Beginners Learning Curve
    By sighlent1 in forum Excel General
    Replies: 1
    Last Post: 08-26-2010, 12:58 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1