vba auto fill issue

K

kelvin_9

Active Member
Joined
Mar 6, 2015
Messages
460
Office Version
  1. 2019
Hello All,

i have some data at sheet1 that i use code to divert in different columns at sheet3 and auto fill down to last row if any
however this will come with an error #1004 on below columns IF i have only 1 row data
what should i do to avoid any of this case?

VBA Code: 
    Sheets("Sheet3").Select
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]+2"
    Selection.AutoFill Destination:=Range("B2:B" & Range("F" & Rows.Count).End(xlUp).Row)
    Range("D2").Select
    ActiveCell.FormulaR1C1 = "987654"
    Selection.AutoFill Destination:=Range("D2:D" & Range("F" & Rows.Count).End(xlUp).Row)
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "Japan"
    Selection.AutoFill Destination:=Range("E2:E" & Range("F" & Rows.Count).End(xlUp).Row)
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "=CONCATENATE(Sheet2!RC[12],"" "",Sheet2!RC[11])"
    Selection.AutoFill Destination:=Range("G2:G" & Range("F" & Rows.Count).End(xlUp).Row)

thank you very much for your help
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
It looks like you are trying to use column F to find the last row with data.
Try this:
VBA Code: 
    Sheets("Sheet3").Select
    
'   Find last row in column F with data
    lr = Cells(Rows.Count, "F").End(xlUp).Row

'   Enter formulas
    Range("B2:B" & lr).FormulaR1C1 = "=RC[-1]+2"
    Range("D2:D" & lr).Value = "987654"
    Range("E2:E" & lr).Value = "Japan"
    Range("G2:G" & lr).FormulaR1C1 = "=CONCATENATE(Sheet2!RC[12],"" "",Sheet2!RC[11])"
 
Upvote 0
Solution
Joe4 said:
It looks like you are trying to use column F to find the last row with data.
Try this:
VBA Code: 
    Sheets("Sheet3").Select
   
'   Find last row in column F with data
    lr = Cells(Rows.Count, "F").End(xlUp).Row

'   Enter formulas
    Range("B2:B" & lr).FormulaR1C1 = "=RC[-1]+2"
    Range("D2:D" & lr).Value = "987654"
    Range("E2:E" & lr).Value = "Japan"
    Range("G2:G" & lr).FormulaR1C1 = "=CONCATENATE(Sheet2!RC[12],"" "",Sheet2!RC[11])"
Click to expand...
Joe4
thank you very much for your reply

this works great, but i have another code get some mistake
i have another LR although i can stop it with '
but column A 1 cell up after below code, what should i amend?

VBA Code: 
    'fill date
    Sheets("Sheet3").Select
    'Dim Lr As Long
    Lr = Range("A" & Rows.Count).End(xlUp).Row
    Range("A" & Lr, Range("B" & Rows.Count).End(xlUp).Offset(, -1)).FillDown
 
Upvote 0
I am not sure I understand that you are asking, but your last line of code looks very wrong. I don't think you have built a valid range reference.
Can you just explain (in plain English, exactly what it is you are trying to do there)?
 
Upvote 0
Joe4 said:
I am not sure I understand that you are asking, but your last line of code looks very wrong. I don't think you have built a valid range reference.
Can you just explain (in plain English, exactly what it is you are trying to do there)?
Click to expand...
sorry for my bad english

step 1, i try to copy data from sheet 1 to sheet 3 and auto fill every column to last row base on column F
step 2, i need to run 2 times this code and fianlly move to a new workbook, save individually, the first time is fine but the second time i found A1 get filled and it should caused of below code? so i modified my code again

VBA Code: 
    'delete object and move
    Sheets("Sheet3").Select
    ActiveSheet.Copy
    Sheets("Sheet3").Name = "normal home"
    ActiveSheet.DrawingObjects.Select
    Selection.Delete
 
Upvote 0
kelvin_9 said:
sorry for my bad english

step 1, i try to copy data from sheet 1 to sheet 3 and auto fill every column to last row base on column F
step 2, i need to run 2 times this code and fianlly move to a new workbook, save individually, the first time is fine but the second time i found A1 get filled and it should caused of below code? so i modified my code again

VBA Code: 
    'delete object and move
    Sheets("Sheet3").Select
    ActiveSheet.Copy
    Sheets("Sheet3").Name = "normal home"
    ActiveSheet.DrawingObjects.Select
    Selection.Delete
Click to expand...
I am sorry, I am now even more confused.
I do not see what this has to do with the code you posted in your last post.

Note the method that I was showing you in my initial reply.
If you are placing the same value or formula in every cell in a column, you do not need to use FillDown.
You simply find the last row you want to apply it to, and then apply the formula to the whole column all at once, like I did.

If you do have another question that does NOT pertain to this FillDown/formula population (but to something different), then it should be posted to a new thread (as it would be a new question).
 
Upvote 0
Joe4 said:
I am sorry, I am now even more confused.
I do not see what this has to do with the code you posted in your last post.

Note the method that I was showing you in my initial reply.
If you are placing the same value or formula in every cell in a column, you do not need to use FillDown.
You simply find the last row you want to apply it to, and then apply the formula to the whole column all at once, like I did.

If you do have another question that does NOT pertain to this FillDown/formula population (but to something different), then it should be posted to a new thread (as it would be a new question).
Click to expand...
sorry

can i share my code for you here ?
 
Upvote 0
If it is in relation to your original question, then yes, but an explanation along with that code is necessary.
If it is a new question (even if it is on the same project), then it should be posted in a new thread.
 
Upvote 0
Joe4 said:
If it is in relation to your original question, then yes, but an explanation along with that code is necessary.
If it is a new question (even if it is on the same project), then it should be posted in a new thread.
Click to expand...
i need to key in a date in A2 at sheet 3 and run the code, the first time is fine
i try the second time, key in a date in A2 at sheet 3 as first time what i do, A2 get filled and i guess my code get something wrong with this

this should be the final code, thanks

VBA Code: 
    'fill date
    Lr = WS_3.Range("A" & Rows.Count).End(xlUp).Row
    WS_3.Range("A" & Lr, WS_3.Range("B" & Rows.Count).End(xlUp).Offset(, -1)).FillDown

Code: 
Option Explicit
Sub Macro1()
'
'
'

'

'NORMAL HOME

Dim WS_1 As Worksheet
    Set WS_1 = Sheets("Sheet1")
Dim WS_2 As Worksheet
    Set WS_2 = Sheets("Sheet2")
Dim WS_3 As Worksheet
    Set WS_3 = Sheets("Sheet3")
Dim Lr As Long

    'filter store and location
    WS_1.Activate
    WS_1.Rows("1:1").AutoFilter
    WS_1.Range("$A$1:$V$1").AutoFilter Field:=2, Criteria1:="NORMAL"
    WS_1.Range("$A$1:$V$1").AutoFilter Field:=10, Criteria1:="="
    Cells.Copy
    WS_2.Activate
    WS_2.Paste

    'back to worksheet 1
    WS_1.Activate
    WS_1.Range("A1").AutoFilter

    'copy order id
    WS_2.Activate
    WS_2.Range("A2", Range("A2").End(xlDown)).Copy
    WS_3.Activate
    WS_3.Range("F2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

   'Find last row in column F with data
    Lr = Cells(Rows.Count, "F").End(xlUp).Row
    WS_3.Range("B2:B" & Lr).FormulaR1C1 = "=RC[-1]+2"
    WS_3.Range("D2:D" & Lr).Value = "987654"
    WS_3.Range("E2:E" & Lr).Value = "Japan"
    WS_3.Range("G2:G" & Lr).FormulaR1C1 = "=CONCATENATE(Sheet2!RC[12],"" "",Sheet2!RC[11])"

    'fill address
    WS_2.Activate
    WS_2.Range("V2:W1000").Copy
    WS_3.Activate
    WS_3.Range("H2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    'fill phone
    WS_2.Activate
    WS_2.Range("U2:U1000").Copy
    WS_3.Activate
    WS_3.Range("L2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    WS_2.Activate
    WS_2.Range("I2:I1000").Copy
    WS_3.Activate
    WS_3.Range("M2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    WS_3.Range("Q2:Q" & Lr).Value = "box"
    WS_3.Range("R2:R" & Lr).Value = "1"
    WS_3.Range("S2:S" & Lr).Value = "3"

    'fill date
    Lr = WS_3.Range("A" & Rows.Count).End(xlUp).Row
    WS_3.Range("A" & Lr, WS_3.Range("B" & Rows.Count).End(xlUp).Offset(, -1)).FillDown

    'value
    WS_3.Cells.Copy
    WS_3.Cells.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

    'clear data in sheet2 if any
    WS_2.Activate
    WS_2.Cells.Delete Shift:=xlUp
    WS_2.Range("A1").Select
    WS_1.Activate
    WS_1.Cells.Select
    WS_1.Cells.EntireColumn.AutoFit
    WS_1.Range("A1").Select

    'delete object and move
    WS_3.Activate
    WS_3.Copy
    Sheets("Sheet3").Name = "normal home"
    ActiveSheet.DrawingObjects.Select
    Selection.Delete

    'clear data in sheet3 if any
    WS_3.Rows("2:1000").Delete
    With WS_3.Range("A2").Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.499984740745262
        .PatternTintAndShade = 0
    End With

End Sub
 
Upvote 0
I don't quite understand what you are trying to do with this part here:
VBA Code: 
    'fill date
    Lr = WS_3.Range("A" & Rows.Count).End(xlUp).Row
    WS_3.Range("A" & Lr, WS_3.Range("B" & Rows.Count).End(xlUp).Offset(, -1)).FillDown

Firstly, we already calculated "Lr" earlier using column F. Can we still use this last row value, or does it need to be re-calculated again?
Since we seem to be trying to fill column A, that would seem to suggest that column A is blank, so I don't understand how we can then use column A to find the last row to fill down to.
What column should we look at to determine exactly how far down we need to fill column A?
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
VBA autofilling code
Replies
6
Views
445
Peter_SSs
Peter_SSs
Sean15
  • Question Question
Could you help improve recorded macro
2
Replies
13
Views
412
Sean15
Sean15
T
  • Question Question
ActiveSheet. Paste error
Replies
1
Views
335
RoryA
RoryA
D
  • Solved
AutoFill or Copy Formula to All Relevant Cells
Replies
5
Views
689
deZine
D
J
  • Question Question
VBA removing heading in spreadsheet
Replies
3
Views
239
rlv01
R

Forum statistics

Threads
1,223,243
Messages
6,170,967
Members
452,371
Latest member
Frana

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