My Excel Workbook is LAGGING! I believe it is due to my code being so long/tedious. I'm pretty new at VBA, I took a course in college about 6 years ago and I haven't had to use that knowledge since recent and it's not coming back to me as quickly as I hoped.
I'm hoping that there is some kind of "looping" that can take place of all the tedious lines...
I've created a form that supervisors enter project information and then it populates the correct cells in a main project tracker.
Here is my code:
Private Sub btn_Update_Click()
Dim myDate As Date
Dim myNote As String
Dim myDept As Variant
Dim myNRC As Variant
Dim find As Range
Dim myQC As Variant
Dim mystatus As Variant
myDate = TB_Date.Value
myDept = LB_Dept.Value
myNRC = tb_NRCNum.Value
myNote = lb_Complete.Value
myQC = LB_QC.Value
mystatus = LB_status.Value
Set find = Cells.find(What:=myNRC, LookAt:=xlWhole, after:=Range("A65536"))
If find Is Nothing Then
MsgBox "NRC IS NOT VALID!"
ElseIf Not find Is Nothing Then
Cells.find(What:=myNRC, LookAt:=xlWhole, after:=Range("A65536")).Activate
If myDept = "Aerial" Then
ActiveCell.Offset(0, 4).Activate
ActiveCell.Value = myDate
Selection.NumberFormat = "m/d/yy"
ActiveCell.Offset(0, 1).Activate
ActiveCell.Value = myNote
ActiveCell.Offset(0, 10).Activate
ActiveCell.Value = myQC
ActiveCell.Offset(0, 2).Activate
ActiveCell.Value = mystatus
ElseIf myDept = "Underground" Then
ActiveCell.Offset(0, 6).Activate
ActiveCell.Value = myDate
Selection.NumberFormat = "m/d/yy"
ActiveCell.Offset(0, 1).Activate
ActiveCell.Value = myNote
ActiveCell.Offset(0, 8).Activate
ActiveCell.Value = myQC
ActiveCell.Offset(0, 2).Activate
ActiveCell.Value = mystatus
ElseIf myDept = "Coax" Then
ActiveCell.Offset(0, 8).Activate
ActiveCell.Value = myDate
Selection.NumberFormat = "m/d/yy"
ActiveCell.Offset(0, 1).Activate
ActiveCell.Value = myNote
ActiveCell.Offset(0, 6).Activate
ActiveCell.Value = myQC
ActiveCell.Offset(0, 2).Activate
ActiveCell.Value = mystatus
ElseIf myDept = "MDU" Then
ActiveCell.Offset(0, 10).Activate
ActiveCell.Value = myDate
Selection.NumberFormat = "m/d/yy"
ActiveCell.Offset(0, 1).Activate
ActiveCell.Value = myNote
ActiveCell.Offset(0, 4).Activate
ActiveCell.Value = myQC
ActiveCell.Offset(0, 2).Activate
ActiveCell.Value = mystatus
ElseIf myDept = "Fiber" Then
ActiveCell.Offset(0, 12).Activate
ActiveCell.Value = myDate
Selection.NumberFormat = "m/d/yy"
ActiveCell.Offset(0, 1).Activate
ActiveCell.Value = myNote
ActiveCell.Offset(0, 2).Activate
ActiveCell.Value = myQC
ActiveCell.Offset(0, 2).Activate
ActiveCell.Value = mystatus
End If
End If
End Sub
I'm hoping that there is some kind of "looping" that can take place of all the tedious lines...
I've created a form that supervisors enter project information and then it populates the correct cells in a main project tracker.
Here is my code:
Private Sub btn_Update_Click()
Dim myDate As Date
Dim myNote As String
Dim myDept As Variant
Dim myNRC As Variant
Dim find As Range
Dim myQC As Variant
Dim mystatus As Variant
myDate = TB_Date.Value
myDept = LB_Dept.Value
myNRC = tb_NRCNum.Value
myNote = lb_Complete.Value
myQC = LB_QC.Value
mystatus = LB_status.Value
Set find = Cells.find(What:=myNRC, LookAt:=xlWhole, after:=Range("A65536"))
If find Is Nothing Then
MsgBox "NRC IS NOT VALID!"
ElseIf Not find Is Nothing Then
Cells.find(What:=myNRC, LookAt:=xlWhole, after:=Range("A65536")).Activate
If myDept = "Aerial" Then
ActiveCell.Offset(0, 4).Activate
ActiveCell.Value = myDate
Selection.NumberFormat = "m/d/yy"
ActiveCell.Offset(0, 1).Activate
ActiveCell.Value = myNote
ActiveCell.Offset(0, 10).Activate
ActiveCell.Value = myQC
ActiveCell.Offset(0, 2).Activate
ActiveCell.Value = mystatus
ElseIf myDept = "Underground" Then
ActiveCell.Offset(0, 6).Activate
ActiveCell.Value = myDate
Selection.NumberFormat = "m/d/yy"
ActiveCell.Offset(0, 1).Activate
ActiveCell.Value = myNote
ActiveCell.Offset(0, 8).Activate
ActiveCell.Value = myQC
ActiveCell.Offset(0, 2).Activate
ActiveCell.Value = mystatus
ElseIf myDept = "Coax" Then
ActiveCell.Offset(0, 8).Activate
ActiveCell.Value = myDate
Selection.NumberFormat = "m/d/yy"
ActiveCell.Offset(0, 1).Activate
ActiveCell.Value = myNote
ActiveCell.Offset(0, 6).Activate
ActiveCell.Value = myQC
ActiveCell.Offset(0, 2).Activate
ActiveCell.Value = mystatus
ElseIf myDept = "MDU" Then
ActiveCell.Offset(0, 10).Activate
ActiveCell.Value = myDate
Selection.NumberFormat = "m/d/yy"
ActiveCell.Offset(0, 1).Activate
ActiveCell.Value = myNote
ActiveCell.Offset(0, 4).Activate
ActiveCell.Value = myQC
ActiveCell.Offset(0, 2).Activate
ActiveCell.Value = mystatus
ElseIf myDept = "Fiber" Then
ActiveCell.Offset(0, 12).Activate
ActiveCell.Value = myDate
Selection.NumberFormat = "m/d/yy"
ActiveCell.Offset(0, 1).Activate
ActiveCell.Value = myNote
ActiveCell.Offset(0, 2).Activate
ActiveCell.Value = myQC
ActiveCell.Offset(0, 2).Activate
ActiveCell.Value = mystatus
End If
End If
End Sub