Record Macro (Excel 2003) to conditionally copy paste by row

Question

I have data coming in from an external source daily. On one sheet I have a list of ticker symbols (sorted alphabetically) with corresponding data continuing in that row.

On another sheet I have the ticker's organized by their corresponding sector, rather than being organized alphabetically.

I'm trying to develop a macro so that the info from the first sheet will automatically paste into the second sheet by recognizing the ticker and pasting in the appropriate row.

Here's the code being used so far but it has not worked the way intended:

Dim LSymbol As String
    Dim LRow As Integer
    Dim LFound As Boolean

    On Error GoTo Err_Execute

    'Retrieve symbol value to search for
    LSymbol = Sheets("Portfolio Update").Range("B4").Value

    Sheets("Test").Select

    'Start at row 2
    LRow = 2
    LFound = False

    While LFound = False

        'Encountered blank cell in column B, terminate search
        If Len(Cells(2, LRow)) = 0 Then
            MsgBox "No matching symbol was found."
            Exit Sub

        'Found match in column b
        ElseIf Cells(2, LRow) = LSymbol Then

            'Select values to copy from "Portfolio Update" sheet
            Sheets("Portfolio Update").Select
            Range("B5:V5").Select
            Selection.Copy

            'Paste onto "Test" sheet
            Sheets("Test").Select
            Cells(3, LRow).Select
            Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=False

            LFound = True
            MsgBox "The data has been successfully copied."

        'Continue searching
        Else
            LRow = LRow + 1
        End If

    Wend

    On Error GoTo 0

    Exit Sub

Err_Execute:
    MsgBox "An error occurred."

End Sub

Thanks.

@TimWilliams: That is a valid answer ;) (Hint Hint) @EBB: Avoid using `.Select` See this link http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select — Siddharth Rout, Aug 09 '12 at 01:10

Tim Williams · Answer 1 · 2012-08-16T16:14:06.483

Should be .Cells(row,col) not.Cells(col,row)`

However, you can avoid looping by using Find() -

Sub Tester()

    Dim LSymbol As String

    Dim shtPU As Worksheet
    Dim shtTest As Worksheet
    Dim f As Range
    Dim c As Range

    Set shtPU = Sheets("Portfolio Update")
    Set shtTest = Sheets("Test")

    On Error GoTo Err_Execute

    For Each c In shtPU.Range("B4:B50").Cells

       LSymbol = c.Value 'Retrieve symbol value to search for

       If Len(LSymbol) > 0 Then
            Set f = shtTest.Columns(2).Find(LSymbol, , xlValues, xlWhole)
            If Not f Is Nothing Then
                'was found
                With c.Offset(0, 1).Resize(1, 21)
                    f.Offset(0, 1).Resize(1, .Columns.Count) = .Value
                End With
                c.Interior.Color = vbGreen
                'MsgBox "The data has been successfully copied."
            Else
                'not found
                c.Interior.Color = vbRed
                'MsgBox "No matching symbol was found."
            End If
       End If

    Next c

    Exit Sub

Err_Execute:
    MsgBox "An error occurred:" & Err.Description

End Sub

EDIT - added in looping through list of symbols

Tim Williams, @SiddharthRout, Awesome. Thanks for your help and prompt responses. I don't have access to it right now but once I do I'll use your advice and let you know how it goes. Thanks again. — EBB, Aug 09 '12 at 16:16
Tim Williams, @SiddharthRout, I was able to test it out and the macro works, but it only searches, copies and pastes the data for the first ticker into the 'Test' sheet. I'm trying to get the macro to search for all 40 tickers and paste the appropriate data into the 'Test" sheet. Any and all help would be appreciated. — EBB, Aug 16 '12 at 12:07

Record Macro (Excel 2003) to conditionally copy paste by row

1 Answers1