does anyone know why this range(c) doesnt work? i am looping through a row and filtering a table with c as a criteria, after that i need to paste everything this filter give me under the c cell.

Sub exercicio1()

Dim table As Range


For Each c In Range("i5", Range("i5").End(xlToRight))

  Range("B5").Select
  Selection.AutoFilter
  ActiveSheet.Range("$B$5:$C$5570").AutoFilter Field:=1, Criteria1:=c
  Range("C6").Select
  Range(Selection, Selection.End(xlDown)).Select
  Selection.Copy
  ActiveSheet.Range("$B$5:$C$5570").AutoFilter Field:=1
  Range(c).Offset(1).Select
  ActiveSheet.Paste
  Application.CutCopyMode = False

Next c

End Sub

i want to select the cell under c

CodePudding user response：

As the earlier answer "c" is already a range...

SO:

c.Offset(1).Select

CodePudding user response：

Return Lookup in Columns Using Application.Match

• Adjust the values in the constant section.

Option Explicit

Sub ReturnLookupInColumns()
    
    ' Define constants.
    
    ' Source (lookup and read)
    Const slCol As String = "B"
    Const svCol As String = "C"
    Const sfRow As Long = 6
    ' Destination (lookup and write)
    Const dfCol As String = "I"
    Const dlRow As Long = 5
    
    ' Reference the worksheet ('ws')...
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    ' ... and write its number of rows to a variable ('wsrCount').
    Dim wsrCount As Long: wsrCount = ws.Rows.Count
    
    ' Using the source lookup column ('slCol'), calculate the last row
    ' ('slRow'), the row of the last non-empty cell in the column.
    Dim slRow As Long: slRow = ws.Cells(wsrCount, slCol).End(xlUp).Row
    
    ' Reference the (one-column) source lookup range ('slrg').
    Dim slrg As Range
    Set slrg = ws.Range(ws.Cells(sfRow, slCol), ws.Cells(slRow, slCol))
    
    ' Using 'EntireRow' and 'Resize' on the source lookup range,
    ' reference the (one-column) source value range ('svrg').
    Dim svrg As Range: Set svrg = slrg.EntireRow.Columns(svCol)
    
    ' Using the destination lookup row ('dlRow'), calculate the last column
    ' ('dlCol'), the column of the last non-empty cell in the row.
    Dim dlCol As Long
    dlCol = ws.Cells(dlRow, ws.Columns.Count).End(xlToLeft).Column
    
    ' Reference the (one-row) destination lookup range ('dlrg').
    Dim dlrg As Range
    Set dlrg = ws.Range(ws.Cells(dlRow, dfCol), ws.Cells(dlRow, dlCol))
    
    ' Clear the contents below the destination lookup range.
    dlrg.Offset(1).Resize(wsrCount - dlrg.Row).ClearContents
    
    ' To avoid an inner loop (increase efficiency), use this little known
    ' feature of 'Application.Match' that will return the destination indexes
    ' (in this case the destination column indexes) of the matching values
    ' of the source lookup range, in a 2D one-based (one-column) array
    ' ('dIndexes'). If a source value is not found, the element
    ' at the same position in the array will contain an error value
    ' ('Error 2042').
    Dim dIndexes As Variant: dIndexes = Application.Match(slrg, dlrg, 0)
    
    ' Declare additional variables used in the loop.
    Dim svCell As Range ' Current Source Value Cell
    Dim sr As Long ' Current Row of the Source Ranges
    Dim dvCell As Range ' Current Destination Value Cell
    Dim dIndex As Variant ' Current Index in the Destination Indexes Array
    ' Firstly, 'dIndex' needs to be declared as variant because it will
    ' be assigned a number or an error value.
    ' Secondly, 'dIndex' needs to be declared as variant because it will
    ' be used as a so-called 'For Each control variable' which needs
    ' to be declared as variant (or as object) no matter what.
    
    ' Loop through the elements in the destination indexes array.
    For Each dIndex In dIndexes
        ' Since the destination indexes array has the same number of elements
        ' as the source ranges have rows and the array is one-based,
        ' this number ('sr') also represents the current element's index,
        ' the row position in the array (see 'dIndexes(sr, 1)' the line after).
        sr = sr   1
        ' Check if the current source lookup value was found.
        If IsNumeric(dIndexes(sr, 1)) Then ' source lookup value was found
            ' Reference the current source value cell.
            Set svCell = svrg.Cells(sr)
            ' Reference the current destination value cell.
            Set dvCell = ws.Cells(wsrCount, dlrg.Columns(dIndex).Column) _
                .End(xlUp).Offset(1)
            ' Write the value from the current source value cell
            ' to the current destination value cell.
            dvCell.Value = svCell.Value
        'Else ' source lookup value was not found; do nothing
        End If
    Next dIndex
    
    ' Inform to not wonder if the code has run or not.
    MsgBox "Lookup has finished.", vbInformation
    
End Sub