My data that I receive are dynamic in the sense that the row count and column count are everchanging, but the columns that I am trying to concatenate will always be in column J and K. I want to put the concatenation at the end of the row to the right and then autofill it. So if I have an array up to column JT, I want the concatenation of J2&K2 at JU2 and then autofilled.
What I've tried to attempt is the following with the error "Run-time error '1004': Application-defined or object -defined error" on the bolded.
Also I have tried to look up on how to autofill this given formula but I can only dynamically reference the row but not the column. Because, like I said the column is also dynamic so the formula might not always be on column JU.
Sub Macro1()
'
Macro1 Macro
'
Dim lastrow As Long
lastrow = Range("A" & Rows.Count).End(xlUp).Row
Dim lastCol As Long
lastCol = Sheet1.Cells(1, Columns.Count).End(xlToLeft).Column
Dim ColK As Integer
ColK = lastCol - 10
Dim ColJ As Integer
ColJ = lastCol - 11
Selection.End(xlToRight).Select
ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Select
ActiveCell.FormulaR1C1 = "*"
ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Select
ActiveCell.FormulaR1C1 = "Combined USI"
ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Select
ActiveCell.FormulaR1C1 = "In Position Report?"
ActiveCell.Offset(rowOffset:=1, columnOffset:=-2).Select
ActiveCell.FormulaR1C1 = "*"
ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Select
ActiveCell.FormulaR1C1 = "=RC[-ColK]&RC[-ColJ]"
ActiveCell.AutoFill Destination:=Range("JU2:JW" & lastrow)
'
End Sub
What I've tried to attempt is the following with the error "Run-time error '1004': Application-defined or object -defined error" on the bolded.
Also I have tried to look up on how to autofill this given formula but I can only dynamically reference the row but not the column. Because, like I said the column is also dynamic so the formula might not always be on column JU.
Sub Macro1()
'
Macro1 Macro
'
Dim lastrow As Long
lastrow = Range("A" & Rows.Count).End(xlUp).Row
Dim lastCol As Long
lastCol = Sheet1.Cells(1, Columns.Count).End(xlToLeft).Column
Dim ColK As Integer
ColK = lastCol - 10
Dim ColJ As Integer
ColJ = lastCol - 11
Selection.End(xlToRight).Select
ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Select
ActiveCell.FormulaR1C1 = "*"
ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Select
ActiveCell.FormulaR1C1 = "Combined USI"
ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Select
ActiveCell.FormulaR1C1 = "In Position Report?"
ActiveCell.Offset(rowOffset:=1, columnOffset:=-2).Select
ActiveCell.FormulaR1C1 = "*"
ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Select
ActiveCell.FormulaR1C1 = "=RC[-ColK]&RC[-ColJ]"
ActiveCell.AutoFill Destination:=Range("JU2:JW" & lastrow)
'
End Sub