AutoFill Destination - Query / Error

[JoeRooney]

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

 

[steve the fish]

Id imagine your issue is that:

[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]

 

[JoeRooney]

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

 

[steve the fish]

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.

 

[JoeRooney]

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 ?

 

[steve the fish]

Is that the entire code? If not attach the rest.

 

[steve the fish]

This is how id write it:

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

 

[JoeRooney]

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

 

[steve the fish]

Ok i immediately see a problem. You macro starts with:

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.

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

 

[JoeRooney]

Thanks a million Steve that has solved it.

Thank you for all your help.