Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Macros in excel 2000
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default Macros in excel 2000
I have recorded a macro to convert a report sent to us by another department.
This involves inserting extra columns to use formulas to convert the data.
However as the amount of rows varies with the amount of data, how do i get
the macro to work by going to the last active cell in a column? It is the
first long macro i have written so any help would be much appreciated. I have
included the macro to help.

Sub CRMReportMacro()
'
' CRMReportMacro Macro
' Macro recorded 11/08/2005 by Administrator
'
' Keyboard Shortcut: Ctrl+j
'
Range("H2").Select
Selection.EntireColumn.Insert
Selection.EntireColumn.Insert
Selection.EntireColumn.Insert
Selection.EntireColumn.Insert
Selection.EntireColumn.Insert
Range("H1").Select
ActiveCell.FormulaR1C1 = "Time Req"
Range("I1").Select
ActiveCell.FormulaR1C1 = "Time Closed"
Range("L1").Select
ActiveCell.FormulaR1C1 = "Time In Hours"
Range("F2:F1101").Select
Selection.Copy
ActiveWindow.SmallScroll Down:=-27
ActiveWindow.LargeScroll Down:=-34
Range("H3").Select
ActiveWindow.SmallScroll Down:=-6
Range("H2").Select
ActiveSheet.Paste
Range("G2:G1101").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.LargeScroll Down:=-33
ActiveWindow.ScrollRow = 2
Range("I2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Save
Range("H:I,L:L").Select
Range("L1").Activate
Selection.NumberFormat = "h:mm"
Range("L2").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-3]-RC[-4])"
Selection.Copy
Range("L3:L1101").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=-39
ActiveWindow.LargeScroll Down:=-33
ActiveWindow.ScrollRow = 2
Application.CutCopyMode = False
ActiveWorkbook.Save
Range("K2").Select
ActiveCell.FormulaR1C1 = "DAYS"
Range("K2").Select
Selection.Copy
Range("K3:K1101").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=-9
Columns("K:K").ColumnWidth = 5.29
ActiveWindow.LargeScroll Down:=-35
Application.CutCopyMode = False
ActiveWorkbook.Save
Range("J1").Select
ActiveCell.FormulaR1C1 = "Net-Work Days"
Range("J2").Select
ActiveCell.FormulaR1C1 = "=NETWORKDAYS(RC[-4],RC[-3])"
Columns("J:J").Select
Selection.NumberFormat = "General"
Range("J2").Select
Selection.Copy
Range("J3:J1101").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWindow.LargeScroll Down:=-35
Columns("J:J").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _
Formula1:="3"
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="3"
With Selection.FormatConditions(2).Font
.Bold = True
.Italic = False
.ColorIndex = 3
End With
ActiveWindow.SmallScroll Down:=546
ActiveWindow.LargeScroll Down:=16
ActiveWindow.ScrollRow = 1071
ActiveWindow.SmallScroll Down:=15
Range("I1102").Select
ActiveCell.FormulaR1C1 = "Over 2 Days"
Range("I1103").Select
ActiveCell.FormulaR1C1 = "Under 2 Days"
Range("J1102").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-1100]C:R[-1]C,""3"")"
Application.Run Range("AUTOSAVE.XLA!mcs02.OnTime")
ActiveWindow.SmallScroll Down:=12
Range("J1103").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-1101]C:R[-2]C,""<=3"")"
ActiveWindow.SmallScroll Down:=12
Range("J1102:J1103").Select
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
ActiveWindow.SmallScroll Down:=-45
Columns("L:L").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
End Sub


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,942
Default Macros in excel 2000
see your post in general questions.

FSt1

"traineeross" wrote:

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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default Macros in excel 2000
from Misc:

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 12/08/2005 by Administrator
'

'Dim iLastRow As Long

Columns("H:L").Insert
Range("H1").Value = "Time Req"
Range("I1").Value = "Time Closed"
Range("L1").Value = "Time In Hours"
iLastRow = Cells(Rows.Count, "F").End(xlUp).Row
Range("F2:F" & iLastRow).Copy Range("H2")
Range("G2:G" & iLastRow).Copy Range("I2")
Application.CutCopyMode = False
Range("L1").NumberFormat = "h:mm"
Range("L2").FormulaR1C1 = "=SUM(RC[-3]-RC[-4])"
Range("L2").Copy
Range("L3:L" & iLastRow).Select
ActiveSheet.Paste

ActiveWorkbook.Save
Range("K2").FormulaR1C1 = "DAYS"
Range("K2").Copy Range("K3:K1" & iLastRow)
Columns("K:K").ColumnWidth = 5.29
Range("J1").FormulaR1C1 = "NetWork Days"
Range("J2").FormulaR1C1 = "=NETWORKDAYS(RC[-4],RC[-3])"
Columns("J:J").NumberFormat = "General"
Range("J2").Copy Range("J3:J" & iLastRow)
With Columns("J:J")
With .FormatConditions
.Delete
.Add Type:=xlCellValue, _
Operator:=xlLessEqual, _
Formula1:="3"
.Add Type:=xlCellValue, _
Operator:=xlLessEqual, _
Formula1:="3"
End With
With .FormatConditions(2).Font
.Bold = True
.Italic = False
.ColorIndex = 3
End With
End With
Range("I1102").Value = "Over 2 Days"
Range("I1103").Value = "Under 2 Days"
Range("J1102").FormulaR1C1 = _
"=COUNTIF(R[" & iLastRow - 1 & _
"]C:R[-1]C,""3"")"
' Application.Run Range("AUTOSAVE.XLA!mcs02.OnTime")
Range("J1103").FormulaR1C1 = _
"=COUNTIF(R[-" & iLastRow & _
"]C:R[-2]C,""<=3"")"
With Range("J1102:J1103").Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
End With
With Columns("L:L")
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
End With
Application.CutCopyMode = False
ActiveWorkbook.Save
End Sub

when you past it in your code module, if you get errors, then print out the
post and compare the code there with what was pasted. Sometimes Google
inserts stray hyphens. If you find any, then delete them.

--
Regards,
Tom Ogilvy

"traineeross" wrote in message
...
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




Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Macros for Excel 2000 JHL Setting up and Configuration of Excel 6 June 10th 06 02:22 AM
Macros in excel 2000 traineeross Excel Discussion (Misc queries) 2 August 13th 05 09:43 AM
Macros in excel 2000 traineeross Excel Discussion (Misc queries) 12 August 12th 05 03:01 PM
excel 2000 macros JJIM_RR Excel Programming 2 July 21st 05 06:27 PM
Macros--excel 2000... dp981z New Users to Excel 1 February 1st 05 06:41 AM


All times are GMT +1. The time now is 06:44 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement