CODEWITHSUNDEEP
excelif-statementvba
Alberto Brown
Alberto Brown

Reputation: 355

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 GenerateSummaryPage()
'

' scans B column for combiner box numbers
Application.ScreenUpdating = False
    Dim dlen As String


    Worksheets("HR-Cal").Activate
    dlen = Worksheets("HR-Cal").Range("AR2")
    r = ActiveCell.Row
    For lrow = Cells(Cells.Rows.Count, "B").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) = ActiveSheet.Range("AL2").Text Then Range("B" & r).Rows.Select
    'If Left(Cells(lrow, "B"), dlen) = Range("AL2").Value Then Range("B" & r & ":G" & r).Select
        Selection.Copy
    Range("AT100000").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)

    Next lrow

Application.ScreenUpdating = True
'insert rows for inverter headers

End Sub

Upvotes: 3

Views: 741

Answers (2)

phil652
phil652

Reputation: 1506

You should try this.

If Left(Cells(lrow, "B"), dlen) = ActiveSheet.Range("AL2").Text Then
   Range(Cells(lrow, "B"), Cells(lrow, "G")).Value = Range(Cells(lrow, "AT"), Cells(lrow, "AY")).Value
End if

If this doesn't work you, before the if you could try to check the value of lrow and dlen to make to sure they have a correct value.

Let me know if that works for you

Upvotes: 1

findwindow
findwindow

Reputation: 3153

Assuming you don't need formatting, I think you want something like this...

Dim ws As Worksheet
Set ws = ThisWorkbook.WorkSheets("HR-Cal")
dlen = 2 'change this
lrow = ws.Cells(Cells.Rows.Count, "B").End(xlUp).Row

For i = 7 To lrow

'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(i, "B"), dlen) = ws.Range("M2").Text Then 'change M2
'Range("B" & i).Rows.Select
'If Left(Cells(lrow, "B"), dlen) = Range("AL2").Value Then Range("B" & r & ":G" & r).Select
'Selection.Copy
Range(Cells(i, 10), Cells(i, 15)).Value = Range(Cells(i, 2), Cells(i, 7)).Value 'change columns
'Range("AT100000").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
End If

Next i

Upvotes: 0

Related Questions