AutoFill Destination - Query / Error

J

JoeRooney

Board Regular
Joined
Nov 27, 2017
Messages
173
Office Version
  1. 365
Hi,


I am currently having an issue with some code that I havenever experienced before and can’t figure out the cause , wondering if anyonehere has had the same issue & possible fix.

I have some code that bring formula’s down to the last cell acrossa worksheet (code below)

Dim lastRow As Long
lastRow =Range("A" & Rows.Count).End(xlUp).Row

Range("B2").Select
ActiveCell.FormulaR1C1 ="=VLOOKUP(RC[-1],TWG_File!C[-1]:C[28],1,FALSE)"
Range("B2").AutoFill Destination:=Range("B2:B" &lastRow)

When I run the code in VB it works approx. 80% of the time and will autofill downto the last cell correctly

When I assign the macro to a button it never works.

The issue is rather than autofill the formula down it autofillsthe formula up, so it adds the formula to the header line.

I can’t figure out why it works fine sometimes and othertimes not.

Any help on this is greatly appreciated.

Thanks

 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
Id imagine your issue is that:

Code: 
[FONT=Calibri][SIZE=3][COLOR=#000000]Range("B2").AutoFill Destination:=Range("B2:B1")

is perfectly valid and will autofill up. So id have thought lastRow = 1. I cant see any reason at all why that doesnt work with a button.
[/COLOR][/SIZE][/FONT]
 
Upvote 0
Thanks Steve, there is no issue with the code as I have just added the macro to my ribbon and when I run it from there it works fine, from VB it also runs fine the majority of them time only when I assign the macro to a button and un from there that it autofill up.

Very weird and cant figure out why
 
Upvote 0
What do you mean by running fine the majority of the time? Id imagine its something to do with not qualifying your ranges with a sheet if you are experiencing seemingly random behaviour.
 
Upvote 0
When I run the code from VB its runs correctly probably 9 times out of 10 , when I assign the macro to a button it never runs correctly and autofills up everytime for most columns.

For example for column B the formula is

=VLOOKUP(A2,TWG_File!A:AD,1,FALSE)

It autofills up

For column d the formula is

=SCF_File!B2


Andit does not , it looks like this problem does not apply to formulas simplylooking at another cell in another worksheet.



Again this onlyhappens when assigned to a button. Can you suggest something I should add tothe code ?
 
Upvote 0
This is how id write it:

Code: 
With Sheets("Sheet1")
    lastRow = Application.Max(2, .Range("A" & .Rows.Count).End(xlUp).Row)
    .Range("B2:B" & lastRow).FormulaR1C1 = "=VLOOKUP(RC[-1],TWG_File!C[-1]:C[28],1,FALSE)"
End With
 
Upvote 0
Sub Merge_Check2()

Dim lastRow As Long
lastRow = Range("A" & Rows.Count).End(xlUp).Row

Sheets("SCF_File").Select
Columns("A:A").Select
Selection.Copy
Sheets("Merge Check").Select
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 13434879
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("A1").Select
Selection.Font.Bold = True
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

Range("B2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],TWG_File!C[-1]:C[28],1,FALSE)"
Range("B2").AutoFill Destination:=Range("B2:B" & lastRow)

Range("C2").Select
ActiveCell.FormulaR1C1 = "=IF(AND(RC[-2]="""",RC[-1]=""""),"""",RC[-2]=RC[-1])"
Range("C2").AutoFill Destination:=Range("C2:C" & lastRow)

Range("D2").Select
ActiveCell.FormulaR1C1 = "=SCF_File!RC[-2]"
Range("D2").AutoFill Destination:=Range("D2:D" & lastRow)

Range("E2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],TWG_File!C[-4]:C[25],4,FALSE)"
Range("E2").AutoFill Destination:=Range("E2:E" & lastRow)

Range("F2").Select
ActiveCell.FormulaR1C1 = _
"=IF(AND(RC[-2]="""",RC[-1]=""""),"""",IF(AND(RC[-2]=82,LEFT(RC[-1],2)=""DK""),""TRUE"",IF(AND(RC[-2]=80,LEFT(RC[-1],2)=""PL""),""TRUE"",""FALSE"")))"
Range("F2").AutoFill Destination:=Range("F2:F" & lastRow)

Range("G2").Select
ActiveCell.FormulaR1C1 = "=SCF_File!RC[-4]"
Range("G2").AutoFill Destination:=Range("G2:G" & lastRow)

Range("H2").Select
ActiveCell.FormulaR1C1 = "=SCF_File!RC[-4]"
Range("H2").AutoFill Destination:=Range("H2:H" & lastRow)

Range("I2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-8],TWG_File!C[-8]:C[21],7,FALSE)"
Range("I2").AutoFill Destination:=Range("I2:I" & lastRow)

Range("J2").Select
ActiveCell.FormulaR1C1 = "=SCF_File!RC[-5]"
Range("J2").AutoFill Destination:=Range("J2:J" & lastRow)

Range("K2").Select
ActiveCell.FormulaR1C1 = "=SCF_File!RC[-5]"
Range("K2").AutoFill Destination:=Range("K2:K" & lastRow)

Range("L2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-11],TWG_File!C[-11]:C[18],8,FALSE)"
Range("L2").AutoFill Destination:=Range("L2:L" & lastRow)

Range("M2").Select
ActiveCell.FormulaR1C1 = "=IF(AND(RC[-2]="""",RC[-1]=""""),"""",RC[-2]=RC[-1])"
Range("M2").AutoFill Destination:=Range("M2:M" & lastRow)

Range("N2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=FALSE,RC[-2]-RC[-3],""-"")"
Range("N2").AutoFill Destination:=Range("N2:N" & lastRow)

Range("O2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-11]=80,SCF_File!RC[-8],""-"")"
Range("O2").AutoFill Destination:=Range("O2:O" & lastRow)

Range("P2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-12]=80,EDATE(RC[-4],RC[2]),""-"")"
Range("P2").AutoFill Destination:=Range("P2:P" & lastRow)

Range("Q2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-13]="""","""",IF(AND(RC[-13]=80,RC[-2]>RC[-6]),""TRUE"",IF(AND(RC[-13]=82,RC[-2]=""-""),""TRUE"",""FALSE"")))"
Range("Q2").AutoFill Destination:=Range("Q2:Q" & lastRow)

Range("R2").Select
ActiveCell.FormulaR1C1 = "=SCF_File!RC[-10]"
Range("R2").AutoFill Destination:=Range("R2:R" & lastRow)

Range("S2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-18],TWG_File!C[-18]:C[11],9,FALSE)"
Range("S2").AutoFill Destination:=Range("S2:S" & lastRow)

Range("T2").Select
ActiveCell.FormulaR1C1 = _
"=IF(AND(RC[-2]="""",RC[-1]=""""),"""",IF(AND(RC[-2]=0,RC[-1]=999),""TRUE"",RC[-2]=RC[-1]))"
Range("T2").AutoFill Destination:=Range("T2:T" & lastRow)

Range("U2").Select
ActiveCell.FormulaR1C1 = "=SCF_File!RC[-12]"
Range("U2").AutoFill Destination:=Range("U2:U" & lastRow)

Range("V2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-21],TWG_File!C[-21]:C[8],22,FALSE)"
Range("V2").AutoFill Destination:=Range("V2:V" & lastRow)

Range("W2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-22],TWG_File!C[-22]:C[7],23,FALSE)"
Range("W2").AutoFill Destination:=Range("W2:W" & lastRow)

Range("X2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-23],TWG_File!C[-23]:C[6],24,FALSE)"
Range("X2").AutoFill Destination:=Range("X2:X" & lastRow)

Range("Y2").Select
ActiveCell.FormulaR1C1 = "=SCF_File!RC[-15]"
Range("Y2").AutoFill Destination:=Range("Y2:Y" & lastRow)

Range("Z2").Select
ActiveCell.FormulaR1C1 = "=SCF_File!RC[-15]"
Range("Z2").AutoFill Destination:=Range("Z2:Z" & lastRow)

Range("AA2").Select
ActiveCell.FormulaR1C1 = "=SCF_File!RC[-15]"
Range("AA2").AutoFill Destination:=Range("AA2:AA" & lastRow)

Range("AB2").Select
ActiveCell.FormulaR1C1 = "=SCF_File!RC[-15]"
Range("AB2").AutoFill Destination:=Range("AB2:AB" & lastRow)

Range("AC2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-28],TWG_File!C[-28]:C[1],12,FALSE)"
Range("AC2").AutoFill Destination:=Range("AC2:AC" & lastRow)

Range("AD2").Select
ActiveCell.FormulaR1C1 = "=IF(AND(RC[-2]="""",RC[-1]=""""),"""",RC[-2]=RC[-1])"
Range("AD2").AutoFill Destination:=Range("AD2:AD" & lastRow)

Range("AE2").Select
ActiveCell.FormulaR1C1 = "=SCF_File!RC[-17]"
Range("AE2").AutoFill Destination:=Range("AE2:AE" & lastRow)

Range("AF2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-31],TWG_File!C[-31]:C[-2],16,FALSE)"
Range("AF2").AutoFill Destination:=Range("AF2:AF" & lastRow)

Range("AG2").Select
ActiveCell.FormulaR1C1 = "=IF(AND(RC[-2]="""",RC[-1]=""""),"""",RC[-2]=RC[-1])"
Range("AG2").AutoFill Destination:=Range("AG2:AG" & lastRow)

Range("AH2").Select
ActiveCell.FormulaR1C1 = "=SCF_File!RC[-19]"
Range("AH2").AutoFill Destination:=Range("AH2:AH" & lastRow)

Range("AI2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-34],TWG_File!C[-34]:C[-5],15,FALSE)"
Range("AI2").AutoFill Destination:=Range("AI2:AI" & lastRow)

Range("AJ2").Select
ActiveCell.FormulaR1C1 = "=IF(AND(RC[-2]="""",RC[-1]=""""),"""",RC[-2]=RC[-1])"
Range("AJ2").AutoFill Destination:=Range("AJ2:AJ" & lastRow)

Range("AK2").Select
ActiveCell.FormulaR1C1 = "=SCF_File!RC[-21]"
Range("AK2").AutoFill Destination:=Range("AK2:AK" & lastRow)

Range("AL2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-37],TWG_File!C[-37]:C[-8],14,FALSE)"
Range("AL2").AutoFill Destination:=Range("AL2:AL" & lastRow)

Range("AM2").Select
ActiveCell.FormulaR1C1 = "=SCF_File!RC[-22]"
Range("AM2").AutoFill Destination:=Range("AM2:AM" & lastRow)

Range("AN2").Select
ActiveCell.FormulaR1C1 = "=SCF_File!RC[-22]"
Range("AN2").AutoFill Destination:=Range("AN2:AN" & lastRow)

Range("AO2").Select
ActiveCell.FormulaR1C1 = "=SCF_File!RC[-22]"
Range("AO2").AutoFill Destination:=Range("AO2:AO" & lastRow)

Range("AP2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-41],TWG_File!C[-41]:C[-12],18,FALSE)"
Range("AP2").AutoFill Destination:=Range("AP2:AP" & lastRow)

Range("AQ2").Select
ActiveCell.FormulaR1C1 = "=IF(AND(RC[-2]="""",RC[-1]=""""),"""",RC[-2]=RC[-1])"
Range("AQ2").AutoFill Destination:=Range("AQ2:AQ" & lastRow)

Range("AR2").Select
ActiveCell.FormulaR1C1 = "=SCF_File!RC[-24]"
Range("AR2").AutoFill Destination:=Range("AR2:AR" & lastRow)

Range("AS2").Select
ActiveCell.FormulaR1C1 = "=IF(SCF_File!RC[-24]="""","""",SCF_File!RC[-24])"
Range("AS2").AutoFill Destination:=Range("AS2:AS" & lastRow)

Range("AT2").Select
ActiveCell.FormulaR1C1 = "=SCF_File!RC[-24]"
Range("AT2").AutoFill Destination:=Range("AT2:AT" & lastRow)

Range("AU2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-46],TWG_File!C[-46]:C[-17],19,FALSE)"
Range("AU2").AutoFill Destination:=Range("AU2:AU" & lastRow)

Range("AV2").Select
ActiveCell.FormulaR1C1 = "=IF(AND(RC[-2]="""",RC[-1]=""""),"""",RC[-2]=RC[-1])"
Range("AV2").AutoFill Destination:=Range("AV2:AV" & lastRow)

Range("AW2").Select
ActiveCell.FormulaR1C1 = "=SCF_File!RC[-26]"
Range("AW2").AutoFill Destination:=Range("AW2:AW" & lastRow)

Range("AX2").Select
ActiveCell.FormulaR1C1 = "=SCF_File!RC[-26]"
Range("AX2").AutoFill Destination:=Range("AX2:AX" & lastRow)

Range("AY2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-50],TWG_File!C[-50]:C[-21],21,FALSE)"
Range("AY2").AutoFill Destination:=Range("AY2:AY" & lastRow)

Range("AZ2").Select
ActiveCell.FormulaR1C1 = "=IF(AND(RC[-2]="""",RC[-1]=""""),"""",RC[-2]=RC[-1])"
Range("AZ2").AutoFill Destination:=Range("AZ2:AZ" & lastRow)

Range("BA2").Select
ActiveCell.FormulaR1C1 = "=IF(SCF_File!RC[-28]="""","""",SCF_File!RC[-28])"
Range("BA2").AutoFill Destination:=Range("BA2:BA" & lastRow)

Range("BB2").Select
ActiveCell.FormulaR1C1 = "=IF(SCF_File!RC[-28]="""","""",SCF_File!RC[-28])"
Range("BB2").AutoFill Destination:=Range("BB2:BB" & lastRow)

Range("BC2").Select
ActiveCell.FormulaR1C1 = "=IF(SCF_File!RC[-28]="""","""",SCF_File!RC[-28])"
Range("BC2").AutoFill Destination:=Range("BC2:BC" & lastRow)

Range("BD2").Select
ActiveCell.FormulaR1C1 = "=IF(SCF_File!RC[-28]="""","""",SCF_File!RC[-28])"
Range("BD2").AutoFill Destination:=Range("BD2:BD" & lastRow)

Range("BE2").Select
ActiveCell.FormulaR1C1 = "=IF(SCF_File!RC[-28]="""","""",SCF_File!RC[-28])"
Range("BE2").AutoFill Destination:=Range("BE2:BE" & lastRow)

Range("BF2").Select
ActiveCell.FormulaR1C1 = "=IF(SCF_File!RC[-29]="""","""",SCF_File!RC[-29])"
Range("BF2").AutoFill Destination:=Range("BF2:BF" & lastRow)

Range("BG2").Select
ActiveCell.FormulaR1C1 = "=IF(SCF_File!RC[-29]="""","""",SCF_File!RC[-29])"
Range("BG2").AutoFill Destination:=Range("BG2:BG" & lastRow)

Range("BH2").Select
ActiveCell.FormulaR1C1 = "=IF(SCF_File!RC[-29]="""","""",SCF_File!RC[-29])"
Range("BH2").AutoFill Destination:=Range("BH2:BH" & lastRow)

Range("BI2").Select
ActiveCell.FormulaR1C1 = "=IF(SCF_File!RC[-29]="""","""",SCF_File!RC[-29])"
Range("BI2").AutoFill Destination:=Range("BI2:BI" & lastRow)

Range("BJ2").Select
ActiveCell.FormulaR1C1 = "=IF(SCF_File!RC[-28]="""","""",SCF_File!RC[-28])"
Range("BJ2").AutoFill Destination:=Range("BJ2:BJ" & lastRow)

Range("BK2").Select
ActiveCell.FormulaR1C1 = "=IF(SCF_File!RC[-28]="""","""",SCF_File!RC[-28])"
Range("BK2").AutoFill Destination:=Range("BK2:BK" & lastRow)

Range("BL2").Select
ActiveCell.FormulaR1C1 = "=IF(SCF_File!RC[-28]="""","""",SCF_File!RC[-28])"
Range("BL2").AutoFill Destination:=Range("BL2:BL" & lastRow)

Range("BM2").Select
ActiveCell.FormulaR1C1 = "=IF(SCF_File!RC[-28]="""","""",SCF_File!RC[-28])"
Range("BM2").AutoFill Destination:=Range("BM2:BM" & lastRow)

Range("BN2").Select
ActiveCell.FormulaR1C1 = "=IF(SCF_File!RC[-28]="""","""",SCF_File!RC[-28])"
Range("BN2").AutoFill Destination:=Range("BN2:BN" & lastRow)

Range("BO2").Select
ActiveCell.FormulaR1C1 = "=IF(SCF_File!RC[-28]="""","""",SCF_File!RC[-28])"
Range("BO2").AutoFill Destination:=Range("BO2:BO" & lastRow)


MsgBox "Merge Check tab has now been updated"
End Sub
 
Upvote 0
Ok i immediately see a problem. You macro starts with:

Code: 
lastRow = Range("A" & Rows.Count).End(xlUp).Row
    
    Sheets("SCF_File").Select

Your lastRow variable is not necessarily looking at the sheet SCF_File and it needs to.

Code: 
lastRow = Sheets("SCF_File").Range("A" & Rows.Count).End(xlUp).Row
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
Autofill formula via VBA erroring
Replies
3
Views
379
Alex Blakenburg
A
G
  • Solved
Autofill to bottom of worksheet (macro not working)
Replies
1
Views
599
Artik
Artik
S
  • Solved
VBA autofilling code
Replies
6
Views
603
Peter_SSs
Peter_SSs
D
  • Solved
Dynamic AutoFill
2
Replies
18
Views
269
MARK858
MARK858
E
  • Solved
How to drag autofill from a set location a variable amount of cells away
2
Replies
15
Views
452
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

Share this page

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
Back
Top