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

  • 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.

  • 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

  • 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.

  • Ahh - I see.


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



    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)

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



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

  • 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.



    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).



    Thanks so much for your help up to this point. It's been extremely useful.

  • 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.