Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Standard numbering
Hi,
I have a worksheet called '2. Template nummerlijst'. In this worksheet there are various columns (A:K) starting on row 9 (row 8 contains headers). I have 1 macro to copy information from a different sheet onto columns B:I. Columns J:K are blanks. The 2nd macro is used to copy a startdate form a single range somewhere else in the same workbook and also to calculate an certain amount to be credited per user. Because the information (size) of columns B:I is a variable I use the next code to perform the 2nd macro; Sub Startdate_Amount() 'Check if everything is filled in If Creditbedrag_per_aansluiting = Range("'2. Template nummerlijst'!N16") < 0 Then MsgBox "Vul eerst creditbedrag per aansluiting in" Else 'Copy down-date from rng Dim rng As Range Set rng = ActiveSheet.Range("J9") 'Get date Windows("Fin-Cont tool.xls").Activate Sheets("1. Checklist").Select Range("B12").Select Selection.Copy Windows("Fin-Cont tool.xls").Activate Sheets("2. Template nummerlijst").Select Range("J9").Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats 'copy date down active content rng.Copy Destination:=Range(rng, rng.Offset(0, -1).End(xlDown).Offset(0, 1)) 'adjust lining to cel J8 Range("J8").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With 'adjust alignment With Selection .HorizontalAlignment = xlRight .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With 'Macro_om_te_crediteren_bedrag_per_aansluiting_te_ genereren Dim rnga As Range Set rnga = ActiveSheet.Range("K9") 'Get amount Prorato = Range("'2. Template nummerlijst'!N15").Text Creditbedrag_per_aansluiting = Range("'2. Template nummerlijst'!N16").Value Looptijd_contract = Range("'1. Checklist'!B14").Value Looptijd_Gemini = Range("'2. Template nummerlijst'!I9").Value Te_crediteren = Range("'2. Template nummerlijst'!K9").Select If Prorato < "Yes" Then Range("'2. Template nummerlijst'!k9") = Range("'2. Template nummerlijst'!N16").Value Else Range("'2. Template nummerlijst'!k9") = (Creditbedrag_per_aansluiting / Looptijd_contract) * Looptijd_Gemini End If rnga.AutoFill Range(rnga, rnga.Offset(0, -1).End(xlDown).Offset(0, 1)) End If End Sub Now I want to use same sort of code to create a standard numbering starting from cel A9 to whatever the length of the list (columns B:I) is. I tried it with macro recorder, but because I need a variable I reckon this won't help because in the recorder the absolute cel reference is displayed. Someone have any suggestions?! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Standard numbering
I'm not sure what 'standard numbers' are but here's an example of filling in
1,2,3, etc down from A9 based on the length of entries in (for example) column C: Sub Example() FillNumbers Range("A9"), Range("C9") End Sub Sub FillNumbers(NumCell As Range, RefCell As Range) Dim NumRows As Integer NumCell.Value = 1 NumRows = Range(RefCell, RefCell.End(xlDown)).Rows.Count NumCell.AutoFill NumCell.Resize(NumRows), xlFillSeries End Sub Just for your information, this code: Windows("Fin-Cont tool.xls").Activate Sheets("1. Checklist").Select Range("B12").Select Selection.Copy Windows("Fin-Cont tool.xls").Activate Sheets("2. Template nummerlijst").Select Range("J9").Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats can be better written: Sheets("1. Checklist").Range("B12").Copy Sheets("2. Template nummerlijst").Range("J9").PasteSpecial Paste:=xlPasteValuesAndNumberFormats I'm assuming the workbook Fin-Cont tool.xls is already active. No need to select sheets or cells to copy from them. The Macro recorder produces a lot of junk. -- Jim "Basta1980" wrote in message ... | Hi, | | I have a worksheet called '2. Template nummerlijst'. In this worksheet there | are various columns (A:K) starting on row 9 (row 8 contains headers). I have | 1 macro to copy information from a different sheet onto columns B:I. Columns | J:K are blanks. The 2nd macro is used to copy a startdate form a single range | somewhere else in the same workbook and also to calculate an certain amount | to be credited per user. Because the information (size) of columns B:I is a | variable I use the next code to perform the 2nd macro; | | Sub Startdate_Amount() | | 'Check if everything is filled in | If Creditbedrag_per_aansluiting = Range("'2. Template nummerlijst'!N16") | < 0 Then | MsgBox "Vul eerst creditbedrag per aansluiting in" | Else | 'Copy down-date from rng | | Dim rng As Range | Set rng = ActiveSheet.Range("J9") | | 'Get date | Windows("Fin-Cont tool.xls").Activate | Sheets("1. Checklist").Select | Range("B12").Select | Selection.Copy | Windows("Fin-Cont tool.xls").Activate | Sheets("2. Template nummerlijst").Select | Range("J9").Select | Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats | | 'copy date down active content | rng.Copy Destination:=Range(rng, rng.Offset(0, -1).End(xlDown).Offset(0, 1)) | | 'adjust lining to cel J8 | Range("J8").Select | Selection.Borders(xlDiagonalDown).LineStyle = xlNone | Selection.Borders(xlDiagonalUp).LineStyle = xlNone | With Selection.Borders(xlEdgeLeft) | .LineStyle = xlContinuous | .Weight = xlMedium | .ColorIndex = xlAutomatic | End With | With Selection.Borders(xlEdgeTop) | .LineStyle = xlContinuous | .Weight = xlMedium | .ColorIndex = xlAutomatic | End With | With Selection.Borders(xlEdgeBottom) | .LineStyle = xlContinuous | .Weight = xlMedium | .ColorIndex = xlAutomatic | End With | With Selection.Borders(xlEdgeRight) | .LineStyle = xlContinuous | .Weight = xlMedium | .ColorIndex = xlAutomatic | End With | 'adjust alignment | With Selection | .HorizontalAlignment = xlRight | .VerticalAlignment = xlBottom | .WrapText = False | .Orientation = 0 | .AddIndent = False | .IndentLevel = 0 | .ShrinkToFit = False | .ReadingOrder = xlContext | .MergeCells = False | End With | | 'Macro_om_te_crediteren_bedrag_per_aansluiting_te_ genereren | Dim rnga As Range | Set rnga = ActiveSheet.Range("K9") | | 'Get amount | | Prorato = Range("'2. Template nummerlijst'!N15").Text | Creditbedrag_per_aansluiting = Range("'2. Template nummerlijst'!N16").Value | Looptijd_contract = Range("'1. Checklist'!B14").Value | Looptijd_Gemini = Range("'2. Template nummerlijst'!I9").Value | | Te_crediteren = Range("'2. Template nummerlijst'!K9").Select | If Prorato < "Yes" Then | Range("'2. Template nummerlijst'!k9") = Range("'2. Template | nummerlijst'!N16").Value | Else | Range("'2. Template nummerlijst'!k9") = (Creditbedrag_per_aansluiting / | Looptijd_contract) * Looptijd_Gemini | End If | | rnga.AutoFill Range(rnga, rnga.Offset(0, -1).End(xlDown).Offset(0, 1)) | End If | End Sub | | Now I want to use same sort of code to create a standard numbering starting | from cel A9 to whatever the length of the list (columns B:I) is. I tried it | with macro recorder, but because I need a variable I reckon this won't help | because in the recorder the absolute cel reference is displayed. Someone have | any suggestions?! | |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Standard numbering
Jim,
This is pretty much exactly what i was searchnig for. Thanks very much "Jim Rech" wrote: I'm not sure what 'standard numbers' are but here's an example of filling in 1,2,3, etc down from A9 based on the length of entries in (for example) column C: Sub Example() FillNumbers Range("A9"), Range("C9") End Sub Sub FillNumbers(NumCell As Range, RefCell As Range) Dim NumRows As Integer NumCell.Value = 1 NumRows = Range(RefCell, RefCell.End(xlDown)).Rows.Count NumCell.AutoFill NumCell.Resize(NumRows), xlFillSeries End Sub Just for your information, this code: Windows("Fin-Cont tool.xls").Activate Sheets("1. Checklist").Select Range("B12").Select Selection.Copy Windows("Fin-Cont tool.xls").Activate Sheets("2. Template nummerlijst").Select Range("J9").Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats can be better written: Sheets("1. Checklist").Range("B12").Copy Sheets("2. Template nummerlijst").Range("J9").PasteSpecial Paste:=xlPasteValuesAndNumberFormats I'm assuming the workbook Fin-Cont tool.xls is already active. No need to select sheets or cells to copy from them. The Macro recorder produces a lot of junk. -- Jim "Basta1980" wrote in message ... | Hi, | | I have a worksheet called '2. Template nummerlijst'. In this worksheet there | are various columns (A:K) starting on row 9 (row 8 contains headers). I have | 1 macro to copy information from a different sheet onto columns B:I. Columns | J:K are blanks. The 2nd macro is used to copy a startdate form a single range | somewhere else in the same workbook and also to calculate an certain amount | to be credited per user. Because the information (size) of columns B:I is a | variable I use the next code to perform the 2nd macro; | | Sub Startdate_Amount() | | 'Check if everything is filled in | If Creditbedrag_per_aansluiting = Range("'2. Template nummerlijst'!N16") | < 0 Then | MsgBox "Vul eerst creditbedrag per aansluiting in" | Else | 'Copy down-date from rng | | Dim rng As Range | Set rng = ActiveSheet.Range("J9") | | 'Get date | Windows("Fin-Cont tool.xls").Activate | Sheets("1. Checklist").Select | Range("B12").Select | Selection.Copy | Windows("Fin-Cont tool.xls").Activate | Sheets("2. Template nummerlijst").Select | Range("J9").Select | Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats | | 'copy date down active content | rng.Copy Destination:=Range(rng, rng.Offset(0, -1).End(xlDown).Offset(0, 1)) | | 'adjust lining to cel J8 | Range("J8").Select | Selection.Borders(xlDiagonalDown).LineStyle = xlNone | Selection.Borders(xlDiagonalUp).LineStyle = xlNone | With Selection.Borders(xlEdgeLeft) | .LineStyle = xlContinuous | .Weight = xlMedium | .ColorIndex = xlAutomatic | End With | With Selection.Borders(xlEdgeTop) | .LineStyle = xlContinuous | .Weight = xlMedium | .ColorIndex = xlAutomatic | End With | With Selection.Borders(xlEdgeBottom) | .LineStyle = xlContinuous | .Weight = xlMedium | .ColorIndex = xlAutomatic | End With | With Selection.Borders(xlEdgeRight) | .LineStyle = xlContinuous | .Weight = xlMedium | .ColorIndex = xlAutomatic | End With | 'adjust alignment | With Selection | .HorizontalAlignment = xlRight | .VerticalAlignment = xlBottom | .WrapText = False | .Orientation = 0 | .AddIndent = False | .IndentLevel = 0 | .ShrinkToFit = False | .ReadingOrder = xlContext | .MergeCells = False | End With | | 'Macro_om_te_crediteren_bedrag_per_aansluiting_te_ genereren | Dim rnga As Range | Set rnga = ActiveSheet.Range("K9") | | 'Get amount | | Prorato = Range("'2. Template nummerlijst'!N15").Text | Creditbedrag_per_aansluiting = Range("'2. Template nummerlijst'!N16").Value | Looptijd_contract = Range("'1. Checklist'!B14").Value | Looptijd_Gemini = Range("'2. Template nummerlijst'!I9").Value | | Te_crediteren = Range("'2. Template nummerlijst'!K9").Select | If Prorato < "Yes" Then | Range("'2. Template nummerlijst'!k9") = Range("'2. Template | nummerlijst'!N16").Value | Else | Range("'2. Template nummerlijst'!k9") = (Creditbedrag_per_aansluiting / | Looptijd_contract) * Looptijd_Gemini | End If | | rnga.AutoFill Range(rnga, rnga.Offset(0, -1).End(xlDown).Offset(0, 1)) | End If | End Sub | | Now I want to use same sort of code to create a standard numbering starting | from cel A9 to whatever the length of the list (columns B:I) is. I tried it | with macro recorder, but because I need a variable I reckon this won't help | because in the recorder the absolute cel reference is displayed. Someone have | any suggestions?! | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Standard deviation | Excel Discussion (Misc queries) | |||
PO with sequential numbering with start / end numbering | Excel Discussion (Misc queries) | |||
Changes to standard toolbar | Excel Discussion (Misc queries) | |||
Standard Deviation | Excel Worksheet Functions | |||
Standard Modules in VBA | Excel Programming |