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