|
More to Error in code
Sorry for the mess up.
Hi! Can someone please help me with my code? I have tried every possible way (I know) to get this to work and I am I completely frustrated. I am using the code for a summary page. My page varies everytime I import information so I want to use 'LastRow' in my code because I want the code to go to the last row of a certain column and use that value. I am going to try to explain as much as I can. I have 3 columns on my summary page. Column A is blank and to be used for calculations (percentage) Column B has descriptions in it (all text) Column F has numeric valuesin it. It is used for cost for the description in Column B. A B F xyz Company 12000.00 abc Company 24000.00 def Company 12000.00 48000.00 (total) I want column A to be a percentage using take C1 divided by C4, C2/C4 and so on. The only problem is that I may have four rows in my summary this month, but next month I may have 24 rows. I have listed my code below, but It doesn't seem to work. Any suggestions? Sorry it is so long! Thanks in advance for any help given. Happy Halloween! ' ' Adjust/Formulas Macro ' Macro recorded 10/29/2003 by Julie ' ' Dim LastRow As Long Dim wks As Worksheet Dim Row_Num As Integer Dim Head_Num As Integer Dim i Range("F6").Select Set wks = ActiveSheet Row_Num = ActiveCell.Row LastRow = Cells(Cells.Rows.Count, "F").End(xlUp).Row Head_Num = 6 i = Row_Num Range("F6").Select Do Until i Head_Num With wks .Range("A6").FormulaR1C1 = "=RC[5]/" & LastRow .Range("A6").AutoFill _ Destination:=Range("A6:A" & LastRow), Type:=xlFillCopy .Range("A7").FormulaR1C1 = "=RC[5]/" & LastRow .Range("A7").AutoFill _ Destination:=.Range("A7:A" & LastRow), Type:=xlFillDefault .Range("A8").FormulaR1C1 = "=RC[5]/" & LastRow .Range("A8").AutoFill _ Destination:=.Range("A8:A" & LastRow), Type:=xlFillDefault .Range("A9").FormulaR1C1 = "=RC[5]/" & LastRow .Range("A9").AutoFill _ Destination:=.Range("A9:A" & LastRow), Type:=xlFillDefault .Range("A10").FormulaR1C1 = "=RC[5]/" & LastRow .Range("A10").AutoFill _ Destination:=.Range("A10:A" & LastRow), Type:=xlFillDefault .Range("A11").FormulaR1C1 = "=RC[5]/" & LastRow .Range("A11").AutoFill _ Destination:=.Range("A11:A" & LastRow), Type:=xlFillDefault .Range("A12").FormulaR1C1 = "=RC[5]/" & LastRow .Range("A12").AutoFill _ Destination:=.Range("A12:A" & LastRow), Type:=xlFillDefault .Range("A13").FormulaR1C1 = "=RC[5]/" & LastRow .Range("A13").AutoFill _ Destination:=.Range("A13:A" & LastRow), Type:=xlFillDefault .Range("A14").FormulaR1C1 = "=RC[5]/" & LastRow .Range("A14").AutoFill _ Destination:=.Range("A14:A" & LastRow), Type:=xlFillDefault .Range("A15").FormulaR1C1 = "=RC[5]/" & LastRow .Range("A15").AutoFill _ Destination:=.Range("A15:A" & LastRow), Type:=xlFillDefault .Range("A16").FormulaR1C1 = "=RC[5]/" & LastRow .Range("A16").AutoFill _ Destination:=.Range("A16:A" & LastRow), Type:=xlFillDefault .Range("A17").FormulaR1C1 = "=RC[5]/" & LastRow .Range("A17").AutoFill _ Destination:=.Range("A17:A" & LastRow), Type:=xlFillDefault .Range("A18").FormulaR1C1 = "=RC[5]/" & LastRow .Range("A18").AutoFill _ Destination:=.Range("A18:A" & LastRow), Type:=xlFillDefault .Range("A19").FormulaR1C1 = "=RC[5]/" & LastRow .Range("A19").AutoFill _ Destination:=.Range("A19:A" & LastRow), Type:=xlFillDefault .Range("A20").FormulaR1C1 = "=RC[5]/" & LastRow .Range("A20").AutoFill _ Destination:=.Range("A20:A" & LastRow), Type:=xlFillDefault End With i = i - 1 Loop I also tried to define last row this way: LastRow = Range("F:F").SpecialCell(xlCellTypeLastCell).Row Thanks, Julie |
More to Error in code
Julie,
What you are trying to do seems very similar to what I have just finished, but I am lost what is in columns C & id C4 your last colum you want to use? Cheers Mark "Julie" wrote in message ... Sorry for the mess up. Hi! Can someone please help me with my code? I have tried every possible way (I know) to get this to work and I am I completely frustrated. I am using the code for a summary page. My page varies everytime I import information so I want to use 'LastRow' in my code because I want the code to go to the last row of a certain column and use that value. I am going to try to explain as much as I can. I have 3 columns on my summary page. Column A is blank and to be used for calculations (percentage) Column B has descriptions in it (all text) Column F has numeric valuesin it. It is used for cost for the description in Column B. A B F xyz Company 12000.00 abc Company 24000.00 def Company 12000.00 48000.00 (total) I want column A to be a percentage using take C1 divided by C4, C2/C4 and so on. The only problem is that I may have four rows in my summary this month, but next month I may have 24 rows. I have listed my code below, but It doesn't seem to work. Any suggestions? Sorry it is so long! Thanks in advance for any help given. Happy Halloween! ' ' Adjust/Formulas Macro ' Macro recorded 10/29/2003 by Julie ' ' Dim LastRow As Long Dim wks As Worksheet Dim Row_Num As Integer Dim Head_Num As Integer Dim i Range("F6").Select Set wks = ActiveSheet Row_Num = ActiveCell.Row LastRow = Cells(Cells.Rows.Count, "F").End(xlUp).Row Head_Num = 6 i = Row_Num Range("F6").Select Do Until i Head_Num With wks .Range("A6").FormulaR1C1 = "=RC[5]/" & LastRow .Range("A6").AutoFill _ Destination:=Range("A6:A" & LastRow), Type:=xlFillCopy .Range("A7").FormulaR1C1 = "=RC[5]/" & LastRow .Range("A7").AutoFill _ Destination:=.Range("A7:A" & LastRow), Type:=xlFillDefault .Range("A8").FormulaR1C1 = "=RC[5]/" & LastRow .Range("A8").AutoFill _ Destination:=.Range("A8:A" & LastRow), Type:=xlFillDefault .Range("A9").FormulaR1C1 = "=RC[5]/" & LastRow .Range("A9").AutoFill _ Destination:=.Range("A9:A" & LastRow), Type:=xlFillDefault .Range("A10").FormulaR1C1 = "=RC[5]/" & LastRow .Range("A10").AutoFill _ Destination:=.Range("A10:A" & LastRow), Type:=xlFillDefault .Range("A11").FormulaR1C1 = "=RC[5]/" & LastRow .Range("A11").AutoFill _ Destination:=.Range("A11:A" & LastRow), Type:=xlFillDefault .Range("A12").FormulaR1C1 = "=RC[5]/" & LastRow .Range("A12").AutoFill _ Destination:=.Range("A12:A" & LastRow), Type:=xlFillDefault .Range("A13").FormulaR1C1 = "=RC[5]/" & LastRow .Range("A13").AutoFill _ Destination:=.Range("A13:A" & LastRow), Type:=xlFillDefault .Range("A14").FormulaR1C1 = "=RC[5]/" & LastRow .Range("A14").AutoFill _ Destination:=.Range("A14:A" & LastRow), Type:=xlFillDefault .Range("A15").FormulaR1C1 = "=RC[5]/" & LastRow .Range("A15").AutoFill _ Destination:=.Range("A15:A" & LastRow), Type:=xlFillDefault .Range("A16").FormulaR1C1 = "=RC[5]/" & LastRow .Range("A16").AutoFill _ Destination:=.Range("A16:A" & LastRow), Type:=xlFillDefault .Range("A17").FormulaR1C1 = "=RC[5]/" & LastRow .Range("A17").AutoFill _ Destination:=.Range("A17:A" & LastRow), Type:=xlFillDefault .Range("A18").FormulaR1C1 = "=RC[5]/" & LastRow .Range("A18").AutoFill _ Destination:=.Range("A18:A" & LastRow), Type:=xlFillDefault .Range("A19").FormulaR1C1 = "=RC[5]/" & LastRow .Range("A19").AutoFill _ Destination:=.Range("A19:A" & LastRow), Type:=xlFillDefault .Range("A20").FormulaR1C1 = "=RC[5]/" & LastRow .Range("A20").AutoFill _ Destination:=.Range("A20:A" & LastRow), Type:=xlFillDefault End With i = i - 1 Loop I also tried to define last row this way: LastRow = Range("F:F").SpecialCell(xlCellTypeLastCell).Row Thanks, Julie |
More to Error in code
Mark, thanks for your response! Columns C, D, and E
contain miscellaneous information that I am not using, but I want to keep for others to use if it is needed. Regarding the C4, I forgot to change that to F4. I believe that you are talking about the following: I want column A to be a percentage using take C1 divided by C4, C2/C4 and so on. The only problem is that I may have four rows in my summary this month, but next month I may have 24 rows. Instead of that I want to...... I want column A to be a percentage using take F1 divided by F4, F2/F4 and so on. The only problem is in this case I have 4 rows in my summary, but next month I may have 24 rows. Thanks for any suggestions that you may have. If you need anymore information, please let me know. Thanks again! Julie -----Original Message----- Julie, What you are trying to do seems very similar to what I have just finished, but I am lost what is in columns C & id C4 your last colum you want to use? Cheers Mark "Julie" wrote in message ... Sorry for the mess up. Hi! Can someone please help me with my code? I have tried every possible way (I know) to get this to work and I am I completely frustrated. I am using the code for a summary page. My page varies everytime I import information so I want to use 'LastRow' in my code because I want the code to go to the last row of a certain column and use that value. I am going to try to explain as much as I can. I have 3 columns on my summary page. Column A is blank and to be used for calculations (percentage) Column B has descriptions in it (all text) Column F has numeric valuesin it. It is used for cost for the description in Column B. A B F xyz Company 12000.00 abc Company 24000.00 def Company 12000.00 48000.00 (total) I want column A to be a percentage using take C1 divided by C4, C2/C4 and so on. The only problem is that I may have four rows in my summary this month, but next month I may have 24 rows. I have listed my code below, but It doesn't seem to work. Any suggestions? Sorry it is so long! Thanks in advance for any help given. Happy Halloween! ' ' Adjust/Formulas Macro ' Macro recorded 10/29/2003 by Julie ' ' Dim LastRow As Long Dim wks As Worksheet Dim Row_Num As Integer Dim Head_Num As Integer Dim i Range("F6").Select Set wks = ActiveSheet Row_Num = ActiveCell.Row LastRow = Cells(Cells.Rows.Count, "F").End (xlUp).Row Head_Num = 6 i = Row_Num Range("F6").Select Do Until i Head_Num With wks .Range("A6").FormulaR1C1 = "=RC[5]/" & LastRow .Range("A6").AutoFill _ Destination:=Range("A6:A" & LastRow), Type:=xlFillCopy .Range("A7").FormulaR1C1 = "=RC[5]/" & LastRow .Range("A7").AutoFill _ Destination:=.Range("A7:A" & LastRow), Type:=xlFillDefault .Range("A8").FormulaR1C1 = "=RC[5]/" & LastRow .Range("A8").AutoFill _ Destination:=.Range("A8:A" & LastRow), Type:=xlFillDefault .Range("A9").FormulaR1C1 = "=RC[5]/" & LastRow .Range("A9").AutoFill _ Destination:=.Range("A9:A" & LastRow), Type:=xlFillDefault .Range("A10").FormulaR1C1 = "=RC[5]/" & LastRow .Range("A10").AutoFill _ Destination:=.Range("A10:A" & LastRow), Type:=xlFillDefault .Range("A11").FormulaR1C1 = "=RC[5]/" & LastRow .Range("A11").AutoFill _ Destination:=.Range("A11:A" & LastRow), Type:=xlFillDefault .Range("A12").FormulaR1C1 = "=RC[5]/" & LastRow .Range("A12").AutoFill _ Destination:=.Range("A12:A" & LastRow), Type:=xlFillDefault .Range("A13").FormulaR1C1 = "=RC[5]/" & LastRow .Range("A13").AutoFill _ Destination:=.Range("A13:A" & LastRow), Type:=xlFillDefault .Range("A14").FormulaR1C1 = "=RC[5]/" & LastRow .Range("A14").AutoFill _ Destination:=.Range("A14:A" & LastRow), Type:=xlFillDefault .Range("A15").FormulaR1C1 = "=RC[5]/" & LastRow .Range("A15").AutoFill _ Destination:=.Range("A15:A" & LastRow), Type:=xlFillDefault .Range("A16").FormulaR1C1 = "=RC[5]/" & LastRow .Range("A16").AutoFill _ Destination:=.Range("A16:A" & LastRow), Type:=xlFillDefault .Range("A17").FormulaR1C1 = "=RC[5]/" & LastRow .Range("A17").AutoFill _ Destination:=.Range("A17:A" & LastRow), Type:=xlFillDefault .Range("A18").FormulaR1C1 = "=RC[5]/" & LastRow .Range("A18").AutoFill _ Destination:=.Range("A18:A" & LastRow), Type:=xlFillDefault .Range("A19").FormulaR1C1 = "=RC[5]/" & LastRow .Range("A19").AutoFill _ Destination:=.Range("A19:A" & LastRow), Type:=xlFillDefault .Range("A20").FormulaR1C1 = "=RC[5]/" & LastRow .Range("A20").AutoFill _ Destination:=.Range("A20:A" & LastRow), Type:=xlFillDefault End With i = i - 1 Loop I also tried to define last row this way: LastRow = Range("F:F").SpecialCell (xlCellTypeLastCell).Row Thanks, Julie . |
More to Error in code
Hi Dave,
Your example is what I needed! You don't know how many things that I have tried to get this to work. If it weren't for your example, I would still be searching on a way to get this done. THANK YOU SO MUCH! I hope that you have a great day! Julie -----Original Message----- maybe: Option Explicit Sub testme() Dim LastRow As Long With Worksheets("sheet1") LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row With .Range("a1:a" & LastRow - 1) .Formula = "=c1/c$" & LastRow .Value = .Value End With End With End Sub I put that .value = .value to convert the formulas to values. Delete that line if you want to keep the formula. Julie wrote: Sorry for the mess up. Hi! Can someone please help me with my code? I have tried every possible way (I know) to get this to work and I am I completely frustrated. I am using the code for a summary page. My page varies everytime I import information so I want to use 'LastRow' in my code because I want the code to go to the last row of a certain column and use that value. I am going to try to explain as much as I can. I have 3 columns on my summary page. Column A is blank and to be used for calculations (percentage) Column B has descriptions in it (all text) Column F has numeric valuesin it. It is used for cost for the description in Column B. A B F xyz Company 12000.00 abc Company 24000.00 def Company 12000.00 48000.00 (total) I want column A to be a percentage using take C1 divided by C4, C2/C4 and so on. The only problem is that I may have four rows in my summary this month, but next month I may have 24 rows. I have listed my code below, but It doesn't seem to work. Any suggestions? Sorry it is so long! Thanks in advance for any help given. Happy Halloween! ' ' Adjust/Formulas Macro ' Macro recorded 10/29/2003 by Julie ' ' Dim LastRow As Long Dim wks As Worksheet Dim Row_Num As Integer Dim Head_Num As Integer Dim i Range("F6").Select Set wks = ActiveSheet Row_Num = ActiveCell.Row LastRow = Cells(Cells.Rows.Count, "F").End (xlUp).Row Head_Num = 6 i = Row_Num Range("F6").Select Do Until i Head_Num With wks .Range("A6").FormulaR1C1 = "=RC[5]/" & LastRow .Range("A6").AutoFill _ Destination:=Range("A6:A" & LastRow), Type:=xlFillCopy .Range("A7").FormulaR1C1 = "=RC[5]/" & LastRow .Range("A7").AutoFill _ Destination:=.Range("A7:A" & LastRow), Type:=xlFillDefault .Range("A8").FormulaR1C1 = "=RC[5]/" & LastRow .Range("A8").AutoFill _ Destination:=.Range("A8:A" & LastRow), Type:=xlFillDefault .Range("A9").FormulaR1C1 = "=RC[5]/" & LastRow .Range("A9").AutoFill _ Destination:=.Range("A9:A" & LastRow), Type:=xlFillDefault .Range("A10").FormulaR1C1 = "=RC[5]/" & LastRow .Range("A10").AutoFill _ Destination:=.Range("A10:A" & LastRow), Type:=xlFillDefault .Range("A11").FormulaR1C1 = "=RC[5]/" & LastRow .Range("A11").AutoFill _ Destination:=.Range("A11:A" & LastRow), Type:=xlFillDefault .Range("A12").FormulaR1C1 = "=RC[5]/" & LastRow .Range("A12").AutoFill _ Destination:=.Range("A12:A" & LastRow), Type:=xlFillDefault .Range("A13").FormulaR1C1 = "=RC[5]/" & LastRow .Range("A13").AutoFill _ Destination:=.Range("A13:A" & LastRow), Type:=xlFillDefault .Range("A14").FormulaR1C1 = "=RC[5]/" & LastRow .Range("A14").AutoFill _ Destination:=.Range("A14:A" & LastRow), Type:=xlFillDefault .Range("A15").FormulaR1C1 = "=RC[5]/" & LastRow .Range("A15").AutoFill _ Destination:=.Range("A15:A" & LastRow), Type:=xlFillDefault .Range("A16").FormulaR1C1 = "=RC[5]/" & LastRow .Range("A16").AutoFill _ Destination:=.Range("A16:A" & LastRow), Type:=xlFillDefault .Range("A17").FormulaR1C1 = "=RC[5]/" & LastRow .Range("A17").AutoFill _ Destination:=.Range("A17:A" & LastRow), Type:=xlFillDefault .Range("A18").FormulaR1C1 = "=RC[5]/" & LastRow .Range("A18").AutoFill _ Destination:=.Range("A18:A" & LastRow), Type:=xlFillDefault .Range("A19").FormulaR1C1 = "=RC[5]/" & LastRow .Range("A19").AutoFill _ Destination:=.Range("A19:A" & LastRow), Type:=xlFillDefault .Range("A20").FormulaR1C1 = "=RC[5]/" & LastRow .Range("A20").AutoFill _ Destination:=.Range("A20:A" & LastRow), Type:=xlFillDefault End With i = i - 1 Loop I also tried to define last row this way: LastRow = Range("F:F").SpecialCell (xlCellTypeLastCell).Row Thanks, Julie -- Dave Peterson . |
| All times are GMT +1. The time now is 03:53 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com