I'm trying to improve my vba skills and I came out across this article from ozgrid on speeding up macros. I've looked at my own code and cleaned up some things like changing "" into vbNullString, but the two things that I don't know how to use from this article are changing If then into booleans and how not to use "select" with the code that I am running. The code in question is below:
Private Sub CmdBtn_Enter_Click() Dim CorrectRow, i, q As Long Application.Calculation = xlCalculationManual Application.ScreenUpdating = False Application.EnableEvents = False For i = 1 To 3 If Me.Controls("ComboBox" & i) <> vbNullString Then CorrectRow = WorksheetFunction.Match(CInt(DateDiff("d", "Oct 1, 2020", Me.TextBox1)), Sheets(Me.Controls("ComboBox" & i).Value).Range("A2:A213"), 0) - 1 'this is matching the date difference to a reference number on the respective worksheet With Sheets(Me.Controls("ComboBox" & i).Value) .Select For q = 0 To DateDiff("d", "Oct 1, 2020", Me.TextBox1) .Range("A2").Offset(CorrectRow + q, 3).Select Do Until ActiveCell.Value = Me.TextBox2 'Loops the cells until the cell equals your text If ActiveCell.Value = vbNullString Then 'checks blank cells ActiveCell.Value = Me.TextBox2 'inserts your text if blank ElseIf ActiveCell.Value <> vbNullString Then 'if its not blank ActiveCell.Offset(0, 1).Select 'offset the cell by one, allows loop to repeat to find a blank cell End If Loop Next q End With End If Next i Sheet1.Select Unload Me Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True Application.EnableEvents = True End Sub
Thank you for any help!