mercredi 23 septembre 2015

excel vba scan col for matching text and paste results in another column

I need to scan Col B for any values that have the same left characters that are found in cell AL2, the length of the string is found in AR1. If the left value in Col B matches the value in AL2 I need to copy the values in that row from Col B to col G. copied into Col At, starting a AT6 and continuing down until there until all values in Col C have been checked. First picture is data that will be scanned, second picture is what I want the macro to spit out

Here is the modified macro that I recorded. I am getting an runtime 13 error on the IF statement. Any ideas on how to clean this up ?

data to be scanned data copied from scanned data

    Sub GenSummary()
    Dim dlen As String


    Worksheets("HR-Cal").Activate
    dlen = Worksheets("HR-Cal").Range("AR1")
    r = ActiveCell.Row
    For lrow = Cells(Cells.Rows.Count, "C").End(xlUp).Row To 7 Step -1

    'checks curent row for box name in cell AL2
    'copies values from col B to col G in that row to into Col AT, starting at AS6
    If Left(Cells(lrow, "B"), dlen) = Range("AL2") Then Rows.Range("B" & r & ":G" & r).Select
    Selection.Copy
    Range("AS6").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    Next lrow


    End Sub

Aucun commentaire:

Enregistrer un commentaire