I've just started learning VBA/macros today. I've got a macro that works, it's just unusuably slow. Does anyone have any good ideas of how to speed it up/replace it with something better?
Essentially, I have up to 20 potential different end dates, and I want to self-populate a sequential list of dates from a set start date, to the latest end date. Hopefully I've made some sense, and the code below fills in any gaps.
Sub DateAutoFill()
Dim StartD As Date, EndD As Date
StartD = Worksheets("Calculator").Range("B55")
If IsDate(Worksheets("Calculator").Range("AN60")) Then
EndD = Worksheets("Calculator").Range("AN60")
Else
If IsDate(Worksheets("Calculator").Range("AL60")) Then
EndD = Worksheets("Calculator").Range("AL60")
Else
If IsDate(Worksheets("Calculator").Range("AJ60")) Then
EndD = Worksheets("Calculator").Range("AJ60")
Else
If IsDate(Worksheets("Calculator").Range("AH60")) Then
EndD = Worksheets("Calculator").Range("AH60")
Else
If IsDate(Worksheets("Calculator").Range("AF60")) Then
EndD = Worksheets("Calculator").Range("AF60")
Else
If IsDate(Worksheets("Calculator").Range("AD60")) Then
EndD = Worksheets("Calculator").Range("AD60")
Else
If IsDate(Worksheets("Calculator").Range("AB60")) Then
EndD = Worksheets("Calculator").Range("AB60")
Else
If IsDate(Worksheets("Calculator").Range("Z60")) Then
EndD = Worksheets("Calculator").Range("Z60")
Else
If IsDate(Worksheets("Calculator").Range("X60")) Then
EndD = Worksheets("Calculator").Range("X60")
Else
If IsDate(Worksheets("Calculator").Range("V60")) Then
EndD = Worksheets("Calculator").Range("V60")
Else
If IsDate(Worksheets("Calculator").Range("T60")) Then
EndD = Worksheets("Calculator").Range("T60")
Else
If IsDate(Worksheets("Calculator").Range("R60")) Then
EndD = Worksheets("Calculator").Range("R60")
Else
If IsDate(Worksheets("Calculator").Range("P60")) Then
EndD = Worksheets("Calculator").Range("P60")
Else
If IsDate(Worksheets("Calculator").Range("N60")) Then
EndD = Worksheets("Calculator").Range("N60")
Else
If IsDate(Worksheets("Calculator").Range("L60")) Then
EndD = Worksheets("Calculator").Range("L60")
Else
If IsDate(Worksheets("Calculator").Range("J60")) Then
EndD = Worksheets("Calculator").Range("J60")
Else
If IsDate(Worksheets("Calculator").Range("H60")) Then
EndD = Worksheets("Calculator").Range("H60")
Else
If IsDate(Worksheets("Calculator").Range("FD60")) Then
EndD = Worksheets("Calculator").Range("F60")
Else
If IsDate(Worksheets("Calculator").Range("D60")) Then
EndD = Worksheets("Calculator").Range("D60")
Else
If IsDate(Worksheets("Calculator").Range("B60")) Then
EndD = Worksheets("Calculator").Range("B60")
Else
MsgBox "Enter Investment Period Section on Calculator Sheet"
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
For Row = 1 To EndD - StartD
Cells(Row, 44) = StartD Row - 1
Next Row
End Sub
It worked! It just took so long it's not useable. I am hoping to get something that is equivalent to drag and drop, or a simple A1 1 formula, that can end at the oldest end date of up to 20 dates.
CodePudding user response：
(1) Don't repeat your worksheet-name over and over again, use a With clause instead.
(2) There is an ElseIf-statement in VBA, if you use that, it makes the code already more readable
(3) It seems that you are checking every second column, starting from AN (that's column number 40) back to B (2). You could use a loop to check for the end date.
Dim StartD As Date, EndD as Date
With Worksheets("Calculator")
StartD = .Range("B55")
Dim col As Long
For col = 40 to 2 step -2 ' Look at every 2nd col, starting at AN (=40)
If IsDate(.Cells(60, col)) Then
EndD = .Cells(60, col)
Exit For
End If
Next
End With
If EndD = 0 Then
MsgBox ...
Else
...
End If
CodePudding user response：
There are a few ways to make the code ore readable.
If ElseIf Else
Sub DateAutoFill()
Dim StartD As Date, EndD As Date
With Worksheets("Calculator")
StartD = .Range("B55")
If IsDate(.Range("AN60")) Then
EndD = .Range("AN60")
ElseIf IsDate(.Range("AL60")) Then
EndD = .Range("AL60")
ElseIf IsDate(.Range("AJ60")) Then
EndD = .Range("AJ60")
ElseIf IsDate(.Range("AH60")) Then
EndD = .Range("AH60")
ElseIf IsDate(.Range("AF60")) Then
EndD = .Range("AF60")
ElseIf IsDate(.Range("AD60")) Then
EndD = .Range("AD60")
ElseIf IsDate(.Range("AB60")) Then
EndD = .Range("AB60")
ElseIf IsDate(.Range("Z60")) Then
EndD = .Range("Z60")
ElseIf IsDate(.Range("X60")) Then
EndD = .Range("X60")
ElseIf IsDate(.Range("V60")) Then
EndD = .Range("V60")
ElseIf IsDate(.Range("T60")) Then
EndD = .Range("T60")
ElseIf IsDate(.Range("R60")) Then
EndD = .Range("R60")
ElseIf IsDate(.Range("P60")) Then
EndD = .Range("P60")
ElseIf IsDate(.Range("N60")) Then
EndD = .Range("N60")
ElseIf IsDate(.Range("L60")) Then
EndD = .Range("L60")
ElseIf IsDate(.Range("J60")) Then
EndD = .Range("J60")
ElseIf IsDate(.Range("H60")) Then
EndD = .Range("H60")
ElseIf IsDate(.Range("FD60")) Then
EndD = .Range("F60")
ElseIf IsDate(.Range("D60")) Then
EndD = .Range("D60")
ElseIf IsDate(.Range("B60")) Then
EndD = .Range("B60")
Else
MsgBox "Enter Investment Period Section on Calculator Sheet"
End If
End With
For Row = 1 To EndD - StartD
Cells(Row, 44) = StartD Row - 1
Next Row
End Sub
Select Case
Sub DateAutoFill()
Dim StartD As Date, EndD As Date
With Worksheets("Calculator")
StartD = .Range("B55")
Select Case True
Case IsDate(.Range("AN60"))
EndD = .Range("AN60")
Case IsDate(.Range("AL60"))
EndD = .Range("AL60")
Case IsDate(.Range("AJ60"))
EndD = .Range("AJ60")
Case IsDate(.Range("AH60"))
EndD = .Range("AH60")
Case IsDate(.Range("AF60"))
EndD = .Range("AF60")
Case IsDate(.Range("AD60"))
EndD = .Range("AD60")
Case IsDate(.Range("AB60"))
EndD = .Range("AB60")
Case IsDate(.Range("Z60"))
EndD = .Range("Z60")
Case IsDate(.Range("X60"))
EndD = .Range("X60")
Case IsDate(.Range("V60"))
EndD = .Range("V60")
Case IsDate(.Range("T60"))
EndD = .Range("T60")
Case IsDate(.Range("R60"))
EndD = .Range("R60")
Case IsDate(.Range("P60"))
EndD = .Range("P60")
Case IsDate(.Range("N60"))
EndD = .Range("N60")
Case IsDate(.Range("L60"))
EndD = .Range("L60")
Case IsDate(.Range("J60"))
EndD = .Range("J60")
Case IsDate(.Range("H60"))
EndD = .Range("H60")
Case IsDate(.Range("FD60"))
EndD = .Range("F60")
Case IsDate(.Range("D60"))
EndD = .Range("D60")
Case IsDate(.Range("B60"))
EndD = .Range("B60")
Case Else
MsgBox "Enter Investment Period Section on Calculator Sheet"
End Select
End With
For Row = 1 To EndD - StartD
Cells(Row, 44) = StartD Row - 1
Next Row
End Sub