I am relatively new to VBA, but was “recruited” by my company to help create a resource forecasting tool. I get a 6 second delay from the moment that I run my script until the moment it completes, and I really would like to do this up to 1 or 2 seconds if possible.
As a side note, I probably have the absurd number of "IF", "Index / Match" and "Indirect" on my excel sheet, they all work by the end date of the week from column K to column EY .. I'm not sure that this is my problem or if it is in my code.
like FYI, here is an example of one of the excel formulas - if it seems overloaded and I have to change it, feel free to tell me
=IF(INDEX(INDIRECT("$A$3:$M$"&$K$4)),MATCH($B26,INDIRECT("$K$3:$K$"&$K$4)),0),COLUMN(F26))<40000,"InputDate",INDEX(INDIRECT("$A$3:$M$"&$K$4),MATCH($B26,INDIRECT("$K$3:$K$"&$K$4)),0),COLUMN(F26))))
Here is my VBA code working in 3 steps.
Marko4 () - Insert rows into the project description table, CopyM () - creates a row from the project table (client name, Proj #, project name) Brief description of the project () - Copy the last range of 7 lines, and then paste them into one line below the last line.
Sub NewProject()
Macro4
CopyM
ProjectSummary
End Sub
Sub Macro4()
'insert a new line in the project description field (top)
Range("A1").End(xlDown).End(xlDown).Offset(1).EntireRow.Insert Shift:=xlDown,
CopyOrigin:=xlFormatFromLeftOrAbove
Range("G3").Copy Destination:=Range("H1").End(xlDown).End(xlDown)
End Sub
-----------------------------------------------------------
Sub CopyM()
'Copy range of cells in column K which correspond only to project description field
Dim rng As Range
Dim rng2 As Range
Set rng = ActiveSheet.Range("K1").End(xlDown).End(xlDown)
Set rng2 = rng.Offset(1)
rng.Copy Destination:=rng2
Application.CutCopyMode = False
End Sub
Sub ProjectSummary()
Dim lastrowe As Integer
Dim lastrowb As Integer
Dim pnext As Integer
lastrowe = Range("D2000").End(xlUp).Offset(2).Row
lastrowb = Range("A2000").End(xlUp).Offset(-1).Row
Rows(lastrowb & ":" & lastrowe).Copy
pnext = Range("D2000").End(xlUp).Offset(3).Row
Rows(pnext).Insert Shift:=xlDown
End Sub
source
share