FanofExcel18
Board Regular
- Joined
- Jun 7, 2018
- Messages
- 65
I have dynamic report that will grow by about 200 rows per day. I'm new to VBA and trying to get the formulas to only calculate based on Active or filled rows.
Any help would be great:
Any help would be great:
Code:
Sub step1()
'
' step1 Macro
'
' Keyboard Shortcut: Ctrl+r
'
Columns("W:W").Select
Selection.Delete Shift:=xlToLeft
Range("W2").Select
ActiveCell.FormulaR1C1 = "=TEXT(RC[-3],""hh AM/PM"")"
Range("X2").Select
ActiveCell.FormulaR1C1 = "=TEXT(RC[-4],""mm/dd/yyyy"")"
Range("Y2").Select
ActiveCell.FormulaR1C1 = "=TEXT(RC[-3],""mm/dd/yyyy"")"
Range("Z2").Select
ActiveCell.FormulaR1C1 = "=MONTH(RC[-6])"
Range("AA2").Select
ActiveCell.FormulaR1C1 = "=TEXT(RC[-7],""ddd"")"
Range("AB2").Select
ActiveCell.FormulaR1C1 = "=RC[-8]"
Range("AC2").Select
ActiveCell.FormulaR1C1 = "=(RC[-7]-RC[-9])*1440"
Range("AD2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/60"
Range("AE2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/24"
Range("AF2").Select
ActiveCell.FormulaR1C1 = "=TODAY()-RC[-11]"
Range("AG2").Select
Range("AB2").Select
ActiveCell.FormulaR1C1 = _
"=WEEKNUM(RC[-8],1)-WEEKNUM(DATE(YEAR(RC[-8]),MONTH(RC[-8]),1),1)+1"
Range("AG2").Select
Columns("AB:AB").Select
Selection.NumberFormat = "General"
Columns("AF:AF").Select
Selection.NumberFormat = "General"
Range("AG2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-29],Sheet2!C[-26]:C[-25],2,0)"
Range("AH2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-26],Sheet2!C[-33]:C[-31],3,0)"
Range("AI2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(Sheet1!RC[-27],Sheet2!C[-34]:C[-33],2,0)"
Range("W2:AI2").Select
Selection.AutoFill Destination:=Range("W2:AI17578")
Range("W2:AI17578").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("AC2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.NumberFormat = "0"
Range("AF2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.NumberFormat = "0"
Range("AD4:AE4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.NumberFormat = "0"
Range("AC2:AE2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Replace What:="#VALUE!", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("AC17578").Select
Columns("AH:AI").Select
Range("AI17571").Activate
Selection.Replace What:="#N/A", Replacement:="N/A", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("W:W").Select
Range("W17571").Activate
Selection.TextToColumns Destination:=Range("W1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Range("A1").Select
End Sub
Last edited by a moderator: