Results 1 to 7 of 7

Match a cell with dynamic range (multiple columns)

Threaded View

  1. #1
    Registered User
    Join Date
    07-08-2010
    Location
    atlanta
    MS-Off Ver
    Excel 2003
    Posts
    26

    Match a cell with dynamic range (multiple columns)

    With the code below I am able to match cell A1 in worksheet:"DataByCC" with values range on column A to the last used cell on that column (col A)
    If there is a match then I copy the entire row on the other Workshhets to DataByCC

    The only Edit/Change I am trying to do is do the match (cell A1 in DataByCC) with columns A through F,,,if there is a match just copy the entire row to "DataByCC"

    I hope someone can help
    Thanks
    bg

    Dim LR As Long, i As Long, ws As Worksheet, j As Long
    Dim k%, NextRow&
    Application.ScreenUpdating = False
    Sheets("DataByCC").UsedRange.Offset(1).ClearContents
    For Each ws In Sheets(Array("REVENUE", "PAYROLL", "Benefits"))
    With ws
    Select Case .Name
    Case "CapEX": j = 13
    Case Else: j = 9
    End Select
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    For i = j To LR
    With .Range("A" & i)
    If IsNumeric(Application.Match(.Value, Sheets("DataByCC").Columns("A"), 0)) Then
    On Error Resume Next
    .EntireRow.SpecialCells(12).Copy
    Sheets("DataByCC").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues 
    Application.CutCopyMode = False
    On Error GoTo 0
    End If
    End With
    Next i
    End With
    Next ws
    Last edited by davesexcel; 08-19-2011 at 07:54 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1