Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy forumla down
I have a user form for several columns of informaition and I have formulas in
others that I need to calculate with each new entry. Could someone please advise how I can do this - my s/sheet ranges from A:AQ and the formulas are in various different columns so there is no suitable range. Having read through the previous posts I have found the following post which seems like it would solve my problem but does not- Assume the formulas are in row 1 Sub ABC() Dim rng as Range Set rng = Cells(Rows.Count, "K").End(xlUp) Range("L1:N1").AutoFill Range("L1:N1").Resize(rng.Row, 3) End Sub -- Regards, Tom Ogilvy "WolfgangPD" wrote: I have a macro set-up to copy information into columns A:K. This information will change each time. There is a formula in L,M and N. I would like a macro that would copy the formulas down to the final row of the info in column K. Any suggestions? -- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy forumla down
You'll still need to pick out a column that can be used to find that last row to
be filled. Tom used column A. Sub ABC() dim LastRow as long with worksheets("Sheet1") 'change IV to the column that can be used to find that last row lastrow = .cells(.rows.count,"IV").end(xlup).row 'just repeat this for each set of columns that has formulas 'it can be a single column or multiple columns. .Range("A1:c1").AutoFill .Range("A1:c1").Resize(lastrow) .Range("f1").AutoFill .Range("f1").Resize(lastrow) .Range("AA1:Aq1").AutoFill .Range("Aa1:aq1").Resize(lastrow) End Sub Leanne M (Aussie) wrote: I have a user form for several columns of informaition and I have formulas in others that I need to calculate with each new entry. Could someone please advise how I can do this - my s/sheet ranges from A:AQ and the formulas are in various different columns so there is no suitable range. Having read through the previous posts I have found the following post which seems like it would solve my problem but does not- Assume the formulas are in row 1 Sub ABC() Dim rng as Range Set rng = Cells(Rows.Count, "K").End(xlUp) Range("L1:N1").AutoFill Range("L1:N1").Resize(rng.Row, 3) End Sub -- Regards, Tom Ogilvy "WolfgangPD" wrote: I have a macro set-up to copy information into columns A:K. This information will change each time. There is a formula in L,M and N. I would like a macro that would copy the formulas down to the final row of the info in column K. Any suggestions? -- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy forumla down
Thanks Dave - As always a great help!
-- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) "Dave Peterson" wrote: You'll still need to pick out a column that can be used to find that last row to be filled. Tom used column A. Sub ABC() dim LastRow as long with worksheets("Sheet1") 'change IV to the column that can be used to find that last row lastrow = .cells(.rows.count,"IV").end(xlup).row 'just repeat this for each set of columns that has formulas 'it can be a single column or multiple columns. .Range("A1:c1").AutoFill .Range("A1:c1").Resize(lastrow) .Range("f1").AutoFill .Range("f1").Resize(lastrow) .Range("AA1:Aq1").AutoFill .Range("Aa1:aq1").Resize(lastrow) End Sub Leanne M (Aussie) wrote: I have a user form for several columns of informaition and I have formulas in others that I need to calculate with each new entry. Could someone please advise how I can do this - my s/sheet ranges from A:AQ and the formulas are in various different columns so there is no suitable range. Having read through the previous posts I have found the following post which seems like it would solve my problem but does not- Assume the formulas are in row 1 Sub ABC() Dim rng as Range Set rng = Cells(Rows.Count, "K").End(xlUp) Range("L1:N1").AutoFill Range("L1:N1").Resize(rng.Row, 3) End Sub -- Regards, Tom Ogilvy "WolfgangPD" wrote: I have a macro set-up to copy information into columns A:K. This information will change each time. There is a formula in L,M and N. I would like a macro that would copy the formulas down to the final row of the info in column K. Any suggestions? -- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy forumla down
Hi Dave,
This is the code I have - Sub ABC() Dim LastRow As Long With Worksheets("List") 'change IV to the column that can be used to find that last row LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row 'just repeat this for each set of columns that has formulas 'it can be a single column or multiple columns. .Range("A1").AutoFill .Range("A1").Resize(LastRow) .Range("F1").AutoFill .Range("F1").Resize(LastRow) .Range("L1").AutoFill .Range("L1").Resize(LastRow) .Range("Y1:AA1").AutoFill .Range("Y1:AA1").Resize(LastRow) .Range("AD1:AG1").AutoFill .Range("AD1:AG1").Resize(LastRow) .Range("AJ1").AutoFill .Range("AJ1").Resize(LastRow) .Range("AQ1").AutoFill .Range("AQ1").Resize(LastRow) End Sub I thought it had worked but it does not appear to. I have also tried a different column to identify last row but still does not work. I do not know if it makes a difference or not however I have the following code at the start of my user form - 'find next empty row iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row Please help a struggling code novice. -- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) "Dave Peterson" wrote: You'll still need to pick out a column that can be used to find that last row to be filled. Tom used column A. Sub ABC() dim LastRow as long with worksheets("Sheet1") 'change IV to the column that can be used to find that last row lastrow = .cells(.rows.count,"IV").end(xlup).row 'just repeat this for each set of columns that has formulas 'it can be a single column or multiple columns. .Range("A1:c1").AutoFill .Range("A1:c1").Resize(lastrow) .Range("f1").AutoFill .Range("f1").Resize(lastrow) .Range("AA1:Aq1").AutoFill .Range("Aa1:aq1").Resize(lastrow) End Sub Leanne M (Aussie) wrote: I have a user form for several columns of informaition and I have formulas in others that I need to calculate with each new entry. Could someone please advise how I can do this - my s/sheet ranges from A:AQ and the formulas are in various different columns so there is no suitable range. Having read through the previous posts I have found the following post which seems like it would solve my problem but does not- Assume the formulas are in row 1 Sub ABC() Dim rng as Range Set rng = Cells(Rows.Count, "K").End(xlUp) Range("L1:N1").AutoFill Range("L1:N1").Resize(rng.Row, 3) End Sub -- Regards, Tom Ogilvy "WolfgangPD" wrote: I have a macro set-up to copy information into columns A:K. This information will change each time. There is a formula in L,M and N. I would like a macro that would copy the formulas down to the final row of the info in column K. Any suggestions? -- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy forumla down
There was an "end with" missing.
But I'm not sure how you can use column A to determine how far the formula in A1 should be copied down. I bet that there's plain old text in one of those columns -- so you can use that column to determine the number of rows to fill down. This suggestion: iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row is the equivalent of: LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 And that means you would go down one row further that the row with the last used value in that important column. (If you didn't start in row 1, then the ..resize() portion would be different.) Sub ABC() Dim LastRow As Long With Worksheets("List") 'change IV to the column that can be used to find that last row LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row 'just repeat this for each set of columns that has formulas 'it can be a single column or multiple columns. .Range("A1").AutoFill .Range("A1").Resize(LastRow) .Range("F1").AutoFill .Range("F1").Resize(LastRow) .Range("L1").AutoFill .Range("L1").Resize(LastRow) .Range("Y1:AA1").AutoFill .Range("Y1:AA1").Resize(LastRow) .Range("AD1:AG1").AutoFill .Range("AD1:AG1").Resize(LastRow) .Range("AJ1").AutoFill .Range("AJ1").Resize(LastRow) .Range("AQ1").AutoFill .Range("AQ1").Resize(LastRow) end with '<-- added End Sub Leanne M (Aussie) wrote: Hi Dave, This is the code I have - Sub ABC() Dim LastRow As Long With Worksheets("List") 'change IV to the column that can be used to find that last row LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row 'just repeat this for each set of columns that has formulas 'it can be a single column or multiple columns. .Range("A1").AutoFill .Range("A1").Resize(LastRow) .Range("F1").AutoFill .Range("F1").Resize(LastRow) .Range("L1").AutoFill .Range("L1").Resize(LastRow) .Range("Y1:AA1").AutoFill .Range("Y1:AA1").Resize(LastRow) .Range("AD1:AG1").AutoFill .Range("AD1:AG1").Resize(LastRow) .Range("AJ1").AutoFill .Range("AJ1").Resize(LastRow) .Range("AQ1").AutoFill .Range("AQ1").Resize(LastRow) End Sub I thought it had worked but it does not appear to. I have also tried a different column to identify last row but still does not work. I do not know if it makes a difference or not however I have the following code at the start of my user form - 'find next empty row iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row Please help a struggling code novice. -- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) "Dave Peterson" wrote: You'll still need to pick out a column that can be used to find that last row to be filled. Tom used column A. Sub ABC() dim LastRow as long with worksheets("Sheet1") 'change IV to the column that can be used to find that last row lastrow = .cells(.rows.count,"IV").end(xlup).row 'just repeat this for each set of columns that has formulas 'it can be a single column or multiple columns. .Range("A1:c1").AutoFill .Range("A1:c1").Resize(lastrow) .Range("f1").AutoFill .Range("f1").Resize(lastrow) .Range("AA1:Aq1").AutoFill .Range("Aa1:aq1").Resize(lastrow) End Sub Leanne M (Aussie) wrote: I have a user form for several columns of informaition and I have formulas in others that I need to calculate with each new entry. Could someone please advise how I can do this - my s/sheet ranges from A:AQ and the formulas are in various different columns so there is no suitable range. Having read through the previous posts I have found the following post which seems like it would solve my problem but does not- Assume the formulas are in row 1 Sub ABC() Dim rng as Range Set rng = Cells(Rows.Count, "K").End(xlUp) Range("L1:N1").AutoFill Range("L1:N1").Resize(rng.Row, 3) End Sub -- Regards, Tom Ogilvy "WolfgangPD" wrote: I have a macro set-up to copy information into columns A:K. This information will change each time. There is a formula in L,M and N. I would like a macro that would copy the formulas down to the final row of the info in column K. Any suggestions? -- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy forumla down
Hi Dave,
Yes the End With is there just didnt copy into post. I have tried both column D and column A. When column D didnt work (this column is the first on that is filled in by the user form) I think I am getting confused and conflicted with the fact that I need to find the next blank row twice yet I need it to be the same blank row. What I mean is that when the user form is initiated it needs to find the next blank row to copy the information from the user form to the sheet. I then need that recently completed row to have the formulas in other columns populated. I think this is where I am going wrong. -- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) "Dave Peterson" wrote: There was an "end with" missing. But I'm not sure how you can use column A to determine how far the formula in A1 should be copied down. I bet that there's plain old text in one of those columns -- so you can use that column to determine the number of rows to fill down. This suggestion: iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row is the equivalent of: LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 And that means you would go down one row further that the row with the last used value in that important column. (If you didn't start in row 1, then the ..resize() portion would be different.) Sub ABC() Dim LastRow As Long With Worksheets("List") 'change IV to the column that can be used to find that last row LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row 'just repeat this for each set of columns that has formulas 'it can be a single column or multiple columns. .Range("A1").AutoFill .Range("A1").Resize(LastRow) .Range("F1").AutoFill .Range("F1").Resize(LastRow) .Range("L1").AutoFill .Range("L1").Resize(LastRow) .Range("Y1:AA1").AutoFill .Range("Y1:AA1").Resize(LastRow) .Range("AD1:AG1").AutoFill .Range("AD1:AG1").Resize(LastRow) .Range("AJ1").AutoFill .Range("AJ1").Resize(LastRow) .Range("AQ1").AutoFill .Range("AQ1").Resize(LastRow) end with '<-- added End Sub Leanne M (Aussie) wrote: Hi Dave, This is the code I have - Sub ABC() Dim LastRow As Long With Worksheets("List") 'change IV to the column that can be used to find that last row LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row 'just repeat this for each set of columns that has formulas 'it can be a single column or multiple columns. .Range("A1").AutoFill .Range("A1").Resize(LastRow) .Range("F1").AutoFill .Range("F1").Resize(LastRow) .Range("L1").AutoFill .Range("L1").Resize(LastRow) .Range("Y1:AA1").AutoFill .Range("Y1:AA1").Resize(LastRow) .Range("AD1:AG1").AutoFill .Range("AD1:AG1").Resize(LastRow) .Range("AJ1").AutoFill .Range("AJ1").Resize(LastRow) .Range("AQ1").AutoFill .Range("AQ1").Resize(LastRow) End Sub I thought it had worked but it does not appear to. I have also tried a different column to identify last row but still does not work. I do not know if it makes a difference or not however I have the following code at the start of my user form - 'find next empty row iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row Please help a struggling code novice. -- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) "Dave Peterson" wrote: You'll still need to pick out a column that can be used to find that last row to be filled. Tom used column A. Sub ABC() dim LastRow as long with worksheets("Sheet1") 'change IV to the column that can be used to find that last row lastrow = .cells(.rows.count,"IV").end(xlup).row 'just repeat this for each set of columns that has formulas 'it can be a single column or multiple columns. .Range("A1:c1").AutoFill .Range("A1:c1").Resize(lastrow) .Range("f1").AutoFill .Range("f1").Resize(lastrow) .Range("AA1:Aq1").AutoFill .Range("Aa1:aq1").Resize(lastrow) End Sub Leanne M (Aussie) wrote: I have a user form for several columns of informaition and I have formulas in others that I need to calculate with each new entry. Could someone please advise how I can do this - my s/sheet ranges from A:AQ and the formulas are in various different columns so there is no suitable range. Having read through the previous posts I have found the following post which seems like it would solve my problem but does not- Assume the formulas are in row 1 Sub ABC() Dim rng as Range Set rng = Cells(Rows.Count, "K").End(xlUp) Range("L1:N1").AutoFill Range("L1:N1").Resize(rng.Row, 3) End Sub -- Regards, Tom Ogilvy "WolfgangPD" wrote: I have a macro set-up to copy information into columns A:K. This information will change each time. There is a formula in L,M and N. I would like a macro that would copy the formulas down to the final row of the info in column K. Any suggestions? -- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy forumla down
You find the next blank row to determine where to put the data from the
userform. So you can either use that in your code. Or you can find the last used row (in column D, the text column) to use in the code. They should be equivalent. For instance: dim NextRow as long Dim LastRow as long with worksheets("Sheet9999") nextrow = .cells(.rows.count,"D").end(xlup).row + 1 'fill the cell with the value from the userform .cells(nextrow, "D").value = "something non-empty" end with 'then later... with worksheets("Sheet9999") lastrow = .cells(.rows.count,"D").end(xlup).row 'copy down formulas end with msgbox nextrow & vblf & lastrow These two numbers will be the same--if you haven't done something else to the data. Leanne M (Aussie) wrote: Hi Dave, Yes the End With is there just didnt copy into post. I have tried both column D and column A. When column D didnt work (this column is the first on that is filled in by the user form) I think I am getting confused and conflicted with the fact that I need to find the next blank row twice yet I need it to be the same blank row. What I mean is that when the user form is initiated it needs to find the next blank row to copy the information from the user form to the sheet. I then need that recently completed row to have the formulas in other columns populated. I think this is where I am going wrong. -- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) "Dave Peterson" wrote: There was an "end with" missing. But I'm not sure how you can use column A to determine how far the formula in A1 should be copied down. I bet that there's plain old text in one of those columns -- so you can use that column to determine the number of rows to fill down. This suggestion: iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row is the equivalent of: LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 And that means you would go down one row further that the row with the last used value in that important column. (If you didn't start in row 1, then the ..resize() portion would be different.) Sub ABC() Dim LastRow As Long With Worksheets("List") 'change IV to the column that can be used to find that last row LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row 'just repeat this for each set of columns that has formulas 'it can be a single column or multiple columns. .Range("A1").AutoFill .Range("A1").Resize(LastRow) .Range("F1").AutoFill .Range("F1").Resize(LastRow) .Range("L1").AutoFill .Range("L1").Resize(LastRow) .Range("Y1:AA1").AutoFill .Range("Y1:AA1").Resize(LastRow) .Range("AD1:AG1").AutoFill .Range("AD1:AG1").Resize(LastRow) .Range("AJ1").AutoFill .Range("AJ1").Resize(LastRow) .Range("AQ1").AutoFill .Range("AQ1").Resize(LastRow) end with '<-- added End Sub Leanne M (Aussie) wrote: Hi Dave, This is the code I have - Sub ABC() Dim LastRow As Long With Worksheets("List") 'change IV to the column that can be used to find that last row LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row 'just repeat this for each set of columns that has formulas 'it can be a single column or multiple columns. .Range("A1").AutoFill .Range("A1").Resize(LastRow) .Range("F1").AutoFill .Range("F1").Resize(LastRow) .Range("L1").AutoFill .Range("L1").Resize(LastRow) .Range("Y1:AA1").AutoFill .Range("Y1:AA1").Resize(LastRow) .Range("AD1:AG1").AutoFill .Range("AD1:AG1").Resize(LastRow) .Range("AJ1").AutoFill .Range("AJ1").Resize(LastRow) .Range("AQ1").AutoFill .Range("AQ1").Resize(LastRow) End Sub I thought it had worked but it does not appear to. I have also tried a different column to identify last row but still does not work. I do not know if it makes a difference or not however I have the following code at the start of my user form - 'find next empty row iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row Please help a struggling code novice. -- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) "Dave Peterson" wrote: You'll still need to pick out a column that can be used to find that last row to be filled. Tom used column A. Sub ABC() dim LastRow as long with worksheets("Sheet1") 'change IV to the column that can be used to find that last row lastrow = .cells(.rows.count,"IV").end(xlup).row 'just repeat this for each set of columns that has formulas 'it can be a single column or multiple columns. .Range("A1:c1").AutoFill .Range("A1:c1").Resize(lastrow) .Range("f1").AutoFill .Range("f1").Resize(lastrow) .Range("AA1:Aq1").AutoFill .Range("Aa1:aq1").Resize(lastrow) End Sub Leanne M (Aussie) wrote: I have a user form for several columns of informaition and I have formulas in others that I need to calculate with each new entry. Could someone please advise how I can do this - my s/sheet ranges from A:AQ and the formulas are in various different columns so there is no suitable range. Having read through the previous posts I have found the following post which seems like it would solve my problem but does not- Assume the formulas are in row 1 Sub ABC() Dim rng as Range Set rng = Cells(Rows.Count, "K").End(xlUp) Range("L1:N1").AutoFill Range("L1:N1").Resize(rng.Row, 3) End Sub -- Regards, Tom Ogilvy "WolfgangPD" wrote: I have a macro set-up to copy information into columns A:K. This information will change each time. There is a formula in L,M and N. I would like a macro that would copy the formulas down to the final row of the info in column K. Any suggestions? -- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert rows and copy forumla above it | Excel Discussion (Misc queries) | |||
Copy & Paste Forumla - but reference cell is changing | Excel Discussion (Misc queries) | |||
If Forumla | Excel Discussion (Misc queries) | |||
Forumla | Excel Discussion (Misc queries) | |||
paste a forumla and copy with a macro | Excel Programming |