Macros in excel 2000

G

Guest

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
 
B

Bob Phillips

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 said:
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
Click to expand...
 
B

Bob Phillips

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 said:
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
Click to expand...
 
G

Guest

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 said:
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 said:
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
Click to expand...
Click to expand...
 
B

Bob Phillips

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 said:
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 said:
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 said:
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.
Click to expand...
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,
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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 said:
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 said:
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 said:
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)


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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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 said:
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 said:
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 said:
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)


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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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 said:
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 said:
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 said:
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

:

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)


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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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 said:
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 said:
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 said:
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

:

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

:

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)


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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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 said:
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 said:
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 said:
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


:

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

:

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

:

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)


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
Click to expand...
Click to expand...
Click to expand...
 
T

Tom Ogilvy

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 said:
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 said:
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 said:
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


:

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

:

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

:

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)


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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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 said:
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 said:
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 said:
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

:

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


:

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

:

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

:

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)


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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Copy & paste macro for multiple worksheets 8
Sub-Totaling 2
macros creating 2
Macro help 3
Macro Flexability 7
Problem with Macro 7
I need a simple loop with a 4+ row added in. 5
variable number of rows. 2

Top