| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Macros in excel 2000
I have recorded a macro to convert a report sent to us by another department.
This involves inserting extra columns to use formulas to convert the data. However as the amount of rows varies with the amount of data, how do i get the macro to work by going to the last active cell in a column? It is the first long macro i have written so any help would be much appreciated. I have included the macro to help. Sub CRMReportMacro() ' ' CRMReportMacro Macro ' Macro recorded 11/08/2005 by Administrator ' ' Keyboard Shortcut: Ctrl+j ' Range("H2").Select Selection.EntireColumn.Insert Selection.EntireColumn.Insert Selection.EntireColumn.Insert Selection.EntireColumn.Insert Selection.EntireColumn.Insert Range("H1").Select ActiveCell.FormulaR1C1 = "Time Req" Range("I1").Select ActiveCell.FormulaR1C1 = "Time Closed" Range("L1").Select ActiveCell.FormulaR1C1 = "Time In Hours" Range("F2:F1101").Select Selection.Copy ActiveWindow.SmallScroll Down:=-27 ActiveWindow.LargeScroll Down:=-34 Range("H3").Select ActiveWindow.SmallScroll Down:=-6 Range("H2").Select ActiveSheet.Paste Range("G2:G1101").Select Application.CutCopyMode = False Selection.Copy ActiveWindow.LargeScroll Down:=-33 ActiveWindow.ScrollRow = 2 Range("I2").Select ActiveSheet.Paste Application.CutCopyMode = False ActiveWorkbook.Save Range("H:I,L:L").Select Range("L1").Activate Selection.NumberFormat = "h:mm" Range("L2").Select ActiveCell.FormulaR1C1 = "=SUM(RC[-3]-RC[-4])" Selection.Copy Range("L3:L1101").Select ActiveSheet.Paste ActiveWindow.SmallScroll Down:=-39 ActiveWindow.LargeScroll Down:=-33 ActiveWindow.ScrollRow = 2 Application.CutCopyMode = False ActiveWorkbook.Save Range("K2").Select ActiveCell.FormulaR1C1 = "DAYS" Range("K2").Select Selection.Copy Range("K3:K1101").Select ActiveSheet.Paste ActiveWindow.SmallScroll Down:=-9 Columns("K:K").ColumnWidth = 5.29 ActiveWindow.LargeScroll Down:=-35 Application.CutCopyMode = False ActiveWorkbook.Save Range("J1").Select ActiveCell.FormulaR1C1 = "Net-Work Days" Range("J2").Select ActiveCell.FormulaR1C1 = "=NETWORKDAYS(RC[-4],RC[-3])" Columns("J:J").Select Selection.NumberFormat = "General" Range("J2").Select Selection.Copy Range("J3:J1101").Select ActiveSheet.Paste Application.CutCopyMode = False ActiveWorkbook.Save ActiveWindow.LargeScroll Down:=-35 Columns("J:J").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _ Formula1:="3" Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="3" With Selection.FormatConditions(2).Font .Bold = True .Italic = False .ColorIndex = 3 End With ActiveWindow.SmallScroll Down:=546 ActiveWindow.LargeScroll Down:=16 ActiveWindow.ScrollRow = 1071 ActiveWindow.SmallScroll Down:=15 Range("I1102").Select ActiveCell.FormulaR1C1 = "Over 2 Days" Range("I1103").Select ActiveCell.FormulaR1C1 = "Under 2 Days" Range("J1102").Select ActiveCell.FormulaR1C1 = "=COUNTIF(R[-1100]C:R[-1]C,""3"")" Application.Run Range("AUTOSAVE.XLA!mcs02.OnTime") ActiveWindow.SmallScroll Down:=12 Range("J1103").Select ActiveCell.FormulaR1C1 = "=COUNTIF(R[-1101]C:R[-2]C,""<=3"")" ActiveWindow.SmallScroll Down:=12 Range("J1102:J1103").Select With Selection.Font .Name = "Arial" .FontStyle = "Bold" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With ActiveWindow.SmallScroll Down:=-45 Columns("L:L").Select With Selection .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .MergeCells = False End With End Sub |
|
#2
|
|||
|
|||
|
I've tried to tidy it up as well, but untested I am afraid.
Sub CRMReportMacro() ' ' CRMReportMacro Macro ' Macro recorded 11/08/2005 by Administrator ' ' Keyboard Shortcut: Ctrl+j ' Dim iLastRow As Long Columns("H:L").Insert Range("H1").Value = "Time Req" Range("I1").Value = "Time Closed" Range("L1").Value = "Time In Hours" Range("F2:F1101").Copy Range("H2") Range("G2:G1101").Copy Range("I2") Application.CutCopyMode = False ActiveWorkbook.Save Range("L1").NumberFormat = "h:mm" iLastRow = Cells(Rows.Count, "L").End(xlUp).Row With Range("L2") .FormulaR1C1 = "=SUM(RC[-3]-RC[-4])" .AutoFill Range("L3:L" & iLastRow) End With ActiveWorkbook.Save Range("K2").FormulaR1C1 = "DAYS" Range("K2").Copy Range("K3:K1" & iLastRow) Columns("K:K").ColumnWidth = 5.29 Range("J1").FormulaR1C1 = "Net-Work Days" Range("J2").FormulaR1C1 = "=NETWORKDAYS(RC[-4],RC[-3])" Columns("J:J").NumberFormat = "General" Range("J2").Copy Range("J3:J" & iLastRow) With Columns("J:J") With .FormatConditions .Delete .FormatConditions.Add Type:=xlCellValue, _ Operator:=xlLessEqual, _ Formula1:="3" .FormatConditions.Add Type:=xlCellValue, _ Operator:=xlLessEqual, _ Formula1:="3" End With With .FormatConditions(2).Font .Bold = True .Italic = False .ColorIndex = 3 End With End With Range("I1102").Value = "Over 2 Days" Range("I1103").Value = "Under 2 Days" Range("J1102").FormulaR1C1 = "=COUNTIF(R[-" & iLastRow - 1 & "]C:R[-1]C,""3"")" Application.Run Range("AUTOSAVE.XLA!mcs02.OnTime") Range("J1103").FormulaR1C1 = "=COUNTIF(R[-" & iLastRow & "]C:R[-2]C,""<=3"")" With Range("J1102:J1103").Font .Name = "Arial" .FontStyle = "Bold" .Size = 10 End With With Columns("L:L") .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom End With End Sub -- HTH RP (remove nothere from the email address if mailing direct) "traineeross" wrote in message ... I have recorded a macro to convert a report sent to us by another department. This involves inserting extra columns to use formulas to convert the data. However as the amount of rows varies with the amount of data, how do i get the macro to work by going to the last active cell in a column? It is the first long macro i have written so any help would be much appreciated. I have included the macro to help. Sub CRMReportMacro() ' ' CRMReportMacro Macro ' Macro recorded 11/08/2005 by Administrator ' ' Keyboard Shortcut: Ctrl+j ' Range("H2").Select Selection.EntireColumn.Insert Selection.EntireColumn.Insert Selection.EntireColumn.Insert Selection.EntireColumn.Insert Selection.EntireColumn.Insert Range("H1").Select ActiveCell.FormulaR1C1 = "Time Req" Range("I1").Select ActiveCell.FormulaR1C1 = "Time Closed" Range("L1").Select ActiveCell.FormulaR1C1 = "Time In Hours" Range("F2:F1101").Select Selection.Copy ActiveWindow.SmallScroll Down:=-27 ActiveWindow.LargeScroll Down:=-34 Range("H3").Select ActiveWindow.SmallScroll Down:=-6 Range("H2").Select ActiveSheet.Paste Range("G2:G1101").Select Application.CutCopyMode = False Selection.Copy ActiveWindow.LargeScroll Down:=-33 ActiveWindow.ScrollRow = 2 Range("I2").Select ActiveSheet.Paste Application.CutCopyMode = False ActiveWorkbook.Save Range("H:I,L:L").Select Range("L1").Activate Selection.NumberFormat = "h:mm" Range("L2").Select ActiveCell.FormulaR1C1 = "=SUM(RC[-3]-RC[-4])" Selection.Copy Range("L3:L1101").Select ActiveSheet.Paste ActiveWindow.SmallScroll Down:=-39 ActiveWindow.LargeScroll Down:=-33 ActiveWindow.ScrollRow = 2 Application.CutCopyMode = False ActiveWorkbook.Save Range("K2").Select ActiveCell.FormulaR1C1 = "DAYS" Range("K2").Select Selection.Copy Range("K3:K1101").Select ActiveSheet.Paste ActiveWindow.SmallScroll Down:=-9 Columns("K:K").ColumnWidth = 5.29 ActiveWindow.LargeScroll Down:=-35 Application.CutCopyMode = False ActiveWorkbook.Save Range("J1").Select ActiveCell.FormulaR1C1 = "Net-Work Days" Range("J2").Select ActiveCell.FormulaR1C1 = "=NETWORKDAYS(RC[-4],RC[-3])" Columns("J:J").Select Selection.NumberFormat = "General" Range("J2").Select Selection.Copy Range("J3:J1101").Select ActiveSheet.Paste Application.CutCopyMode = False ActiveWorkbook.Save ActiveWindow.LargeScroll Down:=-35 Columns("J:J").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _ Formula1:="3" Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="3" With Selection.FormatConditions(2).Font .Bold = True .Italic = False .ColorIndex = 3 End With ActiveWindow.SmallScroll Down:=546 ActiveWindow.LargeScroll Down:=16 ActiveWindow.ScrollRow = 1071 ActiveWindow.SmallScroll Down:=15 Range("I1102").Select ActiveCell.FormulaR1C1 = "Over 2 Days" Range("I1103").Select ActiveCell.FormulaR1C1 = "Under 2 Days" Range("J1102").Select ActiveCell.FormulaR1C1 = "=COUNTIF(R[-1100]C:R[-1]C,""3"")" Application.Run Range("AUTOSAVE.XLA!mcs02.OnTime") ActiveWindow.SmallScroll Down:=12 Range("J1103").Select ActiveCell.FormulaR1C1 = "=COUNTIF(R[-1101]C:R[-2]C,""<=3"")" ActiveWindow.SmallScroll Down:=12 Range("J1102:J1103").Select With Selection.Font .Name = "Arial" .FontStyle = "Bold" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With ActiveWindow.SmallScroll Down:=-45 Columns("L:L").Select With Selection .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .MergeCells = False End With End Sub |
|
#3
|
|||
|
|||
|
Sorry missed a bit
Sub CRMReportMacro() ' ' CRMReportMacro Macro ' Macro recorded 11/08/2005 by Administrator ' ' Keyboard Shortcut: Ctrl+j ' Dim iLastRow As Long Columns("H:L").Insert Range("H1").Value = "Time Req" Range("I1").Value = "Time Closed" Range("L1").Value = "Time In Hours" iLastRow = Cells(Rows.Count, "F").End(xlUp).Row Range("F2:F" & iLastRow).Copy Range("H2") Range("G2:G" & iLastRow).Copy Range("I2") Application.CutCopyMode = False Range("L1").NumberFormat = "h:mm" With Range("L2") .FormulaR1C1 = "=SUM(RC[-3]-RC[-4])" .AutoFill Range("L3:L" & iLastRow) End With ActiveWorkbook.Save Range("K2").FormulaR1C1 = "DAYS" Range("K2").Copy Range("K3:K1" & iLastRow) Columns("K:K").ColumnWidth = 5.29 Range("J1").FormulaR1C1 = "Net-Work Days" Range("J2").FormulaR1C1 = "=NETWORKDAYS(RC[-4],RC[-3])" Columns("J:J").NumberFormat = "General" Range("J2").Copy Range("J3:J" & iLastRow) With Columns("J:J") With .FormatConditions .Delete .FormatConditions.Add Type:=xlCellValue, _ Operator:=xlLessEqual, _ Formula1:="3" .FormatConditions.Add Type:=xlCellValue, _ Operator:=xlLessEqual, _ Formula1:="3" End With With .FormatConditions(2).Font .Bold = True .Italic = False .ColorIndex = 3 End With End With Range("I1102").Value = "Over 2 Days" Range("I1103").Value = "Under 2 Days" Range("J1102").FormulaR1C1 = "=COUNTIF(R[-" & iLastRow - 1 & "]C:R[-1]C,""3"")" Application.Run Range("AUTOSAVE.XLA!mcs02.OnTime") Range("J1103").FormulaR1C1 = "=COUNTIF(R[-" & iLastRow & "]C:R[-2]C,""<=3"")" With Range("J1102:J1103").Font .Name = "Arial" .FontStyle = "Bold" .Size = 10 End With With Columns("L:L") .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom End With Application.CutCopyMode = False ActiveWorkbook.Save End Sub -- HTH RP (remove nothere from the email address if mailing direct) "traineeross" wrote in message ... I have recorded a macro to convert a report sent to us by another department. This involves inserting extra columns to use formulas to convert the data. However as the amount of rows varies with the amount of data, how do i get the macro to work by going to the last active cell in a column? It is the first long macro i have written so any help would be much appreciated. I have included the macro to help. Sub CRMReportMacro() ' ' CRMReportMacro Macro ' Macro recorded 11/08/2005 by Administrator ' ' Keyboard Shortcut: Ctrl+j ' Range("H2").Select Selection.EntireColumn.Insert Selection.EntireColumn.Insert Selection.EntireColumn.Insert Selection.EntireColumn.Insert Selection.EntireColumn.Insert Range("H1").Select ActiveCell.FormulaR1C1 = "Time Req" Range("I1").Select ActiveCell.FormulaR1C1 = "Time Closed" Range("L1").Select ActiveCell.FormulaR1C1 = "Time In Hours" Range("F2:F1101").Select Selection.Copy ActiveWindow.SmallScroll Down:=-27 ActiveWindow.LargeScroll Down:=-34 Range("H3").Select ActiveWindow.SmallScroll Down:=-6 Range("H2").Select ActiveSheet.Paste Range("G2:G1101").Select Application.CutCopyMode = False Selection.Copy ActiveWindow.LargeScroll Down:=-33 ActiveWindow.ScrollRow = 2 Range("I2").Select ActiveSheet.Paste Application.CutCopyMode = False ActiveWorkbook.Save Range("H:I,L:L").Select Range("L1").Activate Selection.NumberFormat = "h:mm" Range("L2").Select ActiveCell.FormulaR1C1 = "=SUM(RC[-3]-RC[-4])" Selection.Copy Range("L3:L1101").Select ActiveSheet.Paste ActiveWindow.SmallScroll Down:=-39 ActiveWindow.LargeScroll Down:=-33 ActiveWindow.ScrollRow = 2 Application.CutCopyMode = False ActiveWorkbook.Save Range("K2").Select ActiveCell.FormulaR1C1 = "DAYS" Range("K2").Select Selection.Copy Range("K3:K1101").Select ActiveSheet.Paste ActiveWindow.SmallScroll Down:=-9 Columns("K:K").ColumnWidth = 5.29 ActiveWindow.LargeScroll Down:=-35 Application.CutCopyMode = False ActiveWorkbook.Save Range("J1").Select ActiveCell.FormulaR1C1 = "Net-Work Days" Range("J2").Select ActiveCell.FormulaR1C1 = "=NETWORKDAYS(RC[-4],RC[-3])" Columns("J:J").Select Selection.NumberFormat = "General" Range("J2").Select Selection.Copy Range("J3:J1101").Select ActiveSheet.Paste Application.CutCopyMode = False ActiveWorkbook.Save ActiveWindow.LargeScroll Down:=-35 Columns("J:J").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _ Formula1:="3" Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="3" With Selection.FormatConditions(2).Font .Bold = True .Italic = False .ColorIndex = 3 End With ActiveWindow.SmallScroll Down:=546 ActiveWindow.LargeScroll Down:=16 ActiveWindow.ScrollRow = 1071 ActiveWindow.SmallScroll Down:=15 Range("I1102").Select ActiveCell.FormulaR1C1 = "Over 2 Days" Range("I1103").Select ActiveCell.FormulaR1C1 = "Under 2 Days" Range("J1102").Select ActiveCell.FormulaR1C1 = "=COUNTIF(R[-1100]C:R[-1]C,""3"")" Application.Run Range("AUTOSAVE.XLA!mcs02.OnTime") ActiveWindow.SmallScroll Down:=12 Range("J1103").Select ActiveCell.FormulaR1C1 = "=COUNTIF(R[-1101]C:R[-2]C,""<=3"")" ActiveWindow.SmallScroll Down:=12 Range("J1102:J1103").Select With Selection.Font .Name = "Arial" .FontStyle = "Bold" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With ActiveWindow.SmallScroll Down:=-45 Columns("L:L").Select With Selection .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .MergeCells = False End With End Sub |
|
#4
|
|||
|
|||
|
Thank-you very much for your help so far, it is much appreciated. However I
just tried to run it and i got a message saying compile error: syntax error and this row was highlighted, Range("J1102").FormulaR1C1 = "=COUNTIF(R[-" & iLastRow - 1 & Hopefully you'll understand this. Many thanks Jody Williams "Bob Phillips" wrote: Sorry missed a bit Sub CRMReportMacro() ' ' CRMReportMacro Macro ' Macro recorded 11/08/2005 by Administrator ' ' Keyboard Shortcut: Ctrl+j ' Dim iLastRow As Long Columns("H:L").Insert Range("H1").Value = "Time Req" Range("I1").Value = "Time Closed" Range("L1").Value = "Time In Hours" iLastRow = Cells(Rows.Count, "F").End(xlUp).Row Range("F2:F" & iLastRow).Copy Range("H2") Range("G2:G" & iLastRow).Copy Range("I2") Application.CutCopyMode = False Range("L1").NumberFormat = "h:mm" With Range("L2") .FormulaR1C1 = "=SUM(RC[-3]-RC[-4])" .AutoFill Range("L3:L" & iLastRow) End With ActiveWorkbook.Save Range("K2").FormulaR1C1 = "DAYS" Range("K2").Copy Range("K3:K1" & iLastRow) Columns("K:K").ColumnWidth = 5.29 Range("J1").FormulaR1C1 = "Net-Work Days" Range("J2").FormulaR1C1 = "=NETWORKDAYS(RC[-4],RC[-3])" Columns("J:J").NumberFormat = "General" Range("J2").Copy Range("J3:J" & iLastRow) With Columns("J:J") With .FormatConditions .Delete .FormatConditions.Add Type:=xlCellValue, _ Operator:=xlLessEqual, _ Formula1:="3" .FormatConditions.Add Type:=xlCellValue, _ Operator:=xlLessEqual, _ Formula1:="3" End With With .FormatConditions(2).Font .Bold = True .Italic = False .ColorIndex = 3 End With End With Range("I1102").Value = "Over 2 Days" Range("I1103").Value = "Under 2 Days" Range("J1102").FormulaR1C1 = "=COUNTIF(R[-" & iLastRow - 1 & "]C:R[-1]C,""3"")" Application.Run Range("AUTOSAVE.XLA!mcs02.OnTime") Range("J1103").FormulaR1C1 = "=COUNTIF(R[-" & iLastRow & "]C:R[-2]C,""<=3"")" With Range("J1102:J1103").Font .Name = "Arial" .FontStyle = "Bold" .Size = 10 End With With Columns("L:L") .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom End With Application.CutCopyMode = False ActiveWorkbook.Save End Sub -- HTH RP (remove nothere from the email address if mailing direct) "traineeross" wrote in message ... I have recorded a macro to convert a report sent to us by another department. This involves inserting extra columns to use formulas to convert the data. However as the amount of rows varies with the amount of data, how do i get the macro to work by going to the last active cell in a column? It is the first long macro i have written so any help would be much appreciated. I have included the macro to help. Sub CRMReportMacro() ' ' CRMReportMacro Macro ' Macro recorded 11/08/2005 by Administrator ' ' Keyboard Shortcut: Ctrl+j ' Range("H2").Select Selection.EntireColumn.Insert Selection.EntireColumn.Insert Selection.EntireColumn.Insert Selection.EntireColumn.Insert Selection.EntireColumn.Insert Range("H1").Select ActiveCell.FormulaR1C1 = "Time Req" Range("I1").Select ActiveCell.FormulaR1C1 = "Time Closed" Range("L1").Select ActiveCell.FormulaR1C1 = "Time In Hours" Range("F2:F1101").Select Selection.Copy ActiveWindow.SmallScroll Down:=-27 ActiveWindow.LargeScroll Down:=-34 Range("H3").Select ActiveWindow.SmallScroll Down:=-6 Range("H2").Select ActiveSheet.Paste Range("G2:G1101").Select Application.CutCopyMode = False Selection.Copy ActiveWindow.LargeScroll Down:=-33 ActiveWindow.ScrollRow = 2 Range("I2").Select ActiveSheet.Paste Application.CutCopyMode = False ActiveWorkbook.Save Range("H:I,L:L").Select Range("L1").Activate Selection.NumberFormat = "h:mm" Range("L2").Select ActiveCell.FormulaR1C1 = "=SUM(RC[-3]-RC[-4])" Selection.Copy Range("L3:L1101").Select ActiveSheet.Paste ActiveWindow.SmallScroll Down:=-39 ActiveWindow.LargeScroll Down:=-33 ActiveWindow.ScrollRow = 2 Application.CutCopyMode = False ActiveWorkbook.Save Range("K2").Select ActiveCell.FormulaR1C1 = "DAYS" Range("K2").Select Selection.Copy Range("K3:K1101").Select ActiveSheet.Paste ActiveWindow.SmallScroll Down:=-9 Columns("K:K").ColumnWidth = 5.29 ActiveWindow.LargeScroll Down:=-35 Application.CutCopyMode = False ActiveWorkbook.Save Range("J1").Select ActiveCell.FormulaR1C1 = "Net-Work Days" Range("J2").Select ActiveCell.FormulaR1C1 = "=NETWORKDAYS(RC[-4],RC[-3])" Columns("J:J").Select Selection.NumberFormat = "General" Range("J2").Select Selection.Copy Range("J3:J1101").Select ActiveSheet.Paste Application.CutCopyMode = False ActiveWorkbook.Save ActiveWindow.LargeScroll Down:=-35 Columns("J:J").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _ Formula1:="3" Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="3" With Selection.FormatConditions(2).Font .Bold = True .Italic = False .ColorIndex = 3 End With ActiveWindow.SmallScroll Down:=546 ActiveWindow.LargeScroll Down:=16 ActiveWindow.ScrollRow = 1071 ActiveWindow.SmallScroll Down:=15 Range("I1102").Select ActiveCell.FormulaR1C1 = "Over 2 Days" Range("I1103").Select ActiveCell.FormulaR1C1 = "Under 2 Days" Range("J1102").Select ActiveCell.FormulaR1C1 = "=COUNTIF(R[-1100]C:R[-1]C,""3"")" Application.Run Range("AUTOSAVE.XLA!mcs02.OnTime") ActiveWindow.SmallScroll Down:=12 Range("J1103").Select ActiveCell.FormulaR1C1 = "=COUNTIF(R[-1101]C:R[-2]C,""<=3"")" ActiveWindow.SmallScroll Down:=12 Range("J1102:J1103").Select With Selection.Font .Name = "Arial" .FontStyle = "Bold" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With ActiveWindow.SmallScroll Down:=-45 Columns("L:L").Select With Selection .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .MergeCells = False End With End Sub |
|
#5
|
|||
|
|||
|
I hate the NG wrap-around ... grrr
Sub CRMReportMacro() ' ' CRMReportMacro Macro ' Macro recorded 11/08/2005 by Administrator ' ' Keyboard Shortcut: Ctrl+j ' Dim iLastRow As Long Columns("H:L").Insert Range("H1").Value = "Time Req" Range("I1").Value = "Time Closed" Range("L1").Value = "Time In Hours" iLastRow = Cells(Rows.Count, "F").End(xlUp).Row Range("F2:F" & iLastRow).Copy Range("H2") Range("G2:G" & iLastRow).Copy Range("I2") Application.CutCopyMode = False Range("L1").NumberFormat = "h:mm" With Range("L2") .FormulaR1C1 = "=SUM(RC[-3]-RC[-4])" .AutoFill Range("L3:L" & iLastRow) End With ActiveWorkbook.Save Range("K2").FormulaR1C1 = "DAYS" Range("K2").Copy Range("K3:K1" & iLastRow) Columns("K:K").ColumnWidth = 5.29 Range("J1").FormulaR1C1 = "Net-Work Days" Range("J2").FormulaR1C1 = "=NETWORKDAYS(RC[-4],RC[-3])" Columns("J:J").NumberFormat = "General" Range("J2").Copy Range("J3:J" & iLastRow) With Columns("J:J") With .FormatConditions .Delete .FormatConditions.Add Type:=xlCellValue, _ Operator:=xlLessEqual, _ Formula1:="3" .FormatConditions.Add Type:=xlCellValue, _ Operator:=xlLessEqual, _ Formula1:="3" End With With .FormatConditions(2).Font .Bold = True .Italic = False .ColorIndex = 3 End With End With Range("I1102").Value = "Over 2 Days" Range("I1103").Value = "Under 2 Days" Range("J1102").FormulaR1C1 = "=COUNTIF(R[-" & _ iLastRow - 1 &"]C:R[-1]C,""3"")" Application.Run Range("AUTOSAVE.XLA!mcs02.OnTime") Range("J1103").FormulaR1C1 = "=COUNTIF(R[-" & _ iLastRow &"]C:R[-2]C,""<=3"")" With Range("J1102:J1103").Font .Name = "Arial" .FontStyle = "Bold" .Size = 10 End With With Columns("L:L") .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom End With Application.CutCopyMode = False ActiveWorkbook.Save End Sub -- HTH RP (remove nothere from the email address if mailing direct) "traineeross" wrote in message ... Thank-you very much for your help so far, it is much appreciated. However I just tried to run it and i got a message saying compile error: syntax error and this row was highlighted, Range("J1102").FormulaR1C1 = "=COUNTIF(R[-" & iLastRow - 1 & Hopefully you'll understand this. Many thanks Jody Williams "Bob Phillips" wrote: Sorry missed a bit Sub CRMReportMacro() ' ' CRMReportMacro Macro ' Macro recorded 11/08/2005 by Administrator ' ' Keyboard Shortcut: Ctrl+j ' Dim iLastRow As Long Columns("H:L").Insert Range("H1").Value = "Time Req" Range("I1").Value = "Time Closed" Range("L1").Value = "Time In Hours" iLastRow = Cells(Rows.Count, "F").End(xlUp).Row Range("F2:F" & iLastRow).Copy Range("H2") Range("G2:G" & iLastRow).Copy Range("I2") Application.CutCopyMode = False Range("L1").NumberFormat = "h:mm" With Range("L2") .FormulaR1C1 = "=SUM(RC[-3]-RC[-4])" .AutoFill Range("L3:L" & iLastRow) End With ActiveWorkbook.Save Range("K2").FormulaR1C1 = "DAYS" Range("K2").Copy Range("K3:K1" & iLastRow) Columns("K:K").ColumnWidth = 5.29 Range("J1").FormulaR1C1 = "Net-Work Days" Range("J2").FormulaR1C1 = "=NETWORKDAYS(RC[-4],RC[-3])" Columns("J:J").NumberFormat = "General" Range("J2").Copy Range("J3:J" & iLastRow) With Columns("J:J") With .FormatConditions .Delete .FormatConditions.Add Type:=xlCellValue, _ Operator:=xlLessEqual, _ Formula1:="3" .FormatConditions.Add Type:=xlCellValue, _ Operator:=xlLessEqual, _ Formula1:="3" End With With .FormatConditions(2).Font .Bold = True .Italic = False .ColorIndex = 3 End With End With Range("I1102").Value = "Over 2 Days" Range("I1103").Value = "Under 2 Days" Range("J1102").FormulaR1C1 = "=COUNTIF(R[-" & iLastRow - 1 & "]C:R[-1]C,""3"")" Application.Run Range("AUTOSAVE.XLA!mcs02.OnTime") Range("J1103").FormulaR1C1 = "=COUNTIF(R[-" & iLastRow & "]C:R[-2]C,""<=3"")" With Range("J1102:J1103").Font .Name = "Arial" .FontStyle = "Bold" .Size = 10 End With With Columns("L:L") .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom End With Application.CutCopyMode = False ActiveWorkbook.Save End Sub -- HTH RP (remove nothere from the email address if mailing direct) "traineeross" wrote in message ... I have recorded a macro to convert a report sent to us by another department. This involves inserting extra columns to use formulas to convert the data. However as the amount of rows varies with the amount of data, how do i get the macro to work by going to the last active cell in a column? It is the first long macro i have written so any help would be much appreciated. I have included the macro to help. Sub CRMReportMacro() ' ' CRMReportMacro Macro ' Macro recorded 11/08/2005 by Administrator ' ' Keyboard Shortcut: Ctrl+j ' Range("H2").Select Selection.EntireColumn.Insert Selection.EntireColumn.Insert Selection.EntireColumn.Insert Selection.EntireColumn.Insert Selection.EntireColumn.Insert Range("H1").Select ActiveCell.FormulaR1C1 = "Time Req" Range("I1").Select ActiveCell.FormulaR1C1 = "Time Closed" Range("L1").Select ActiveCell.FormulaR1C1 = "Time In Hours" Range("F2:F1101").Select Selection.Copy ActiveWindow.SmallScroll Down:=-27 ActiveWindow.LargeScroll Down:=-34 Range("H3").Select ActiveWindow.SmallScroll Down:=-6 Range("H2").Select ActiveSheet.Paste Range("G2:G1101").Select Application.CutCopyMode = False Selection.Copy ActiveWindow.LargeScroll Down:=-33 ActiveWindow.ScrollRow = 2 Range("I2").Select ActiveSheet.Paste Application.CutCopyMode = False ActiveWorkbook.Save Range("H:I,L:L").Select Range("L1").Activate Selection.NumberFormat = "h:mm" Range("L2").Select ActiveCell.FormulaR1C1 = "=SUM(RC[-3]-RC[-4])" Selection.Copy Range("L3:L1101").Select ActiveSheet.Paste ActiveWindow.SmallScroll Down:=-39 ActiveWindow.LargeScroll Down:=-33 ActiveWindow.ScrollRow = 2 Application.CutCopyMode = False ActiveWorkbook.Save Range("K2").Select ActiveCell.FormulaR1C1 = "DAYS" Range("K2").Select Selection.Copy Range("K3:K1101").Select ActiveSheet.Paste ActiveWindow.SmallScroll Down:=-9 Columns("K:K").ColumnWidth = 5.29 ActiveWindow.LargeScroll Down:=-35 Application.CutCopyMode = False ActiveWorkbook.Save Range("J1").Select ActiveCell.FormulaR1C1 = "Net-Work Days" Range("J2").Select ActiveCell.FormulaR1C1 = "=NETWORKDAYS(RC[-4],RC[-3])" Columns("J:J").Select Selection.NumberFormat = "General" Range("J2").Select Selection.Copy Range("J3:J1101").Select ActiveSheet.Paste Application.CutCopyMode = False ActiveWorkbook.Save ActiveWindow.LargeScroll Down:=-35 Columns("J:J").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _ Formula1:="3" Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="3" With Selection.FormatConditions(2).Font .Bold = True .Italic = False .ColorIndex = 3 End With ActiveWindow.SmallScroll Down:=546 ActiveWindow.LargeScroll Down:=16 ActiveWindow.ScrollRow = 1071 ActiveWindow.SmallScroll Down:=15 Range("I1102").Select ActiveCell.FormulaR1C1 = "Over 2 Days" Range("I1103").Select ActiveCell.FormulaR1C1 = "Under 2 Days" Range("J1102").Select ActiveCell.FormulaR1C1 = "=COUNTIF(R[-1100]C:R[-1]C,""3"")" Application.Run Range("AUTOSAVE.XLA!mcs02.OnTime") ActiveWindow.SmallScroll Down:=12 Range("J1103").Select ActiveCell.FormulaR1C1 = "=COUNTIF(R[-1101]C:R[-2]C,""<=3"")" ActiveWindow.SmallScroll Down:=12 Range("J1102:J1103").Select With Selection.Font .Name = "Arial" .FontStyle = "Bold" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With ActiveWindow.SmallScroll Down:=-45 Columns("L:L").Select With Selection .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .MergeCells = False End With End Sub |
|
#6
|
|||
|
|||
|
hi,
the line of code is not complete. in the VB editor, if the line is in red fonts then it wrapped on you. that line and the line below it should be 1 line of code. you can fix it by putting an underscore _ at the end of the line. this tells vb that the line continues. Range("J1102").FormulaR1C1 = "=COUNTIF(R[-" & iLastRow - 1 & _ "]C:R[-1]C,""3"")" also it looks like the line 2 lines down wrapped also. you can fix it the same way. Range("J1103").FormulaR1C1 = "=COUNTIF(R[-" & iLastRow & _ "]C:R[-2]C,""<=3"")" Regards FSt1 "traineeross" wrote: Thank-you very much for your help so far, it is much appreciated. However I just tried to run it and i got a message saying compile error: syntax error and this row was highlighted, Range("J1102").FormulaR1C1 = "=COUNTIF(R[-" & iLastRow - 1 & Hopefully you'll understand this. Many thanks Jody Williams "Bob Phillips" wrote: Sorry missed a bit Sub CRMReportMacro() ' ' CRMReportMacro Macro ' Macro recorded 11/08/2005 by Administrator ' ' Keyboard Shortcut: Ctrl+j ' Dim iLastRow As Long Columns("H:L").Insert Range("H1").Value = "Time Req" Range("I1").Value = "Time Closed" Range("L1").Value = "Time In Hours" iLastRow = Cells(Rows.Count, "F").End(xlUp).Row Range("F2:F" & iLastRow).Copy Range("H2") Range("G2:G" & iLastRow).Copy Range("I2") Application.CutCopyMode = False Range("L1").NumberFormat = "h:mm" With Range("L2") .FormulaR1C1 = "=SUM(RC[-3]-RC[-4])" .AutoFill Range("L3:L" & iLastRow) End With ActiveWorkbook.Save Range("K2").FormulaR1C1 = "DAYS" Range("K2").Copy Range("K3:K1" & iLastRow) Columns("K:K").ColumnWidth = 5.29 Range("J1").FormulaR1C1 = "Net-Work Days" Range("J2").FormulaR1C1 = "=NETWORKDAYS(RC[-4],RC[-3])" Columns("J:J").NumberFormat = "General" Range("J2").Copy Range("J3:J" & iLastRow) With Columns("J:J") With .FormatConditions .Delete .FormatConditions.Add Type:=xlCellValue, _ Operator:=xlLessEqual, _ Formula1:="3" .FormatConditions.Add Type:=xlCellValue, _ Operator:=xlLessEqual, _ Formula1:="3" End With With .FormatConditions(2).Font .Bold = True .Italic = False .ColorIndex = 3 End With End With Range("I1102").Value = "Over 2 Days" Range("I1103").Value = "Under 2 Days" Range("J1102").FormulaR1C1 = "=COUNTIF(R[-" & iLastRow - 1 & "]C:R[-1]C,""3"")" Application.Run Range("AUTOSAVE.XLA!mcs02.OnTime") Range("J1103").FormulaR1C1 = "=COUNTIF(R[-" & iLastRow & "]C:R[-2]C,""<=3"")" With Range("J1102:J1103").Font .Name = "Arial" .FontStyle = "Bold" .Size = 10 End With With Columns("L:L") .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom End With Application.CutCopyMode = False ActiveWorkbook.Save End Sub -- HTH RP (remove nothere from the email address if mailing direct) "traineeross" wrote in message ... I have recorded a macro to convert a report sent to us by another department. This involves inserting extra columns to use formulas to convert the data. However as the amount of rows varies with the amount of data, how do i get the macro to work by going to the last active cell in a column? It is the first long macro i have written so any help would be much appreciated. I have included the macro to help. Sub CRMReportMacro() ' ' CRMReportMacro Macro ' Macro recorded 11/08/2005 by Administrator ' ' Keyboard Shortcut: Ctrl+j ' Range("H2").Select Selection.EntireColumn.Insert Selection.EntireColumn.Insert Selection.EntireColumn.Insert Selection.EntireColumn.Insert Selection.EntireColumn.Insert Range("H1").Select ActiveCell.FormulaR1C1 = "Time Req" Range("I1").Select ActiveCell.FormulaR1C1 = "Time Closed" Range("L1").Select ActiveCell.FormulaR1C1 = "Time In Hours" Range("F2:F1101").Select Selection.Copy ActiveWindow.SmallScroll Down:=-27 ActiveWindow.LargeScroll Down:=-34 Range("H3").Select ActiveWindow.SmallScroll Down:=-6 Range("H2").Select ActiveSheet.Paste Range("G2:G1101").Select Application.CutCopyMode = False Selection.Copy ActiveWindow.LargeScroll Down:=-33 ActiveWindow.ScrollRow = 2 Range("I2").Select ActiveSheet.Paste Application.CutCopyMode = False ActiveWorkbook.Save Range("H:I,L:L").Select Range("L1").Activate Selection.NumberFormat = "h:mm" Range("L2").Select ActiveCell.FormulaR1C1 = "=SUM(RC[-3]-RC[-4])" Selection.Copy Range("L3:L1101").Select ActiveSheet.Paste ActiveWindow.SmallScroll Down:=-39 ActiveWindow.LargeScroll Down:=-33 ActiveWindow.ScrollRow = 2 Application.CutCopyMode = False ActiveWorkbook.Save Range("K2").Select ActiveCell.FormulaR1C1 = "DAYS" Range("K2").Select Selection.Copy Range("K3:K1101").Select ActiveSheet.Paste ActiveWindow.SmallScroll Down:=-9 Columns("K:K").ColumnWidth = 5.29 ActiveWindow.LargeScroll Down:=-35 Application.CutCopyMode = False ActiveWorkbook.Save Range("J1").Select ActiveCell.FormulaR1C1 = "Net-Work Days" Range("J2").Select ActiveCell.FormulaR1C1 = "=NETWORKDAYS(RC[-4],RC[-3])" Columns("J:J").Select Selection.NumberFormat = "General" Range("J2").Select Selection.Copy Range("J3:J1101").Select ActiveSheet.Paste Application.CutCopyMode = False ActiveWorkbook.Save ActiveWindow.LargeScroll Down:=-35 Columns("J:J").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _ Formula1:="3" Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="3" With Selection.FormatConditions(2).Font .Bold = True .Italic = False .ColorIndex = 3 End With ActiveWindow.SmallScroll Down:=546 ActiveWindow.LargeScroll Down:=16 ActiveWindow.ScrollRow = 1071 ActiveWindow.SmallScroll Down:=15 Range("I1102").Select ActiveCell.FormulaR1C1 = "Over 2 Days" Range("I1103").Select ActiveCell.FormulaR1C1 = "Under 2 Days" Range("J1102").Select ActiveCell.FormulaR1C1 = "=COUNTIF(R[-1100]C:R[-1]C,""3"")" Application.Run Range("AUTOSAVE.XLA!mcs02.OnTime") ActiveWindow.SmallScroll Down:=12 Range("J1103").Select ActiveCell.FormulaR1C1 = "=COUNTIF(R[-1101]C:R[-2]C,""<=3"")" ActiveWindow.SmallScroll Down:=12 Range("J1102:J1103").Select With Selection.Font .Name = "Arial" .FontStyle = "Bold" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With ActiveWindow.SmallScroll Down:=-45 Columns("L:L").Select With Selection .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .MergeCells = False End With End Sub |
|
#7
|
|||
|
|||
|
Range("J1102").FormulaR1C1 = "=COUNTIF(R[-" & iLastRow - 1 &
"]C:R[-1]C,""3"")" and Range("J1103").FormulaR1C1 = "=COUNTIF(R[-" & iLastRow & "]C:R[-2]C,""<=3"")" These rows both were in red when i tried running it again. Hope this makes it clearer. Many thanks Jody "Bob Phillips" wrote: I hate the NG wrap-around ... grrr Sub CRMReportMacro() ' ' CRMReportMacro Macro ' Macro recorded 11/08/2005 by Administrator ' ' Keyboard Shortcut: Ctrl+j ' Dim iLastRow As Long Columns("H:L").Insert Range("H1").Value = "Time Req" Range("I1").Value = "Time Closed" Range("L1").Value = "Time In Hours" iLastRow = Cells(Rows.Count, "F").End(xlUp).Row Range("F2:F" & iLastRow).Copy Range("H2") Range("G2:G" & iLastRow).Copy Range("I2") Application.CutCopyMode = False Range("L1").NumberFormat = "h:mm" With Range("L2") .FormulaR1C1 = "=SUM(RC[-3]-RC[-4])" .AutoFill Range("L3:L" & iLastRow) End With ActiveWorkbook.Save Range("K2").FormulaR1C1 = "DAYS" Range("K2").Copy Range("K3:K1" & iLastRow) Columns("K:K").ColumnWidth = 5.29 Range("J1").FormulaR1C1 = "Net-Work Days" Range("J2").FormulaR1C1 = "=NETWORKDAYS(RC[-4],RC[-3])" Columns("J:J").NumberFormat = "General" Range("J2").Copy Range("J3:J" & iLastRow) With Columns("J:J") With .FormatConditions .Delete .FormatConditions.Add Type:=xlCellValue, _ Operator:=xlLessEqual, _ Formula1:="3" .FormatConditions.Add Type:=xlCellValue, _ Operator:=xlLessEqual, _ Formula1:="3" End With With .FormatConditions(2).Font .Bold = True .Italic = False .ColorIndex = 3 End With End With Range("I1102").Value = "Over 2 Days" Range("I1103").Value = "Under 2 Days" Range("J1102").FormulaR1C1 = "=COUNTIF(R[-" & _ iLastRow - 1 &"]C:R[-1]C,""3"")" Application.Run Range("AUTOSAVE.XLA!mcs02.OnTime") Range("J1103").FormulaR1C1 = "=COUNTIF(R[-" & _ iLastRow &"]C:R[-2]C,""<=3"")" With Range("J1102:J1103").Font .Name = "Arial" .FontStyle = "Bold" .Size = 10 End With With Columns("L:L") .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom End With Application.CutCopyMode = False ActiveWorkbook.Save End Sub -- HTH RP (remove nothere from the email address if mailing direct) "traineeross" wrote in message ... Thank-you very much for your help so far, it is much appreciated. However I just tried to run it and i got a message saying compile error: syntax error and this row was highlighted, Range("J1102").FormulaR1C1 = "=COUNTIF(R[-" & iLastRow - 1 & Hopefully you'll understand this. Many thanks Jody Williams "Bob Phillips" wrote: Sorry missed a bit Sub CRMReportMacro() ' ' CRMReportMacro Macro ' Macro recorded 11/08/2005 by Administrator ' ' Keyboard Shortcut: Ctrl+j ' Dim iLastRow As Long Columns("H:L").Insert Range("H1").Value = "Time Req" Range("I1").Value = "Time Closed" Range("L1").Value = "Time In Hours" iLastRow = Cells(Rows.Count, "F").End(xlUp).Row Range("F2:F" & iLastRow).Copy Range("H2") Range("G2:G" & iLastRow).Copy Range("I2") Application.CutCopyMode = False Range("L1").NumberFormat = "h:mm" With Range("L2") .FormulaR1C1 = "=SUM(RC[-3]-RC[-4])" .AutoFill Range("L3:L" & iLastRow) End With ActiveWorkbook.Save Range("K2").FormulaR1C1 = "DAYS" Range("K2").Copy Range("K3:K1" & iLastRow) Columns("K:K").ColumnWidth = 5.29 Range("J1").FormulaR1C1 = "Net-Work Days" Range("J2").FormulaR1C1 = "=NETWORKDAYS(RC[-4],RC[-3])" Columns("J:J").NumberFormat = "General" Range("J2").Copy Range("J3:J" & iLastRow) With Columns("J:J") With .FormatConditions .Delete .FormatConditions.Add Type:=xlCellValue, _ Operator:=xlLessEqual, _ Formula1:="3" .FormatConditions.Add Type:=xlCellValue, _ Operator:=xlLessEqual, _ Formula1:="3" End With With .FormatConditions(2).Font .Bold = True .Italic = False .ColorIndex = 3 End With End With Range("I1102").Value = "Over 2 Days" Range("I1103").Value = "Under 2 Days" Range("J1102").FormulaR1C1 = "=COUNTIF(R[-" & iLastRow - 1 & "]C:R[-1]C,""3"")" Application.Run Range("AUTOSAVE.XLA!mcs02.OnTime") Range("J1103").FormulaR1C1 = "=COUNTIF(R[-" & iLastRow & "]C:R[-2]C,""<=3"")" With Range("J1102:J1103").Font .Name = "Arial" .FontStyle = "Bold" .Size = 10 End With With Columns("L:L") .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom End With Application.CutCopyMode = False ActiveWorkbook.Save End Sub -- HTH RP (remove nothere from the email address if mailing direct) "traineeross" wrote in message ... I have recorded a macro to convert a report sent to us by another department. This involves inserting extra columns to use formulas to convert the data. However as the amount of rows varies with the amount of data, how do i get the macro to work by going to the last active cell in a column? It is the first long macro i have written so any help would be much appreciated. I have included the macro to help. Sub CRMReportMacro() ' ' CRMReportMacro Macro ' Macro recorded 11/08/2005 by Administrator ' ' Keyboard Shortcut: Ctrl+j ' Range("H2").Select Selection.EntireColumn.Insert Selection.EntireColumn.Insert Selection.EntireColumn.Insert Selection.EntireColumn.Insert Selection.EntireColumn.Insert Range("H1").Select ActiveCell.FormulaR1C1 = "Time Req" Range("I1").Select ActiveCell.FormulaR1C1 = "Time Closed" Range("L1").Select ActiveCell.FormulaR1C1 = "Time In Hours" Range("F2:F1101").Select Selection.Copy ActiveWindow.SmallScroll Down:=-27 ActiveWindow.LargeScroll Down:=-34 Range("H3").Select ActiveWindow.SmallScroll Down:=-6 Range("H2").Select ActiveSheet.Paste Range("G2:G1101").Select Application.CutCopyMode = False Selection.Copy ActiveWindow.LargeScroll Down:=-33 ActiveWindow.ScrollRow = 2 Range("I2").Select ActiveSheet.Paste Application.CutCopyMode = False ActiveWorkbook.Save Range("H:I,L:L").Select Range("L1").Activate Selection.NumberFormat = "h:mm" Range("L2").Select ActiveCell.FormulaR1C1 = "=SUM(RC[-3]-RC[-4])" Selection.Copy Range("L3:L1101").Select ActiveSheet.Paste ActiveWindow.SmallScroll Down:=-39 ActiveWindow.LargeScroll Down:=-33 ActiveWindow.ScrollRow = 2 Application.CutCopyMode = False ActiveWorkbook.Save Range("K2").Select ActiveCell.FormulaR1C1 = "DAYS" Range("K2").Select Selection.Copy Range("K3:K1101").Select ActiveSheet.Paste ActiveWindow.SmallScroll Down:=-9 Columns("K:K").ColumnWidth = 5.29 ActiveWindow.LargeScroll Down:=-35 Application.CutCopyMode = False ActiveWorkbook.Save Range("J1").Select ActiveCell.FormulaR1C1 = "Net-Work Days" Range("J2").Select ActiveCell.FormulaR1C1 = "=NETWORKDAYS(RC[-4],RC[-3])" Columns("J:J").Select Selection.NumberFormat = "General" Range("J2").Select Selection.Copy Range("J3:J1101").Select ActiveSheet.Paste Application.CutCopyMode = False ActiveWorkbook.Save ActiveWindow.LargeScroll Down:=-35 Columns("J:J").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _ Formula1:="3" Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="3" With Selection.FormatConditions(2).Font .Bold = True .Italic = False .ColorIndex = 3 End With ActiveWindow.SmallScroll Down:=546 ActiveWindow.LargeScroll Down:=16 ActiveWindow.ScrollRow = 1071 ActiveWindow.SmallScroll Down:=15 Range("I1102").Select ActiveCell.FormulaR1C1 = "Over 2 Days" Range("I1103").Select ActiveCell.FormulaR1C1 = "Under 2 Days" Range("J1102").Select ActiveCell.FormulaR1C1 = "=COUNTIF(R[-1100]C:R[-1]C,""3"")" Application.Run Range("AUTOSAVE.XLA!mcs02.OnTime") ActiveWindow.SmallScroll Down:=12 Range("J1103").Select ActiveCell.FormulaR1C1 = "=COUNTIF(R[-1101]C:R[-2]C,""<=3"")" ActiveWindow.SmallScroll Down:=12 Range("J1102:J1103").Select With Selection.Font .Name = "Arial" .FontStyle = "Bold" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic |
|
#8
|
|||
|
|||
|
Thank-you very much, that has solved that bit but now when i run it i get
this error in a grey box Run-time Error '1004': Autofill method of Range class failed and the options, end debug or help. Thank-you in advance Jody Williams "FSt1" wrote: hi, the line of code is not complete. in the VB editor, if the line is in red fonts then it wrapped on you. that line and the line below it should be 1 line of code. you can fix it by putting an underscore _ at the end of the line. this tells vb that the line continues. Range("J1102").FormulaR1C1 = "=COUNTIF(R[-" & iLastRow - 1 & _ "]C:R[-1]C,""3"")" also it looks like the line 2 lines down wrapped also. you can fix it the same way. Range("J1103").FormulaR1C1 = "=COUNTIF(R[-" & iLastRow & _ "]C:R[-2]C,""<=3"")" Regards FSt1 "traineeross" wrote: Thank-you very much for your help so far, it is much appreciated. However I just tried to run it and i got a message saying compile error: syntax error and this row was highlighted, Range("J1102").FormulaR1C1 = "=COUNTIF(R[-" & iLastRow - 1 & Hopefully you'll understand this. Many thanks Jody Williams "Bob Phillips" wrote: Sorry missed a bit Sub CRMReportMacro() ' ' CRMReportMacro Macro ' Macro recorded 11/08/2005 by Administrator ' ' Keyboard Shortcut: Ctrl+j ' Dim iLastRow As Long Columns("H:L").Insert Range("H1").Value = "Time Req" Range("I1").Value = "Time Closed" Range("L1").Value = "Time In Hours" iLastRow = Cells(Rows.Count, "F").End(xlUp).Row Range("F2:F" & iLastRow).Copy Range("H2") Range("G2:G" & iLastRow).Copy Range("I2") Application.CutCopyMode = False Range("L1").NumberFormat = "h:mm" With Range("L2") .FormulaR1C1 = "=SUM(RC[-3]-RC[-4])" .AutoFill Range("L3:L" & iLastRow) End With ActiveWorkbook.Save Range("K2").FormulaR1C1 = "DAYS" Range("K2").Copy Range("K3:K1" & iLastRow) Columns("K:K").ColumnWidth = 5.29 Range("J1").FormulaR1C1 = "Net-Work Days" Range("J2").FormulaR1C1 = "=NETWORKDAYS(RC[-4],RC[-3])" Columns("J:J").NumberFormat = "General" Range("J2").Copy Range("J3:J" & iLastRow) With Columns("J:J") With .FormatConditions .Delete .FormatConditions.Add Type:=xlCellValue, _ Operator:=xlLessEqual, _ Formula1:="3" .FormatConditions.Add Type:=xlCellValue, _ Operator:=xlLessEqual, _ Formula1:="3" End With With .FormatConditions(2).Font .Bold = True .Italic = False .ColorIndex = 3 End With End With Range("I1102").Value = "Over 2 Days" Range("I1103").Value = "Under 2 Days" Range("J1102").FormulaR1C1 = "=COUNTIF(R[-" & iLastRow - 1 & "]C:R[-1]C,""3"")" Application.Run Range("AUTOSAVE.XLA!mcs02.OnTime") Range("J1103").FormulaR1C1 = "=COUNTIF(R[-" & iLastRow & "]C:R[-2]C,""<=3"")" With Range("J1102:J1103").Font .Name = "Arial" .FontStyle = "Bold" .Size = 10 End With With Columns("L:L") .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom End With Application.CutCopyMode = False ActiveWorkbook.Save End Sub -- HTH RP (remove nothere from the email address if mailing direct) "traineeross" wrote in message ... I have recorded a macro to convert a report sent to us by another department. This involves inserting extra columns to use formulas to convert the data. However as the amount of rows varies with the amount of data, how do i get the macro to work by going to the last active cell in a column? It is the first long macro i have written so any help would be much appreciated. I have included the macro to help. Sub CRMReportMacro() ' ' CRMReportMacro Macro ' Macro recorded 11/08/2005 by Administrator ' ' Keyboard Shortcut: Ctrl+j ' Range("H2").Select Selection.EntireColumn.Insert Selection.EntireColumn.Insert Selection.EntireColumn.Insert Selection.EntireColumn.Insert Selection.EntireColumn.Insert Range("H1").Select ActiveCell.FormulaR1C1 = "Time Req" Range("I1").Select ActiveCell.FormulaR1C1 = "Time Closed" Range("L1").Select ActiveCell.FormulaR1C1 = "Time In Hours" Range("F2:F1101").Select Selection.Copy ActiveWindow.SmallScroll Down:=-27 ActiveWindow.LargeScroll Down:=-34 Range("H3").Select ActiveWindow.SmallScroll Down:=-6 Range("H2").Select ActiveSheet.Paste Range("G2:G1101").Select Application.CutCopyMode = False Selection.Copy ActiveWindow.LargeScroll Down:=-33 ActiveWindow.ScrollRow = 2 Range("I2").Select ActiveSheet.Paste Application.CutCopyMode = False ActiveWorkbook.Save Range("H:I,L:L").Select Range("L1").Activate Selection.NumberFormat = "h:mm" Range("L2").Select ActiveCell.FormulaR1C1 = "=SUM(RC[-3]-RC[-4])" Selection.Copy Range("L3:L1101").Select ActiveSheet.Paste ActiveWindow.SmallScroll Down:=-39 ActiveWindow.LargeScroll Down:=-33 ActiveWindow.ScrollRow = 2 Application.CutCopyMode = False ActiveWorkbook.Save Range("K2").Select ActiveCell.FormulaR1C1 = "DAYS" Range("K2").Select Selection.Copy Range("K3:K1101").Select ActiveSheet.Paste ActiveWindow.SmallScroll Down:=-9 Columns("K:K").ColumnWidth = 5.29 ActiveWindow.LargeScroll Down:=-35 Application.CutCopyMode = False ActiveWorkbook.Save Range("J1").Select ActiveCell.FormulaR1C1 = "Net-Work Days" Range("J2").Select ActiveCell.FormulaR1C1 = "=NETWORKDAYS(RC[-4],RC[-3])" Columns("J:J").Select Selection.NumberFormat = "General" Range("J2").Select Selection.Copy Range("J3:J1101").Select ActiveSheet.Paste Application.CutCopyMode = False ActiveWorkbook.Save ActiveWindow.LargeScroll Down:=-35 Columns("J:J").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _ Formula1:="3" Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="3" With Selection.FormatConditions(2).Font .Bold = True .Italic = False .ColorIndex = 3 End With ActiveWindow.SmallScroll Down:=546 ActiveWindow.LargeScroll Down:=16 ActiveWindow.ScrollRow = 1071 ActiveWindow.SmallScroll Down:=15 Range("I1102").Select ActiveCell.FormulaR1C1 = "Over 2 Days" Range("I1103").Select ActiveCell.FormulaR1C1 = "Under 2 Days" Range("J1102").Select ActiveCell.FormulaR1C1 = "=COUNTIF(R[-1100]C:R[-1]C,""3"")" Application.Run Range("AUTOSAVE.XLA!mcs02.OnTime") ActiveWindow.SmallScroll Down:=12 Range("J1103").Select ActiveCell.FormulaR1C1 = "=COUNTIF(R[-1101]C:R[-2]C,""<=3"")" ActiveWindow.SmallScroll Down:=12 Range("J1102:J1103").Select With Selection.Font .Name = "Arial" .FontStyle = "Bold" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With ActiveWindow.SmallScroll Down:=-45 Columns("L:L").Select With Selection .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .MergeCells = False End With End Sub |
|
#9
|
|||
|
|||
|
hi,
in your code replace this With Range("L2") .FormulaR1C1 = "=SUM(RC[-3]-RC[-4])" .AutoFill Range("L3:L" & iLastRow) End With with this Range("L2").FormulaR1C1 = "=SUM(RC[-3]-RC[-4])" Range("L2").Copy Range("L3:L" & iLastRow).Select ActiveSheet.Paste I saw a couple of thing that might be wrong with the autofill method but i would have to play with it and experiment. but since you have the data, i can't play with it. you did the first macro on record and this is basicly what you did just a little more genaric. not as pretty but should work. post back if it don't. regards FSt1 "traineeross" wrote: Thank-you very much, that has solved that bit but now when i run it i get this error in a grey box Run-time Error '1004': Autofill method of Range class failed and the options, end debug or help. Thank-you in advance Jody Williams "FSt1" wrote: hi, the line of code is not complete. in the VB editor, if the line is in red fonts then it wrapped on you. that line and the line below it should be 1 line of code. you can fix it by putting an underscore _ at the end of the line. this tells vb that the line continues. Range("J1102").FormulaR1C1 = "=COUNTIF(R[-" & iLastRow - 1 & _ "]C:R[-1]C,""3"")" also it looks like the line 2 lines down wrapped also. you can fix it the same way. Range("J1103").FormulaR1C1 = "=COUNTIF(R[-" & iLastRow & _ "]C:R[-2]C,""<=3"")" Regards FSt1 "traineeross" wrote: Thank-you very much for your help so far, it is much appreciated. However I just tried to run it and i got a message saying compile error: syntax error and this row was highlighted, Range("J1102").FormulaR1C1 = "=COUNTIF(R[-" & iLastRow - 1 & Hopefully you'll understand this. Many thanks Jody Williams "Bob Phillips" wrote: Sorry missed a bit Sub CRMReportMacro() ' ' CRMReportMacro Macro ' Macro recorded 11/08/2005 by Administrator ' ' Keyboard Shortcut: Ctrl+j ' Dim iLastRow As Long Columns("H:L").Insert Range("H1").Value = "Time Req" Range("I1").Value = "Time Closed" Range("L1").Value = "Time In Hours" iLastRow = Cells(Rows.Count, "F").End(xlUp).Row Range("F2:F" & iLastRow).Copy Range("H2") Range("G2:G" & iLastRow).Copy Range("I2") Application.CutCopyMode = False Range("L1").NumberFormat = "h:mm" With Range("L2") .FormulaR1C1 = "=SUM(RC[-3]-RC[-4])" .AutoFill Range("L3:L" & iLastRow) End With ActiveWorkbook.Save Range("K2").FormulaR1C1 = "DAYS" Range("K2").Copy Range("K3:K1" & iLastRow) Columns("K:K").ColumnWidth = 5.29 Range("J1").FormulaR1C1 = "Net-Work Days" Range("J2").FormulaR1C1 = "=NETWORKDAYS(RC[-4],RC[-3])" Columns("J:J").NumberFormat = "General" Range("J2").Copy Range("J3:J" & iLastRow) With Columns("J:J") With .FormatConditions .Delete .FormatConditions.Add Type:=xlCellValue, _ Operator:=xlLessEqual, _ Formula1:="3" .FormatConditions.Add Type:=xlCellValue, _ Operator:=xlLessEqual, _ Formula1:="3" End With With .FormatConditions(2).Font .Bold = True .Italic = False .ColorIndex = 3 End With End With Range("I1102").Value = "Over 2 Days" Range("I1103").Value = "Under 2 Days" Range("J1102").FormulaR1C1 = "=COUNTIF(R[-" & iLastRow - 1 & "]C:R[-1]C,""3"")" Application.Run Range("AUTOSAVE.XLA!mcs02.OnTime") Range("J1103").FormulaR1C1 = "=COUNTIF(R[-" & iLastRow & "]C:R[-2]C,""<=3"")" With Range("J1102:J1103").Font .Name = "Arial" .FontStyle = "Bold" .Size = 10 End With With Columns("L:L") .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom End With Application.CutCopyMode = False ActiveWorkbook.Save End Sub -- HTH RP (remove nothere from the email address if mailing direct) "traineeross" wrote in message ... I have recorded a macro to convert a report sent to us by another department. This involves inserting extra columns to use formulas to convert the data. However as the amount of rows varies with the amount of data, how do i get the macro to work by going to the last active cell in a column? It is the first long macro i have written so any help would be much appreciated. I have included the macro to help. Sub CRMReportMacro() ' ' CRMReportMacro Macro ' Macro recorded 11/08/2005 by Administrator ' ' Keyboard Shortcut: Ctrl+j ' Range("H2").Select Selection.EntireColumn.Insert Selection.EntireColumn.Insert Selection.EntireColumn.Insert Selection.EntireColumn.Insert Selection.EntireColumn.Insert Range("H1").Select ActiveCell.FormulaR1C1 = "Time Req" Range("I1").Select ActiveCell.FormulaR1C1 = "Time Closed" Range("L1").Select ActiveCell.FormulaR1C1 = "Time In Hours" Range("F2:F1101").Select Selection.Copy ActiveWindow.SmallScroll Down:=-27 ActiveWindow.LargeScroll Down:=-34 Range("H3").Select ActiveWindow.SmallScroll Down:=-6 Range("H2").Select ActiveSheet.Paste Range("G2:G1101").Select Application.CutCopyMode = False Selection.Copy ActiveWindow.LargeScroll Down:=-33 ActiveWindow.ScrollRow = 2 Range("I2").Select ActiveSheet.Paste Application.CutCopyMode = False ActiveWorkbook.Save Range("H:I,L:L").Select Range("L1").Activate Selection.NumberFormat = "h:mm" Range("L2").Select ActiveCell.FormulaR1C1 = "=SUM(RC[-3]-RC[-4])" Selection.Copy Range("L3:L1101").Select ActiveSheet.Paste ActiveWindow.SmallScroll Down:=-39 ActiveWindow.LargeScroll Down:=-33 ActiveWindow.ScrollRow = 2 Application.CutCopyMode = False ActiveWorkbook.Save Range("K2").Select ActiveCell.FormulaR1C1 = "DAYS" Range("K2").Select Selection.Copy Range("K3:K1101").Select ActiveSheet.Paste ActiveWindow.SmallScroll Down:=-9 Columns("K:K").ColumnWidth = 5.29 ActiveWindow.LargeScroll Down:=-35 Application.CutCopyMode = False ActiveWorkbook.Save Range("J1").Select ActiveCell.FormulaR1C1 = "Net-Work Days" Range("J2").Select ActiveCell.FormulaR1C1 = "=NETWORKDAYS(RC[-4],RC[-3])" Columns("J:J").Select Selection.NumberFormat = "General" Range("J2").Select Selection.Copy Range("J3:J1101").Select ActiveSheet.Paste Application.CutCopyMode = False ActiveWorkbook.Save ActiveWindow.LargeScroll Down:=-35 Columns("J:J").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _ Formula1:="3" Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="3" With Selection.FormatConditions(2).Font .Bold = True .Italic = False .ColorIndex = 3 End With ActiveWindow.SmallScroll Down:=546 ActiveWindow.LargeScroll Down:=16 ActiveWindow.ScrollRow = 1071 ActiveWindow.SmallScroll Down:=15 Range("I1102").Select ActiveCell.FormulaR1C1 = "Over 2 Days" Range("I1103").Select ActiveCell.FormulaR1C1 = "Under 2 Days" Range("J1102").Select ActiveCell.FormulaR1C1 = "=COUNTIF(R[-1100]C:R[-1]C,""3"")" Application.Run Range("AUTOSAVE.XLA!mcs02.OnTime") ActiveWindow.SmallScroll Down:=12 Range("J1103").Select ActiveCell.FormulaR1C1 = "=COUNTIF(R[-1101]C:R[-2]C,""<=3"")" ActiveWindow.SmallScroll Down:=12 Range("J1102:J1103").Select With Selection.Font .Name = "Arial" .FontStyle = "Bold" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With ActiveWindow.SmallScroll Down:=-45 Columns("L:L").Select With Selection .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .MergeCells = False End With End Sub |
|
#10
|
|||
|
|||
|
Thank-you that fixed that bit although i now get-
Run time error '438': Object doesn't support this property or method I have included the whole macro to help, Thank-you for continuing to try and help it is very much appreciated. Sub Macro2() ' ' Macro2 Macro ' Macro recorded 12/08/2005 by Administrator ' 'Dim iLastRow As Long Columns("H:L").Insert Range("H1").Value = "Time Req" Range("I1").Value = "Time Closed" Range("L1").Value = "Time In Hours" iLastRow = Cells(Rows.Count, "F").End(xlUp).Row Range("F2:F" & iLastRow).Copy Range("H2") Range("G2:G" & iLastRow).Copy Range("I2") Application.CutCopyMode = False Range("L1").NumberFormat = "h:mm" Range("L2").FormulaR1C1 = "=SUM(RC[-3]-RC[-4])" Range("L2").Copy Range("L3:L" & iLastRow).Select ActiveSheet.Paste ActiveWorkbook.Save Range("K2").FormulaR1C1 = "DAYS" Range("K2").Copy Range("K3:K1" & iLastRow) Columns("K:K").ColumnWidth = 5.29 Range("J1").FormulaR1C1 = "Net-Work Days" Range("J2").FormulaR1C1 = "=NETWORKDAYS(RC[-4],RC[-3])" Columns("J:J").NumberFormat = "General" Range("J2").Copy Range("J3:J" & iLastRow) With Columns("J:J") With .FormatConditions .Delete .FormatConditions.Add Type:=xlCellValue, _ Operator:=xlLessEqual, _ Formula1:="3" .FormatConditions.Add Type:=xlCellValue, _ Operator:=xlLessEqual, _ Formula1:="3" End With With .FormatConditions(2).Font .Bold = True .Italic = False .ColorIndex = 3 End With End With Range("I1102").Value = "Over 2 Days" Range("I1103").Value = "Under 2 Days" Range("J1102").FormulaR1C1 = "=COUNTIF(R[-" & iLastRow - 1 & _ "]C:R[-1]C,""3"")" Application.Run Range("AUTOSAVE.XLA!mcs02.OnTime") Range("J1103").FormulaR1C1 = "=COUNTIF(R[-" & iLastRow & _ "]C:R[-2]C,""<=3"")" With Range("J1102:J1103").Font .Name = "Arial" .FontStyle = "Bold" .Size = 10 End With With Columns("L:L") .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom End With Application.CutCopyMode = False ActiveWorkbook.Save End Sub "FSt1" wrote: hi, in your code replace this With Range("L2") .FormulaR1C1 = "=SUM(RC[-3]-RC[-4])" .AutoFill Range("L3:L" & iLastRow) End With with this Range("L2").FormulaR1C1 = "=SUM(RC[-3]-RC[-4])" Range("L2").Copy Range("L3:L" & iLastRow).Select ActiveSheet.Paste I saw a couple of thing that might be wrong with the autofill method but i would have to play with it and experiment. but since you have the data, i can't play with it. you did the first macro on record and this is basicly what you did just a little more genaric. not as pretty but should work. post back if it don't. regards FSt1 "traineeross" wrote: Thank-you very much, that has solved that bit but now when i run it i get this error in a grey box Run-time Error '1004': Autofill method of Range class failed and the options, end debug or help. Thank-you in advance Jody Williams "FSt1" wrote: hi, the line of code is not complete. in the VB editor, if the line is in red fonts then it wrapped on you. that line and the line below it should be 1 line of code. you can fix it by putting an underscore _ at the end of the line. this tells vb that the line continues. Range("J1102").FormulaR1C1 = "=COUNTIF(R[-" & iLastRow - 1 & _ "]C:R[-1]C,""3"")" also it looks like the line 2 lines down wrapped also. you can fix it the same way. Range("J1103").FormulaR1C1 = "=COUNTIF(R[-" & iLastRow & _ "]C:R[-2]C,""<=3"")" Regards FSt1 "traineeross" wrote: Thank-you very much for your help so far, it is much appreciated. However I just tried to run it and i got a message saying compile error: syntax error and this row was highlighted, Range("J1102").FormulaR1C1 = "=COUNTIF(R[-" & iLastRow - 1 & Hopefully you'll understand this. Many thanks Jody Williams "Bob Phillips" wrote: Sorry missed a bit Sub CRMReportMacro() ' ' CRMReportMacro Macro ' Macro recorded 11/08/2005 by Administrator ' ' Keyboard Shortcut: Ctrl+j ' Dim iLastRow As Long Columns("H:L").Insert Range("H1").Value = "Time Req" Range("I1").Value = "Time Closed" Range("L1").Value = "Time In Hours" iLastRow = Cells(Rows.Count, "F").End(xlUp).Row Range("F2:F" & iLastRow).Copy Range("H2") Range("G2:G" & iLastRow).Copy Range("I2") Application.CutCopyMode = False Range("L1").NumberFormat = "h:mm" With Range("L2") .FormulaR1C1 = "=SUM(RC[-3]-RC[-4])" .AutoFill Range("L3:L" & iLastRow) End With ActiveWorkbook.Save Range("K2").FormulaR1C1 = "DAYS" Range("K2").Copy Range("K3:K1" & iLastRow) Columns("K:K").ColumnWidth = 5.29 Range("J1").FormulaR1C1 = "Net-Work Days" Range("J2").FormulaR1C1 = "=NETWORKDAYS(RC[-4],RC[-3])" Columns("J:J").NumberFormat = "General" Range("J2").Copy Range("J3:J" & iLastRow) With Columns("J:J") With .FormatConditions .Delete .FormatConditions.Add Type:=xlCellValue, _ Operator:=xlLessEqual, _ Formula1:="3" .FormatConditions.Add Type:=xlCellValue, _ Operator:=xlLessEqual, _ Formula1:="3" End With With .FormatConditions(2).Font .Bold = True .Italic = False .ColorIndex = 3 End With End With Range("I1102").Value = "Over 2 Days" Range("I1103").Value = "Under 2 Days" Range("J1102").FormulaR1C1 = "=COUNTIF(R[-" & iLastRow - 1 & "]C:R[-1]C,""3"")" Application.Run Range("AUTOSAVE.XLA!mcs02.OnTime") Range("J1103").FormulaR1C1 = "=COUNTIF(R[-" & iLastRow & "]C:R[-2]C,""<=3"")" With Range("J1102:J1103").Font .Name = "Arial" .FontStyle = "Bold" .Size = 10 End With With Columns("L:L") .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom End With Application.CutCopyMode = False ActiveWorkbook.Save End Sub -- HTH RP (remove nothere from the email address if mailing direct) "traineeross" wrote in message ... I have recorded a macro to convert a report sent to us by another department. This involves inserting extra columns to use formulas to convert the data. However as the amount of rows varies with the amount of data, how do i get the macro to work by going to the last active cell in a column? It is the first long macro i have written so any help would be much appreciated. I have included the macro to help. Sub CRMReportMacro() ' ' CRMReportMacro Macro ' Macro recorded 11/08/2005 by Administrator ' ' Keyboard Shortcut: Ctrl+j ' Range("H2").Select Selection.EntireColumn.Insert Selection.EntireColumn.Insert Selection.EntireColumn.Insert Selection.EntireColumn.Insert Selection.EntireColumn.Insert Range("H1").Select ActiveCell.FormulaR1C1 = "Time Req" Range("I1").Select ActiveCell.FormulaR1C1 = "Time Closed" Range("L1").Select ActiveCell.FormulaR1C1 = "Time In Hours" Range("F2:F1101").Select Selection.Copy ActiveWindow.SmallScroll Down:=-27 ActiveWindow.LargeScroll Down:=-34 Range("H3").Select ActiveWindow.SmallScroll Down:=-6 Range("H2").Select ActiveSheet.Paste Range("G2:G1101").Select Application.CutCopyMode = False Selection.Copy ActiveWindow.LargeScroll Down:=-33 ActiveWindow.ScrollRow = 2 Range("I2").Select ActiveSheet.Paste Application.CutCopyMode = False ActiveWorkbook.Save Range("H:I,L:L").Select Range("L1").Activate Selection.NumberFormat = "h:mm" Range("L2").Select ActiveCell.FormulaR1C1 = "=SUM(RC[-3]-RC[-4])" Selection.Copy Range("L3:L1101").Select ActiveSheet.Paste ActiveWindow.SmallScroll Down:=-39 ActiveWindow.LargeScroll Down:=-33 ActiveWindow.ScrollRow = 2 Application.CutCopyMode = False ActiveWorkbook.Save Range("K2").Select ActiveCell.FormulaR1C1 = "DAYS" Range("K2").Select Selection.Copy Range("K3:K1101").Select ActiveSheet.Paste ActiveWindow.SmallScroll Down:=-9 Columns("K:K").ColumnWidth = 5.29 ActiveWindow.LargeScroll Down:=-35 Application.CutCopyMode = False ActiveWorkbook.Save Range("J1").Select ActiveCell.FormulaR1C1 = "Net-Work Days" Range("J2").Select ActiveCell.FormulaR1C1 = "=NETWORKDAYS(RC[-4],RC[-3])" Columns("J:J").Select Selection.NumberFormat = "General" Range("J2").Select Selection.Copy Range("J3:J1101").Select ActiveSheet.Paste Application.CutCopyMode = False ActiveWorkbook.Save ActiveWindow.LargeScroll Down:=-35 Columns("J:J").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _ Formula1:="3" Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="3" With Selection.FormatConditions(2).Font .Bold = True .Italic = False .ColorIndex = 3 End With ActiveWindow.SmallScroll Down:=546 ActiveWindow.LargeScroll Down:=16 ActiveWindow.ScrollRow = 1071 ActiveWindow.SmallScroll Down:=15 Range("I1102").Select ActiveCell.FormulaR1C1 = "Over 2 Days" Range("I1103").Select ActiveCell.FormulaR1C1 = "Under 2 Days" Range("J1102").Select ActiveCell.FormulaR1C1 = "=COUNTIF(R[-1100]C:R[-1]C,""3"")" Application.Run Range("AUTOSAVE.XLA!mcs02.OnTime") ActiveWindow.SmallScroll Down:=12 Range("J1103").Select ActiveCell.FormulaR1C1 = "=COUNTIF(R[-1101]C:R[-2]C,""<=3"")" ActiveWindow.SmallScroll Down:=12 Range("J1102:J1103").Select With Selection.Font .Name = "Arial" .FontStyle = "Bold" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With ActiveWindow.SmallScroll Down:=-45 Columns("L:L").Select With Selection .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .MergeCells = False End With End Sub |
|
#11
|
|||
|
|||
|
hi,
in your code replace this. With Columns("J:J") With .FormatConditions .Delete .FormatConditions.Add Type:=xlCellValue, _ Operator:=xlLessEqual, _ Formula1:="3" .FormatConditions.Add Type:=xlCellValue, _ Operator:=xlLessEqual, _ Formula1:="3" End With With .FormatConditions(2).Font .Bold = True .Italic = False .ColorIndex = 3 End With End With with this...... Columns("J:J").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _ Formula1:="3" Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="3" With Selection.FormatConditions(2).Font .Bold = True .Italic = False .ColorIndex = 3 End With Again we are simplely replace the trouble part with what you did on record. I copied this to my vb editor and stepped throught it. it worked until i hit the line Application.Run Range("AUTOSAVE.XLA!mcs02.OnTime") i don't have that so it crashed on me. try it. good luck regards FSt1 "traineeross" wrote: Thank-you that fixed that bit although i now get- Run time error '438': Object doesn't support this property or method I have included the whole macro to help, Thank-you for continuing to try and help it is very much appreciated. Sub Macro2() ' ' Macro2 Macro ' Macro recorded 12/08/2005 by Administrator ' 'Dim iLastRow As Long Columns("H:L").Insert Range("H1").Value = "Time Req" Range("I1").Value = "Time Closed" Range("L1").Value = "Time In Hours" iLastRow = Cells(Rows.Count, "F").End(xlUp).Row Range("F2:F" & iLastRow).Copy Range("H2") Range("G2:G" & iLastRow).Copy Range("I2") Application.CutCopyMode = False Range("L1").NumberFormat = "h:mm" Range("L2").FormulaR1C1 = "=SUM(RC[-3]-RC[-4])" Range("L2").Copy Range("L3:L" & iLastRow).Select ActiveSheet.Paste ActiveWorkbook.Save Range("K2").FormulaR1C1 = "DAYS" Range("K2").Copy Range("K3:K1" & iLastRow) Columns("K:K").ColumnWidth = 5.29 Range("J1").FormulaR1C1 = "Net-Work Days" Range("J2").FormulaR1C1 = "=NETWORKDAYS(RC[-4],RC[-3])" Columns("J:J").NumberFormat = "General" Range("J2").Copy Range("J3:J" & iLastRow) With Columns("J:J") With .FormatConditions .Delete .FormatConditions.Add Type:=xlCellValue, _ Operator:=xlLessEqual, _ Formula1:="3" .FormatConditions.Add Type:=xlCellValue, _ Operator:=xlLessEqual, _ Formula1:="3" End With With .FormatConditions(2).Font .Bold = True .Italic = False .ColorIndex = 3 End With End With Range("I1102").Value = "Over 2 Days" Range("I1103").Value = "Under 2 Days" Range("J1102").FormulaR1C1 = "=COUNTIF(R[-" & iLastRow - 1 & _ "]C:R[-1]C,""3"")" Application.Run Range("AUTOSAVE.XLA!mcs02.OnTime") Range("J1103").FormulaR1C1 = "=COUNTIF(R[-" & iLastRow & _ "]C:R[-2]C,""<=3"")" With Range("J1102:J1103").Font .Name = "Arial" .FontStyle = "Bold" .Size = 10 End With With Columns("L:L") .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom End With Application.CutCopyMode = False ActiveWorkbook.Save End Sub "FSt1" wrote: hi, in your code replace this With Range("L2") .FormulaR1C1 = "=SUM(RC[-3]-RC[-4])" .AutoFill Range("L3:L" & iLastRow) End With with this Range("L2").FormulaR1C1 = "=SUM(RC[-3]-RC[-4])" Range("L2").Copy Range("L3:L" & iLastRow).Select ActiveSheet.Paste I saw a couple of thing that might be wrong with the autofill method but i would have to play with it and experiment. but since you have the data, i can't play with it. you did the first macro on record and this is basicly what you did just a little more genaric. not as pretty but should work. post back if it don't. regards FSt1 "traineeross" wrote: Thank-you very much, that has solved that bit but now when i run it i get this error in a grey box Run-time Error '1004': Autofill method of Range class failed and the options, end debug or help. Thank-you in advance Jody Williams "FSt1" wrote: hi, the line of code is not complete. in the VB editor, if the line is in red fonts then it wrapped on you. that line and the line below it should be 1 line of code. you can fix it by putting an underscore _ at the end of the line. this tells vb that the line continues. Range("J1102").FormulaR1C1 = "=COUNTIF(R[-" & iLastRow - 1 & _ "]C:R[-1]C,""3"")" also it looks like the line 2 lines down wrapped also. you can fix it the same way. Range("J1103").FormulaR1C1 = "=COUNTIF(R[-" & iLastRow & _ "]C:R[-2]C,""<=3"")" Regards FSt1 "traineeross" wrote: Thank-you very much for your help so far, it is much appreciated. However I just tried to run it and i got a message saying compile error: syntax error and this row was highlighted, Range("J1102").FormulaR1C1 = "=COUNTIF(R[-" & iLastRow - 1 & Hopefully you'll understand this. Many thanks Jody Williams "Bob Phillips" wrote: Sorry missed a bit Sub CRMReportMacro() ' ' CRMReportMacro Macro ' Macro recorded 11/08/2005 by Administrator ' ' Keyboard Shortcut: Ctrl+j ' Dim iLastRow As Long Columns("H:L").Insert Range("H1").Value = "Time Req" Range("I1").Value = "Time Closed" Range("L1").Value = "Time In Hours" iLastRow = Cells(Rows.Count, "F").End(xlUp).Row Range("F2:F" & iLastRow).Copy Range("H2") Range("G2:G" & iLastRow).Copy Range("I2") Application.CutCopyMode = False Range("L1").NumberFormat = "h:mm" With Range("L2") .FormulaR1C1 = "=SUM(RC[-3]-RC[-4])" .AutoFill Range("L3:L" & iLastRow) End With ActiveWorkbook.Save Range("K2").FormulaR1C1 = "DAYS" Range("K2").Copy Range("K3:K1" & iLastRow) Columns("K:K").ColumnWidth = 5.29 Range("J1").FormulaR1C1 = "Net-Work Days" Range("J2").FormulaR1C1 = "=NETWORKDAYS(RC[-4],RC[-3])" Columns("J:J").NumberFormat = "General" Range("J2").Copy Range("J3:J" & iLastRow) With Columns("J:J") With .FormatConditions .Delete .FormatConditions.Add Type:=xlCellValue, _ Operator:=xlLessEqual, _ Formula1:="3" .FormatConditions.Add Type:=xlCellValue, _ Operator:=xlLessEqual, _ Formula1:="3" End With With .FormatConditions(2).Font .Bold = True .Italic = False .ColorIndex = 3 End With End With Range("I1102").Value = "Over 2 Days" Range("I1103").Value = "Under 2 Days" Range("J1102").FormulaR1C1 = "=COUNTIF(R[-" & iLastRow - 1 & "]C:R[-1]C,""3"")" Application.Run Range("AUTOSAVE.XLA!mcs02.OnTime") Range("J1103").FormulaR1C1 = "=COUNTIF(R[-" & iLastRow & "]C:R[-2]C,""<=3"")" With Range("J1102:J1103").Font .Name = "Arial" .FontStyle = "Bold" .Size = 10 End With With Columns("L:L") .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom End With Application.CutCopyMode = False ActiveWorkbook.Save End Sub -- HTH RP (remove nothere from the email address if mailing direct) "traineeross" wrote in message ... I have recorded a macro to convert a report sent to us by another department. This involves inserting extra columns to use formulas to convert the data. However as the amount of rows varies with the amount of data, how do i get the macro to work by going to the last active cell in a column? It is the first long macro i have written so any help would be much appreciated. I have included the macro to help. Sub CRMReportMacro() ' ' CRMReportMacro Macro ' Macro recorded 11/08/2005 by Administrator ' ' Keyboard Shortcut: Ctrl+j ' Range("H2").Select Selection.EntireColumn.Insert Selection.EntireColumn.Insert Selection.EntireColumn.Insert Selection.EntireColumn.Insert Selection.EntireColumn.Insert Range("H1").Select ActiveCell.FormulaR1C1 = "Time Req" Range("I1").Select ActiveCell.FormulaR1C1 = "Time Closed" Range("L1").Select ActiveCell.FormulaR1C1 = "Time In Hours" Range("F2:F1101").Select Selection.Copy ActiveWindow.SmallScroll Down:=-27 ActiveWindow.LargeScroll Down:=-34 Range("H3").Select ActiveWindow.SmallScroll Down:=-6 Range("H2").Select ActiveSheet.Paste Range("G2:G1101").Select Application.CutCopyMode = False Selection.Copy ActiveWindow.LargeScroll Down:=-33 ActiveWindow.ScrollRow = 2 Range("I2").Select ActiveSheet.Paste Application.CutCopyMode = False ActiveWorkbook.Save Range("H:I,L:L").Select Range("L1").Activate Selection.NumberFormat = "h:mm" Range("L2").Select ActiveCell.FormulaR1C1 = "=SUM(RC[-3]-RC[-4])" Selection.Copy Range("L3:L1101").Select ActiveSheet.Paste ActiveWindow.SmallScroll Down:=-39 ActiveWindow.LargeScroll Down:=-33 ActiveWindow.ScrollRow = 2 Application.CutCopyMode = False ActiveWorkbook.Save Range("K2").Select ActiveCell.FormulaR1C1 = "DAYS" Range("K2").Select Selection.Copy Range("K3:K1101").Select ActiveSheet.Paste ActiveWindow.SmallScroll Down:=-9 |
|
#12
|
|||
|
|||
|
Sub Macro2()
' ' Macro2 Macro ' Macro recorded 12/08/2005 by Administrator ' 'Dim iLastRow As Long Columns("H:L").Insert Range("H1").Value = "Time Req" Range("I1").Value = "Time Closed" Range("L1").Value = "Time In Hours" iLastRow = Cells(Rows.Count, "F").End(xlUp).Row Range("F2:F" & iLastRow).Copy Range("H2") Range("G2:G" & iLastRow).Copy Range("I2") Application.CutCopyMode = False Range("L1").NumberFormat = "h:mm" Range("L2").FormulaR1C1 = "=SUM(RC[-3]-RC[-4])" Range("L2").Copy Range("L3:L" & iLastRow).Select ActiveSheet.Paste ActiveWorkbook.Save Range("K2").FormulaR1C1 = "DAYS" Range("K2").Copy Range("K3:K1" & iLastRow) Columns("K:K").ColumnWidth = 5.29 Range("J1").FormulaR1C1 = "NetWork Days" Range("J2").FormulaR1C1 = "=NETWORKDAYS(RC[-4],RC[-3])" Columns("J:J").NumberFormat = "General" Range("J2").Copy Range("J3:J" & iLastRow) With Columns("J:J") With .FormatConditions .Delete .Add Type:=xlCellValue, _ Operator:=xlLessEqual, _ Formula1:="3" .Add Type:=xlCellValue, _ Operator:=xlLessEqual, _ Formula1:="3" End With With .FormatConditions(2).Font .Bold = True .Italic = False .ColorIndex = 3 End With End With Range("I1102").Value = "Over 2 Days" Range("I1103").Value = "Under 2 Days" Range("J1102").FormulaR1C1 = _ "=COUNTIF(R[" & iLastRow - 1 & _ "]C:R[-1]C,""3"")" ' Application.Run Range("AUTOSAVE.XLA!mcs02.OnTime") Range("J1103").FormulaR1C1 = _ "=COUNTIF(R[-" & iLastRow & _ "]C:R[-2]C,""<=3"")" With Range("J1102:J1103").Font .Name = "Arial" .FontStyle = "Bold" .Size = 10 End With With Columns("L:L") .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom End With Application.CutCopyMode = False ActiveWorkbook.Save End Sub when you past it in your code module, if you get errors, then print out the post and compare the code there with what was pasted. Sometimes Google inserts stray hyphens. If you find any, then delete them. -- Regards, Tom Ogilvy "traineeross" wrote in message ... Thank-you that fixed that bit although i now get- Run time error '438': Object doesn't support this property or method I have included the whole macro to help, Thank-you for continuing to try and help it is very much appreciated. Sub Macro2() ' ' Macro2 Macro ' Macro recorded 12/08/2005 by Administrator ' 'Dim iLastRow As Long Columns("H:L").Insert Range("H1").Value = "Time Req" Range("I1").Value = "Time Closed" Range("L1").Value = "Time In Hours" iLastRow = Cells(Rows.Count, "F").End(xlUp).Row Range("F2:F" & iLastRow).Copy Range("H2") Range("G2:G" & iLastRow).Copy Range("I2") Application.CutCopyMode = False Range("L1").NumberFormat = "h:mm" Range("L2").FormulaR1C1 = "=SUM(RC[-3]-RC[-4])" Range("L2").Copy Range("L3:L" & iLastRow).Select ActiveSheet.Paste ActiveWorkbook.Save Range("K2").FormulaR1C1 = "DAYS" Range("K2").Copy Range("K3:K1" & iLastRow) Columns("K:K").ColumnWidth = 5.29 Range("J1").FormulaR1C1 = "Net-Work Days" Range("J2").FormulaR1C1 = "=NETWORKDAYS(RC[-4],RC[-3])" Columns("J:J").NumberFormat = "General" Range("J2").Copy Range("J3:J" & iLastRow) With Columns("J:J") With .FormatConditions .Delete .FormatConditions.Add Type:=xlCellValue, _ Operator:=xlLessEqual, _ Formula1:="3" .FormatConditions.Add Type:=xlCellValue, _ Operator:=xlLessEqual, _ Formula1:="3" End With With .FormatConditions(2).Font .Bold = True .Italic = False .ColorIndex = 3 End With End With Range("I1102").Value = "Over 2 Days" Range("I1103").Value = "Under 2 Days" Range("J1102").FormulaR1C1 = "=COUNTIF(R[-" & iLastRow - 1 & _ "]C:R[-1]C,""3"")" Application.Run Range("AUTOSAVE.XLA!mcs02.OnTime") Range("J1103").FormulaR1C1 = "=COUNTIF(R[-" & iLastRow & _ "]C:R[-2]C,""<=3"")" With Range("J1102:J1103").Font .Name = "Arial" .FontStyle = "Bold" .Size = 10 End With With Columns("L:L") .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom End With Application.CutCopyMode = False ActiveWorkbook.Save End Sub "FSt1" wrote: hi, in your code replace this With Range("L2") .FormulaR1C1 = "=SUM(RC[-3]-RC[-4])" .AutoFill Range("L3:L" & iLastRow) End With with this Range("L2").FormulaR1C1 = "=SUM(RC[-3]-RC[-4])" Range("L2").Copy Range("L3:L" & iLastRow).Select ActiveSheet.Paste I saw a couple of thing that might be wrong with the autofill method but i would have to play with it and experiment. but since you have the data, i can't play with it. you did the first macro on record and this is basicly what you did just a little more genaric. not as pretty but should work. post back if it don't. regards FSt1 "traineeross" wrote: Thank-you very much, that has solved that bit but now when i run it i get this error in a grey box Run-time Error '1004': Autofill method of Range class failed and the options, end debug or help. Thank-you in advance Jody Williams "FSt1" wrote: hi, the line of code is not complete. in the VB editor, if the line is in red fonts then it wrapped on you. that line and the line below it should be 1 line of code. you can fix it by putting an underscore _ at the end of the line. this tells vb that the line continues. Range("J1102").FormulaR1C1 = "=COUNTIF(R[-" & iLastRow - 1 & _ "]C:R[-1]C,""3"")" also it looks like the line 2 lines down wrapped also. you can fix it the same way. Range("J1103").FormulaR1C1 = "=COUNTIF(R[-" & iLastRow & _ "]C:R[-2]C,""<=3"")" Regards FSt1 "traineeross" wrote: Thank-you very much for your help so far, it is much appreciated. However I just tried to run it and i got a message saying compile error: syntax error and this row was highlighted, Range("J1102").FormulaR1C1 = "=COUNTIF(R[-" & iLastRow - 1 & Hopefully you'll understand this. Many thanks Jody Williams "Bob Phillips" wrote: Sorry missed a bit Sub CRMReportMacro() ' ' CRMReportMacro Macro ' Macro recorded 11/08/2005 by Administrator ' ' Keyboard Shortcut: Ctrl+j ' Dim iLastRow As Long Columns("H:L").Insert Range("H1").Value = "Time Req" Range("I1").Value = "Time Closed" Range("L1").Value = "Time In Hours" iLastRow = Cells(Rows.Count, "F").End(xlUp).Row Range("F2:F" & iLastRow).Copy Range("H2") Range("G2:G" & iLastRow).Copy Range("I2") Application.CutCopyMode = False Range("L1").NumberFormat = "h:mm" With Range("L2") .FormulaR1C1 = "=SUM(RC[-3]-RC[-4])" .AutoFill Range("L3:L" & iLastRow) End With ActiveWorkbook.Save Range("K2").FormulaR1C1 = "DAYS" Range("K2").Copy Range("K3:K1" & iLastRow) Columns("K:K").ColumnWidth = 5.29 Range("J1").FormulaR1C1 = "Net-Work Days" Range("J2").FormulaR1C1 = "=NETWORKDAYS(RC[-4],RC[-3])" Columns("J:J").NumberFormat = "General" Range("J2").Copy Range("J3:J" & iLastRow) With Columns("J:J") With .FormatConditions .Delete .FormatConditions.Add Type:=xlCellValue, _ Operator:=xlLessEqual, _ Formula1:="3" .FormatConditions.Add Type:=xlCellValue, _ Operator:=xlLessEqual, _ Formula1:="3" End With With .FormatConditions(2).Font .Bold = True .Italic = False .ColorIndex = 3 End With End With Range("I1102").Value = "Over 2 Days" Range("I1103").Value = "Under 2 Days" Range("J1102").FormulaR1C1 = "=COUNTIF(R[-" & iLastRow - 1 & "]C:R[-1]C,""3"")" Application.Run Range("AUTOSAVE.XLA!mcs02.OnTime") Range("J1103").FormulaR1C1 = "=COUNTIF(R[-" & iLastRow & "]C:R[-2]C,""<=3"")" With Range("J1102:J1103").Font .Name = "Arial" .FontStyle = "Bold" .Size = 10 End With With Columns("L:L") .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom End With Application.CutCopyMode = False ActiveWorkbook.Save End Sub -- HTH RP (remove nothere from the email address if mailing direct) "traineeross" wrote in message ... I have recorded a macro to convert a report sent to us by another department. This involves inserting extra columns to use formulas to convert the data. However as the amount of rows varies with the amount of data, how do i get the macro to work by going to the last active cell in a column? It is the first long macro i have written so any help would be much appreciated. I have included the macro to help. Sub CRMReportMacro() ' ' CRMReportMacro Macro ' Macro recorded 11/08/2005 by Administrator ' ' Keyboard Shortcut: Ctrl+j ' Range("H2").Select Selection.EntireColumn.Insert Selection.EntireColumn.Insert Selection.EntireColumn.Insert Selection.EntireColumn.Insert Selection.EntireColumn.Insert Range("H1").Select ActiveCell.FormulaR1C1 = "Time Req" Range("I1").Select ActiveCell.FormulaR1C1 = "Time Closed" Range("L1").Select ActiveCell.FormulaR1C1 = "Time In Hours" Range("F2:F1101").Select Selection.Copy ActiveWindow.SmallScroll Down:=-27 ActiveWindow.LargeScroll Down:=-34 Range("H3").Select ActiveWindow.SmallScroll Down:=-6 Range("H2").Select ActiveSheet.Paste Range("G2:G1101").Select Application.CutCopyMode = False Selection.Copy ActiveWindow.LargeScroll Down:=-33 ActiveWindow.ScrollRow = 2 Range("I2").Select ActiveSheet.Paste Application.CutCopyMode = False ActiveWorkbook.Save Range("H:I,L:L").Select Range("L1").Activate Selection.NumberFormat = "h:mm" Range("L2").Select ActiveCell.FormulaR1C1 = "=SUM(RC[-3]-RC[-4])" Selection.Copy Range("L3:L1101").Select ActiveSheet.Paste ActiveWindow.SmallScroll Down:=-39 ActiveWindow.LargeScroll Down:=-33 ActiveWindow.ScrollRow = 2 Application.CutCopyMode = False ActiveWorkbook.Save Range("K2").Select ActiveCell.FormulaR1C1 = "DAYS" Range("K2").Select Selection.Copy Range("K3:K1101").Select ActiveSheet.Paste ActiveWindow.SmallScroll Down:=-9 Columns("K:K").ColumnWidth = 5.29 ActiveWindow.LargeScroll Down:=-35 Application.CutCopyMode = False ActiveWorkbook.Save Range("J1").Select ActiveCell.FormulaR1C1 = "Net-Work Days" Range("J2").Select ActiveCell.FormulaR1C1 = "=NETWORKDAYS(RC[-4],RC[-3])" Columns("J:J").Select Selection.NumberFormat = "General" Range("J2").Select Selection.Copy Range("J3:J1101").Select ActiveSheet.Paste Application.CutCopyMode = False ActiveWorkbook.Save ActiveWindow.LargeScroll Down:=-35 Columns("J:J").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _ Formula1:="3" Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="3" With Selection.FormatConditions(2).Font .Bold = True .Italic = False .ColorIndex = 3 End With ActiveWindow.SmallScroll Down:=546 ActiveWindow.LargeScroll Down:=16 ActiveWindow.ScrollRow = 1071 ActiveWindow.SmallScroll Down:=15 Range("I1102").Select ActiveCell.FormulaR1C1 = "Over 2 Days" Range("I1103").Select ActiveCell.FormulaR1C1 = "Under 2 Days" Range("J1102").Select ActiveCell.FormulaR1C1 = "=COUNTIF(R[-1100]C:R[-1]C,""3"")" Application.Run Range("AUTOSAVE.XLA!mcs02.OnTime") ActiveWindow.SmallScroll Down:=12 Range("J1103").Select ActiveCell.FormulaR1C1 = "=COUNTIF(R[-1101]C:R[-2]C,""<=3"")" ActiveWindow.SmallScroll Down:=12 Range("J1102:J1103").Select With Selection.Font .Name = "Arial" .FontStyle = "Bold" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With ActiveWindow.SmallScroll Down:=-45 Columns("L:L").Select With Selection .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .MergeCells = False End With End Sub |
|
#13
|
|||
|
|||
|
Brilliant thank-you very much.
Only one thing left now i've noticed, these parts needs to go one cell below the last active cell in row I and J . How do i tell it to go one cell below the last active cell ? Range("I1102").Value = "Over 2 Days" Range("I1103").Value = "Under 2 Days" Range("J1102").FormulaR1C1 = "=COUNTIF(R[-" & iLastRow - 1 & _ "]C:R[-1]C,""3"")" Application.Run Range("AUTOSAVE.XLA!mcs02.OnTime") Range("J1103").FormulaR1C1 = "=COUNTIF(R[-" & iLastRow & _ "]C:R[-2]C,""<=3"")" Your help is very much appreciated, Jody Williams "FSt1" wrote: hi, in your code replace this. With Columns("J:J") With .FormatConditions .Delete .FormatConditions.Add Type:=xlCellValue, _ Operator:=xlLessEqual, _ Formula1:="3" .FormatConditions.Add Type:=xlCellValue, _ Operator:=xlLessEqual, _ Formula1:="3" End With With .FormatConditions(2).Font .Bold = True .Italic = False .ColorIndex = 3 End With End With with this...... Columns("J:J").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _ Formula1:="3" Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="3" With Selection.FormatConditions(2).Font .Bold = True .Italic = False .ColorIndex = 3 End With Again we are simplely replace the trouble part with what you did on record. I copied this to my vb editor and stepped throught it. it worked until i hit the line Application.Run Range("AUTOSAVE.XLA!mcs02.OnTime") i don't have that so it crashed on me. try it. good luck regards FSt1 "traineeross" wrote: Thank-you that fixed that bit although i now get- Run time error '438': Object doesn't support this property or method I have included the whole macro to help, Thank-you for continuing to try and help it is very much appreciated. Sub Macro2() ' ' Macro2 Macro ' Macro recorded 12/08/2005 by Administrator ' 'Dim iLastRow As Long Columns("H:L").Insert Range("H1").Value = "Time Req" Range("I1").Value = "Time Closed" Range("L1").Value = "Time In Hours" iLastRow = Cells(Rows.Count, "F").End(xlUp).Row Range("F2:F" & iLastRow).Copy Range("H2") Range("G2:G" & iLastRow).Copy Range("I2") Application.CutCopyMode = False Range("L1").NumberFormat = "h:mm" Range("L2").FormulaR1C1 = "=SUM(RC[-3]-RC[-4])" Range("L2").Copy Range("L3:L" & iLastRow).Select ActiveSheet.Paste ActiveWorkbook.Save Range("K2").FormulaR1C1 = "DAYS" Range("K2").Copy Range("K3:K1" & iLastRow) Columns("K:K").ColumnWidth = 5.29 Range("J1").FormulaR1C1 = "Net-Work Days" Range("J2").FormulaR1C1 = "=NETWORKDAYS(RC[-4],RC[-3])" Columns("J:J").NumberFormat = "General" Range("J2").Copy Range("J3:J" & iLastRow) With Columns("J:J") With .FormatConditions .Delete .FormatConditions.Add Type:=xlCellValue, _ Operator:=xlLessEqual, _ Formula1:="3" .FormatConditions.Add Type:=xlCellValue, _ Operator:=xlLessEqual, _ Formula1:="3" End With With .FormatConditions(2).Font .Bold = True .Italic = False .ColorIndex = 3 End With End With Range("I1102").Value = "Over 2 Days" Range("I1103").Value = "Under 2 Days" Range("J1102").FormulaR1C1 = "=COUNTIF(R[-" & iLastRow - 1 & _ "]C:R[-1]C,""3"")" Application.Run Range("AUTOSAVE.XLA!mcs02.OnTime") Range("J1103").FormulaR1C1 = "=COUNTIF(R[-" & iLastRow & _ "]C:R[-2]C,""<=3"")" With Range("J1102:J1103").Font .Name = "Arial" .FontStyle = "Bold" .Size = 10 End With With Columns("L:L") .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom End With Application.CutCopyMode = False ActiveWorkbook.Save End Sub "FSt1" wrote: hi, in your code replace this With Range("L2") .FormulaR1C1 = "=SUM(RC[-3]-RC[-4])" .AutoFill Range("L3:L" & iLastRow) End With with this Range("L2").FormulaR1C1 = "=SUM(RC[-3]-RC[-4])" Range("L2").Copy Range("L3:L" & iLastRow).Select ActiveSheet.Paste I saw a couple of thing that might be wrong with the autofill method but i would have to play with it and experiment. but since you have the data, i can't play with it. you did the first macro on record and this is basicly what you did just a little more genaric. not as pretty but should work. post back if it don't. regards FSt1 "traineeross" wrote: Thank-you very much, that has solved that bit but now when i run it i get this error in a grey box Run-time Error '1004': Autofill method of Range class failed and the options, end debug or help. Thank-you in advance Jody Williams "FSt1" wrote: hi, the line of code is not complete. in the VB editor, if the line is in red fonts then it wrapped on you. that line and the line below it should be 1 line of code. you can fix it by putting an underscore _ at the end of the line. this tells vb that the line continues. Range("J1102").FormulaR1C1 = "=COUNTIF(R[-" & iLastRow - 1 & _ "]C:R[-1]C,""3"")" also it looks like the line 2 lines down wrapped also. you can fix it the same way. Range("J1103").FormulaR1C1 = "=COUNTIF(R[-" & iLastRow & _ "]C:R[-2]C,""<=3"")" Regards FSt1 "traineeross" wrote: Thank-you very much for your help so far, it is much appreciated. However I just tried to run it and i got a message saying compile error: syntax error and this row was highlighted, Range("J1102").FormulaR1C1 = "=COUNTIF(R[-" & iLastRow - 1 & Hopefully you'll understand this. Many thanks Jody Williams "Bob Phillips" wrote: Sorry missed a bit Sub CRMReportMacro() ' ' CRMReportMacro Macro ' Macro recorded 11/08/2005 by Administrator ' ' Keyboard Shortcut: Ctrl+j ' Dim iLastRow As Long Columns("H:L").Insert Range("H1").Value = "Time Req" Range("I1").Value = "Time Closed" Range("L1").Value = "Time In Hours" iLastRow = Cells(Rows.Count, "F").End(xlUp).Row Range("F2:F" & iLastRow).Copy Range("H2") Range("G2:G" & iLastRow).Copy Range("I2") Application.CutCopyMode = False Range("L1").NumberFormat = "h:mm" With Range("L2") .FormulaR1C1 = "=SUM(RC[-3]-RC[-4])" .AutoFill Range("L3:L" & iLastRow) End With ActiveWorkbook.Save Range("K2").FormulaR1C1 = "DAYS" Range("K2").Copy Range("K3:K1" & iLastRow) Columns("K:K").ColumnWidth = 5.29 Range("J1").FormulaR1C1 = "Net-Work Days" Range("J2").FormulaR1C1 = "=NETWORKDAYS(RC[-4],RC[-3])" Columns("J:J").NumberFormat = "General" Range("J2").Copy Range("J3:J" & iLastRow) With Columns("J:J") With .FormatConditions .Delete .FormatConditions.Add Type:=xlCellValue, _ Operator:=xlLessEqual, _ Formula1:="3" .FormatConditions.Add Type:=xlCellValue, _ Operator:=xlLessEqual, _ Formula1:="3" End With With .FormatConditions(2).Font .Bold = True .Italic = False .ColorIndex = 3 End With End With Range("I1102").Value = "Over 2 Days" Range("I1103").Value = "Under 2 Days" Range("J1102").FormulaR1C1 = "=COUNTIF(R[-" & iLastRow - 1 & "]C:R[-1]C,""3"")" Application.Run Range("AUTOSAVE.XLA!mcs02.OnTime") Range("J1103").FormulaR1C1 = "=COUNTIF(R[-" & iLastRow & "]C:R[-2]C,""<=3"")" With Range("J1102:J1103").Font .Name = "Arial" .FontStyle = "Bold" .Size = 10 End With With Columns("L:L") .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom End With Application.CutCopyMode = False ActiveWorkbook.Save End Sub -- HTH RP (remove nothere from the email address if mailing direct) "traineeross" wrote in message ... I have recorded a macro to convert a report sent to us by another department. This involves inserting extra columns to use formulas to convert the data. However as the amount of rows varies with the amount of data, how do i get the macro to work by going to the last active cell in a column? It is the first long macro i have written so any help would be much appreciated. I have included the macro to help. Sub CRMReportMacro() ' ' CRMReportMacro Macro ' Macro recorded 11/08/2005 by Administrator ' ' Keyboard Shortcut: Ctrl+j |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Hyperlink in Excel 2000 can't open bookmarked Word 2000 file | Excel Discussion (Misc queries) | |||
| Excel XP 2002 Vs. Excel 2000 | Excel Discussion (Misc queries) | |||
| other systems detecting excel 4.0 if excel 2000 is installed | Setting up and Configuration of Excel | |||
| How do I Break links Using Excel 2000 | Excel Discussion (Misc queries) | |||
| Statistical Excel Function Question within Excel 2000... | Excel Worksheet Functions | |||
Linear Mode
