Necesito ocupar la función buscarv en macros pero son datos que vn aumentando todos los días es decir rango variable
Estimados
Necesito ocupar la función bucarv pero con rango variables ya que todos los días van aumentados los datos desde la planilla que saco los datos a la otra planilla que voy rellenar.
Adjunto ejemplo de lo que hago donde lo destacado con negrita es el rango variable
'
' Macro18 Macro
'
'
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-2],'[list-integrmedica-al-origenes.xlsx]list-integrmedica-al-05-09-2014'!R2C6:R19233C37,3,0)"
Selection.Copy
Range("E2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-4],'[list-integrmedica-al-origenes.xlsx]list-integrmedica-al-05-09-2014'!R2C6:R19233C37,9,0)"
Range("E2").Select
Selection.Copy
Range("F2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-5],'[list-integrmedica-al-origenes.xlsx]list-integrmedica-al-05-09-2014'!R2C6:R19233C37,11,0)"
Range("F2").Select
Selection.Copy
Range("G2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-6],'[list-integrmedica-al-origenes.xlsx]list-integrmedica-al-05-09-2014'!R2C6:R19233C37,12,0)"
Range("G2").Select
Selection.Copy
Range("H2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-7],'[list-integrmedica-al-origenes.xlsx]list-integrmedica-al-05-09-2014'!R2C6:R19233C37,17,0)"
Range("H2").Select
Selection.Copy
Range("I2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-8],'[list-integrmedica-al-origenes.xlsx]list-integrmedica-al-05-09-2014'!R2C6:R19233C37,26,0)"
Range("C2:I2").Select
Selection.AutoFill Destination:=Range("C2:I17611")
Range("C2:I17611").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 20
Range("Z2").Select
Application.CutCopyMode = False
End Sub
Sub Macro19()
1 Respuesta
Podemos cambiar la fórmula, en lugar de la fila, buscamos en toda la columna.
Prueba con la siguiente macro:
Sub Formulas()
'Por.Dante Amor
l1 = "list-integrmedica-al-origenes.xlsx"
h1 = "list-integrmedica-al-05-09-2014"
Range("C2") = "=VLOOKUP(RC[-2],'[" & l1 & "]" & h1 & "'!C6:C37,3,0)"
Range("E2") = "=VLOOKUP(RC[-4],'[" & l1 & "]" & h1 & "'!C6:C37,9,0)"
Range("F2") = "=VLOOKUP(RC[-5],'[" & l1 & "]" & h1 & "'!C6:C37,11,0)"
Range("G2") = "=VLOOKUP(RC[-6],'[" & l1 & "]" & h1 & "'!C6:C37,12,0)"
Range("H2") = "=VLOOKUP(RC[-7],'[" & l1 & "]" & h1 & "'!C6:C37,17,0)"
Range("I2") = "=VLOOKUP(RC[-8],'[" & l1 & "]" & h1 & "'!C6:C37,26,0)"
Range("C2:I2").AutoFill Destination:=Range("C2:I17611")
Range("C2:I17611").Copy
Range("C2").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub
