VBA Autofill until variable changes

[Franplante]

I'm trying to script this macro to end autofill when the variable changes. This is my script so far. I know it is not right. Any help possible would be appreciated.

I want the autofill to end when Field Location changes. It repeats the same value over and over again for multiple rows, which is what I want. When it changes to a new value I want it to stop and not autofill the new value. I am using the index and match function to fill this table with dynamic information to change when a variable changes on another worksheet. As that variable changes I want the table to load only with the data associated with the variable selected.

Sub FieldLocation()
'
' FieldLocation Macro

Dim Lastrow As Long

Lastrow = Range("A2:A" & Rows.Count).End(xlDown).Row
ActiveCell.FormulaR1C1 = _
"=INDEX('Elleh Block'!C:C[5],MATCH(List!R8C12,'Elleh Block'!C[1],0)+ROW()-2,2,1)"
Range("A2").Select
Range("A2").AutoFill Destination:=Range("A2:A" & Lastrow), Type:=xlFillDefault
Range("A2:A" & Lastrow).Select

End Sub


Thank you!

 

[VoG]

In which column is the formula supposed to be entered?

 

[Franplante]

The formula is being entered and autofilled in column A of the Table sheet.

 

[VoG]

So you are overwriting column A? Maybe

Sub FieldLocation()
'
' FieldLocation Macro

Dim Lastrow As Long

Lastrow = Range("A" & Rows.Count).End(xlUp).Row
Range("A2:A" & Lastrow).FormulaR1C1 = "=INDEX('Elleh Block'!C:C[5],MATCH(List!R8C12,'Elleh Block'!C[1],0)+ROW()-2,2,1)"
End Sub

 

[Franplante]

I am absolutely overwriting column A. I am trying to find a way to End Autofill when the cell changes variable in the autofill.

For example

<TABLE style="WIDTH: 86pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=115 border=0 x:str><COLGROUP><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4205" width=115><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 86pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=115 height=17>A-A043-L/094-I-05</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>A-A043-L/094-I-05</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>A-A043-L/094-I-05</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>A-A043-L/094-I-05</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>A-A043-L/094-I-05</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>A-A043-L/094-I-05</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>A-A043-L/094-I-05</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>A-A043-L/094-I-05</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>A-A043-L/094-I-05</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>A-A043-L/094-I-05</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>A-A043-L/094-I-05</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>A-A043-L/094-I-05</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>A-A043-L/094-I-05</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>A-A043-L/094-I-05</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>A-A043-L/094-I-05</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>A-A043-L/094-I-05</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>C-A011-G/094-I-12</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>C-A011-G/094-I-12</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>C-A011-G/094-I-12</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>C-A011-G/094-I-12</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>C-A011-G/094-I-12</TD></TR></TBODY></TABLE>

I want the Autofill to End when the value changes from A-A043-L/094-I-05 to C-A011-G/094-I-12 instead of continuing to Autofill until the end of the Column. I can't set it to a static range because the table will be dynamic and the range will change depending on what value I am MATCHing on.

 

[VoG]

Maybe

Sub FieldLocation()
'
' FieldLocation Macro

Dim Lastrow As Long, i As Long
Lastrow = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To Lastrow
    If Range("A" & i).Value <> Range("A" & i + 1).Value Then Exit For
Next i
Range("A2:A" & i).FormulaR1C1 = "=INDEX('Elleh Block'!C:C[5],MATCH(List!R8C12,'Elleh Block'!C[1],0)+ROW()-2,2,1)"
End Sub

 

[Franplante]

I tried that code but that only fills in 1 cell, only one instance of A-A043-L/094-I-05 and I want it to repeat the formula until it returns another Field Location