Hello,
I have a few different Macros that will look for specifics in the Data and add a Note in a Column. However I am wondering if there is a way that I can have the code add a note in addition to a note that may already be in that cell?
Example: If data shows its a High Dollar Device and adds a note in stating "Review- High Dollar Device" but if the data also has a Missing Coverage on top of that High Dollar Device - it will then therefore get overwritten by Missing Coverage and will say "Review -Missing Coverage" now... Ideally I would like for it to add onto the note so end result would be "Review - High Dollar Device and Missing Coverage"
Below are the different Macros I have running that add Notes
Thank you for your help in advance!
I have a few different Macros that will look for specifics in the Data and add a Note in a Column. However I am wondering if there is a way that I can have the code add a note in addition to a note that may already be in that cell?
Example: If data shows its a High Dollar Device and adds a note in stating "Review- High Dollar Device" but if the data also has a Missing Coverage on top of that High Dollar Device - it will then therefore get overwritten by Missing Coverage and will say "Review -Missing Coverage" now... Ideally I would like for it to add onto the note so end result would be "Review - High Dollar Device and Missing Coverage"
Below are the different Macros I have running that add Notes
VBA Code:
Sub BlankWarranty()
'Looks for Additions with a Blank Warranty in WarrantyEnd Column'
Dim r1 As String
Dim r2 As String
Dim lr As Long
lr = Range("M" & Rows.Count).End(3).Row 'Transaction Type'
r1 = Range("M2:M" & lr).Address 'Transaction Type'
r2 = Range("O2:O" & lr).Address 'WarrantyEnd'
'Notes added in Site Manager Notes'
Range("U2:U" & lr).Value = Evaluate("=IF(" & r1 & "=""Addition"",IF(ISBLANK(" & r2 & "),""Review - Blank Warranty Addition"",""""),"""")")
End Sub
VBA Code:
Sub HighDollarDevice()
Dim BCol As Range
Dim ACol As Range
Set BCol = Range("1:1").Find("BDS Unit Price", , , xlWhole, , , False, , False)
Set ACol = Range("1:1").Find("Site Manager Notes", , , xlWhole, , , False, , False)
If BCol Is Nothing Or ACol Is Nothing Then
MsgBox "BDS Unit Price Column Not Found"
Exit Sub
End If
With Range(BCol.Offset(1), Cells(Rows.Count, BCol.Column).End(xlUp)).Offset(, ACol.Column - BCol.Column)
.Value = Evaluate(Replace(Replace("if(#>4999,""Review - High Dollar Device"",if(@="""","""",@))", "@", .Address), "#", .Offset(, BCol.Column - ACol.Column).Address))
End With
End Sub
VBA Code:
Sub LowDollarDevice()
Dim BDSCol As Range
Dim SMNCol As Range
Set BDSCol = Range("1:1").Find("BDS Unit Price", , , xlWhole, , , False, , False)
Set SMNCol = Range("1:1").Find("Site Manager Notes", , , xlWhole, , , False, , False)
If BDSCol Is Nothing Or SMNCol Is Nothing Then
MsgBox "Column Not Found"
Exit Sub
End If
With Range(BDSCol.Offset(1), Cells(Rows.Count, BDSCol.Column).End(xlUp)).Offset(, SMNCol.Column - BDSCol.Column)
.Value = Evaluate(Replace(Replace("if(#<-4999,""Review - Low Dollar Device"",if(@="""","""",@))", "@", .Address), "#", .Offset(, BDSCol.Column - SMNCol.Column).Address))
End With
End Sub
VBA Code:
Sub UnderContract()
Dim VCol As Range
Dim ACol As Range
Set VCol = Range("1:1").Find("VendorContract", , , xlWhole, , , False, , False)
Set ACol = Range("1:1").Find("Site Manager Notes", , , xlWhole, , , False, , False)
If VCol Is Nothing Or ACol Is Nothing Then
MsgBox "VendorContract Column Not Found"
Exit Sub
End If
With Range(VCol.Offset(1), Cells(Rows.Count, VCol.Column).End(xlUp))
.SpecialCells(xlConstants).Offset(, ACol.Column - VCol.Column).Value = "Review - Contract"
End With
End Sub
VBA Code:
Sub Proration()
Dim PCol As Range
Dim ACol As Range
Set PCol = Range("1:1").Find("Proration Date", , , xlWhole, , , False, , False)
Set ACol = Range("1:1").Find("Site Manager Notes", , , xlWhole, , , False, , False)
If PCol Is Nothing Or ACol Is Nothing Then
MsgBox "Proration Date Column Not Found"
Exit Sub
End If
With Range(PCol.Offset(1), Cells(Rows.Count, PCol.Column).End(xlUp))
.SpecialCells(xlConstants).Offset(, ACol.Column - PCol.Column).Value = "Review - Prorate?"
End With
End Sub
VBA Code:
Sub MissingCoverage()
'Looks for Missing Coverage in Coverage Column'
Dim CCol As Range
Dim SMNCol As Range
Set CCol = Range("1:1").Find("Coverage", , , xlWhole, , , False, , False)
If CCol Is Nothing Then
MsgBox "Coverage Column Not Found"
Exit Sub
End If
Set SMNCol = Range("1:1").Find("Site Manager Notes", , , xlWhole, , , False, , False)
If SMNCol Is Nothing Then
MsgBox "Site Manager Notes Column Not Found"
Exit Sub
End If
With CCol.EntireColumn
.Replace "Missing Coverage", "=true", xlWhole, , False, , False, False
On Error Resume Next
.SpecialCells(xlFormulas, xlLogical).Offset(, SMNCol.Column - .Column).Value = "Review - Missing Coverage"
On Error GoTo 0
.Replace "=true", "Missing Coverage", xlWhole, , False, , False, False
End With
End Sub
VBA Code:
Sub Reactivations()
'Looks for Missing Coverage in Coverage Column'
Dim NCol As Range
Dim SMNCol As Range
Set NCol = Range("1:1").Find("Notes", , , xlWhole, , , False, , False)
If NCol Is Nothing Then
MsgBox "Notes Column Not Found"
Exit Sub
End If
Set SMNCol = Range("1:1").Find("Site Manager Notes", , , xlWhole, , , False, , False)
If SMNCol Is Nothing Then
MsgBox "Site Manager Notes Column Not Found"
Exit Sub
End If
With NCol.EntireColumn
.Replace "Standard Reactivations", "=true", xlWhole, , False, , False, False
On Error Resume Next
.SpecialCells(xlFormulas, xlLogical).Offset(, SMNCol.Column - .Column).Value = "Reactivation"
On Error GoTo 0
.Replace "=true", "Standard Reactivations", xlWhole, , False, , False, False
End With
End Sub
Thank you for your help in advance!