VBA Autofill until variable changes

Franplante

New Member
Joined
Mar 31, 2011
Messages
8
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!
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
In which column is the formula supposed to be entered?
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
So you are overwriting column A? Maybe

Code:
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

New Member
Joined
Mar 31, 2011
Messages
8

ADVERTISEMENT

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

Legend
Joined
Jun 19, 2002
Messages
63,650
Maybe

Code:
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

New Member
Joined
Mar 31, 2011
Messages
8
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,318
Messages
5,600,923
Members
414,416
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top