Autofill formula con una columa variable hasta lrow
publicado
Hola a todos,
Estoy atascado en algo que debería ser sencillo, pero no lo consigo sacar por más que busco.
Tengo un archivo el cual primero inserta 2 columnas a la derecha de una columna llamada "fRateBatcher"), después inserta unas formulas en las Offset(0 , 1), y después debe buscar donde el rango donde se han asignado las fórmulas (como ejemplo A3:B3) y copiar/pegar formula con ese rango variable hasta last row.
Mi problema está siendo que no consigo encontrar el Range ya que el rango puede variar en columa, aunque no en fila.
Este es el código que tengo ahora:
Sub SANCMARC_Magic()
Dim rng As Range
Set rng = Range("A11:CDD11").Find("fRateBatcher")
Dim last_row As Long
last_row = Cells(Rows.Count, 2).End(xlUp).Row
Sheets("DISP Format").Select
Set Colheaders = Range("A11:CDD11").Find("fRateBatcher")
If Colheaders Is Nothing Then
MsgBox "Column was not found."
Exit Sub
Else
Columns(Colheaders.Column).Offset(0, 1).Insert
End If
Set rng2 = Range("A11:CDD11").Find("fRateBatcher").Offset(0, 1)
rng2.Value = "SANCMARC MIN COST"
Set Formula = Range("A11:CDD11").Find("SANCMARC MIN COST").Offset(1, 0)
Set fillRange = Range("A11:CDD11").Find("SANCMARC MIN COST").Offset(1, lastrow)
Formula.Formula2R1C1 = _
"=IFERROR(VLOOKUP(CONCATENATE(XLOOKUP(INDEX(C1:C116,ROW(RC44),MATCH('Postal codes (important)'!R1C42,R11,0)),'Postal codes (important)'!C12,'Postal codes (important)'!C13,"""",0),XLOOKUP(INDEX(C1:C116,ROW(RC44),MATCH('Postal codes (important)'!R1C43,R11,0)),'Postal codes (important)'!C12,'Postal codes (important)'!C13,"""",0)),Matrix!C1:C52,MATCH(VLOOKUP(INDEX(C1:C11" & _
"6,ROW(RC44),MATCH('Postal codes (important)'!R1C44,R11,0)),'Postal codes (important)'!C36:C37,2,0)&"" ""&Matrix!R3C16,Matrix!R2,0),0)*ROUND(INDEX(C1:C116,ROW(RC44),MATCH('Postal codes (important)'!R1C41,R11,0)),0),"""")" & _
""
Sheets("DISP Format").Select
Set Colheaders = Range("A11:CDD11").Find("SANCMARC MIN COST")
If Colheaders Is Nothing Then
MsgBox "Column was not found."
Exit Sub
Else
Columns(Colheaders.Column).Offset(0, 1).Insert
End If
Set rng3 = Range("A11:CDD11").Find("SANCMARC MIN COST").Offset(0, 1)
rng3.Value = "SANCMARC MAX COST"
Set Formula2 = Range("A11:CDD11").Find("SANCMARC MAX COST").Offset(1, 0)
Set fillRange2 = Range("A11:CDD11").Find("SANCMARC MAX COST").Offset(1, lastrow)
Formula2.Formula2R1C1 = _
"=IFERROR(VLOOKUP(CONCATENATE(XLOOKUP(INDEX(C1:C116,ROW(RC44),MATCH('Postal codes (important)'!R1C42,R11,0)),'Postal codes (important)'!C12,'Postal codes (important)'!C13,"""",0),XLOOKUP(INDEX(C1:C116,ROW(RC44),MATCH('Postal codes (important)'!R1C43,R11,0)),'Postal codes (important)'!C12,'Postal codes (important)'!C13,"""",0)),Matrix!C1:C52,MATCH(VLOOKUP(INDEX(C1:C11" & _
"6,ROW(RC44),MATCH('Postal codes (important)'!R1C44,R11,0)),'Postal codes (important)'!C36:C37,2,0)&"" ""&Matrix!R3C7,Matrix!R2,0),0)*ROUND(INDEX(C1:C116,ROW(RC44),MATCH('Postal codes (important)'!R1C41,R11,0)),0),"""")" & _
""
rng3.Interior.ColorIndex = 46
rng2.Interior.ColorIndex = 46
Formula2.Select
Selection.Copy
Application.CutCopyMode = False
Selection.AutoFill Destination:= "Formula2"&":"&last_row (diria que aquí está el fallo)
End Sub
Os agradezco mucho si alguien me puede ayudar, y perdón por la biblia de código, aún soy muuuuuuuuy novato.
Marc
Featured Replies
Archivado
Este tema está ahora archivado y está cerrado a más respuestas.
Hola a todos,
Estoy atascado en algo que debería ser sencillo, pero no lo consigo sacar por más que busco.
Tengo un archivo el cual primero inserta 2 columnas a la derecha de una columna llamada "fRateBatcher"), después inserta unas formulas en las Offset(0 , 1), y después debe buscar donde el rango donde se han asignado las fórmulas (como ejemplo A3:B3) y copiar/pegar formula con ese rango variable hasta last row.
Mi problema está siendo que no consigo encontrar el Range ya que el rango puede variar en columa, aunque no en fila.
Este es el código que tengo ahora:
Sub SANCMARC_Magic() Dim rng As Range Set rng = Range("A11:CDD11").Find("fRateBatcher") Dim last_row As Long last_row = Cells(Rows.Count, 2).End(xlUp).Row Sheets("DISP Format").Select Set Colheaders = Range("A11:CDD11").Find("fRateBatcher") If Colheaders Is Nothing Then MsgBox "Column was not found." Exit Sub Else Columns(Colheaders.Column).Offset(0, 1).Insert End If Set rng2 = Range("A11:CDD11").Find("fRateBatcher").Offset(0, 1) rng2.Value = "SANCMARC MIN COST" Set Formula = Range("A11:CDD11").Find("SANCMARC MIN COST").Offset(1, 0) Set fillRange = Range("A11:CDD11").Find("SANCMARC MIN COST").Offset(1, lastrow) Formula.Formula2R1C1 = _ "=IFERROR(VLOOKUP(CONCATENATE(XLOOKUP(INDEX(C1:C116,ROW(RC44),MATCH('Postal codes (important)'!R1C42,R11,0)),'Postal codes (important)'!C12,'Postal codes (important)'!C13,"""",0),XLOOKUP(INDEX(C1:C116,ROW(RC44),MATCH('Postal codes (important)'!R1C43,R11,0)),'Postal codes (important)'!C12,'Postal codes (important)'!C13,"""",0)),Matrix!C1:C52,MATCH(VLOOKUP(INDEX(C1:C11" & _ "6,ROW(RC44),MATCH('Postal codes (important)'!R1C44,R11,0)),'Postal codes (important)'!C36:C37,2,0)&"" ""&Matrix!R3C16,Matrix!R2,0),0)*ROUND(INDEX(C1:C116,ROW(RC44),MATCH('Postal codes (important)'!R1C41,R11,0)),0),"""")" & _ "" Sheets("DISP Format").Select Set Colheaders = Range("A11:CDD11").Find("SANCMARC MIN COST") If Colheaders Is Nothing Then MsgBox "Column was not found." Exit Sub Else Columns(Colheaders.Column).Offset(0, 1).Insert End If Set rng3 = Range("A11:CDD11").Find("SANCMARC MIN COST").Offset(0, 1) rng3.Value = "SANCMARC MAX COST" Set Formula2 = Range("A11:CDD11").Find("SANCMARC MAX COST").Offset(1, 0) Set fillRange2 = Range("A11:CDD11").Find("SANCMARC MAX COST").Offset(1, lastrow) Formula2.Formula2R1C1 = _ "=IFERROR(VLOOKUP(CONCATENATE(XLOOKUP(INDEX(C1:C116,ROW(RC44),MATCH('Postal codes (important)'!R1C42,R11,0)),'Postal codes (important)'!C12,'Postal codes (important)'!C13,"""",0),XLOOKUP(INDEX(C1:C116,ROW(RC44),MATCH('Postal codes (important)'!R1C43,R11,0)),'Postal codes (important)'!C12,'Postal codes (important)'!C13,"""",0)),Matrix!C1:C52,MATCH(VLOOKUP(INDEX(C1:C11" & _ "6,ROW(RC44),MATCH('Postal codes (important)'!R1C44,R11,0)),'Postal codes (important)'!C36:C37,2,0)&"" ""&Matrix!R3C7,Matrix!R2,0),0)*ROUND(INDEX(C1:C116,ROW(RC44),MATCH('Postal codes (important)'!R1C41,R11,0)),0),"""")" & _ "" rng3.Interior.ColorIndex = 46 rng2.Interior.ColorIndex = 46 Formula2.Select Selection.Copy Application.CutCopyMode = False Selection.AutoFill Destination:= "Formula2"&":"&last_row (diria que aquí está el fallo) End Sub
Os agradezco mucho si alguien me puede ayudar, y perdón por la biblia de código, aún soy muuuuuuuuy novato.
Marc