Announcement

Collapse
No announcement yet.

Lookup value then copy/paste

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Lookup value then copy/paste



    I have a column of codes comprised of mostly numbers and one letter (ie 81548323D1). I'm trying to write a code that will lookup the first four digits of the code, then copy/paste only those digits to another column/sheet.

    Does any function exist to do this?

    Any help is immensely appreciated as this problem has been plaguing me for some time now.

    Regards,

    Peter

  • #2
    Re: Lookup value then copy/paste

    Hi ptdrivas,
    Nice to meet you this forum.
    If a1:a9 have data then array formula will works.
    =INDEX($A$1:$A$9,SMALL(IF(LEFT($A$1:$A$9,4)="8154",ROW($A$1:$A$9)),ROW(A1)))
    F2 > Hold Ctrl + Shift and hit Enter.
    Regards,Junho

    ps:Thanks Dave for new style forum and belated best wishs on your birthday.

    Comment


    • #3
      Re: Lookup value then copy/paste

      I may be misunderstanding the question - do you just want to extract the first four digits? Also when you say 'digits', do you mean that it shouldn't return a non-digit character (ie A91324248348 should give you 9132 rather than A913?)

      If it's the first instance then a plain vanilla =LEFT(a1, 4) will suffice if you drag it down.. and you can then copy / paste values to dump that stuff into a new column/sheet/workbook.

      If you're after VBA code for this then let me know

      Comment


      • #4
        Re: Lookup value then copy/paste

        @AwesomeSwelles that works very well. I do indeed want to only extract the first four numbers in the code, and all codes are formatted ########D#. Also, I have a full column of these codes (about 150-200) so I was thinking I could do some version of a vlookup to find all codes in the column with the desired first 4 numbers, and then extract all those codes into a column on a different sheet. I hope that makes more sense, but if not let me know and I will clarify more.

        Thanks so much for your help.

        Comment


        • #5
          Ahh - I see.

          I'd do it in VBA - this is a bit clunky but would work

          Code:
          Sub DigitiseMe()
          Dim TargetRng As Range
          ' What numbers do you want to check
          Digits = 2354
          ' Where do you want the matches to go
          Set TargetRng = Range("B1")
          
          Application.ScreenUpdating = False
          For Each Cell In ActiveSheet.UsedRange.Columns("A:A").Cells
               If Int(Left(Cell.Value, 4)) = Digits Then
                  TargetRng.Value = Cell.Value
                  Set TargetRng = TargetRng.Offset(1, 0)
               End If
          Next Cell
          Application.ScreenUpdating = True
          End Sub
          Digits - this is the 4-digit number you want to match
          TargetRng - This is the cell you want to 'paste' the first match to, the rest will follow underneath, change this to sheet2.range("b1") for example if you want to start in B1 on sheet2..
          columns("A:A") should be replaced with the column that your codes are in (and you may need to play around with how you define the range if there are gaps, etc that will throw off activesheet.usedrange)

          Comment


          • #6
            Code:
            For Each Cell In ActiveSheet.UsedRange.Columns("A:A").Cells
                 If Int(Left(Cell.Value, 4)) = Digits Then
                    TargetRng.Value = Cell.Value
                    Set TargetRng = TargetRng.Offset(1, 0)
                 End If
            Next Cell
            Application.ScreenUpdating = True
            End Sub

            Should Cell be defined before this portion of the sub begins? (ie Dim Cell as String)

            Comment


            • #7
              as Range
              Cheers,

              S M C

              Click To Read: How To Use Tags In Your Threads/Posts
              Please take time to read Forum Rules before posting
              Message To Cross Posters

              Comment


              • #8
                One last curveball.....

                I've sort of shifted gears and am trying to incorporate part of the For Each loop you wrote with a UDF that I wrote called JDatetoDate. The code is below. Basically what this looks to do is take the first four digits of the code, and make them a date formatted MM/DD/YYYY. Still the same general concept of selecting the codes, extracting the four digits and then putting them into the column, and the function has been proven to work for this method.

                Code:
                Dim TargetRng As Range
                Dim JDate As String
                Dim Cell As Range
                Set TargetRng = Sheet2.Range("A60")
                
                For Each Cell In ActiveSheet.UsedRange.Columns("E:E").Cells
                    JDate = ActiveCell.Value
                    rdate = JDateToDate(JDate)
                    TargetRng.Value = rdate
                    Set TargetRng = TargetRng.Offset(1, 0)
                Next Cell
                The problem I'm having is that it's not looping through the cells and instead only reading the first cell in the column to the function and returning in 23 times it the column that I want it to be in on a separate sheet. Am I just running into a problem with my range messing up the UsedRange? Or am I wrong and this is a problem with my function (shown below).

                Code:
                Public Function JDateToDate(JDate As String) As Long
                Dim TheYear As Integer
                Dim TheDay As Integer
                Dim TheDate As Long
                
                TheYear = Left(JDate, 1)
                If TheYear <= 9 & TheYear >= 1 Then
                    TheYear = TheYear + 2000
                ElseIf TheYear < 1 Then
                    TheYear = 2010
                End If
                
                TheDay = Mid(JDate, 2, 3)
                TheDate = DateSerial(TheYear, 1, TheDay)
                JDateToDate = TheDate
                
                End Function
                Thanks so much for your help up to this point. It's been extremely useful.

                Comment


                • #9


                  Note that ActiveSheet.UsedRange will be the range starting with the first used range to the last used range.

                  So if your first used range is E5 and your last used range is J28, then your used range will be E5:J28. Now, columns("E:E") of E5:J28 is essentially Range("I5:I28").

                  And, in this example, there are only 23 rows. So check if there is something like this happening in your sheet.
                  Cheers,

                  S M C

                  Click To Read: How To Use Tags In Your Threads/Posts
                  Please take time to read Forum Rules before posting
                  Message To Cross Posters

                  Comment

                  Working...
                  X