'Blank Warranty Addition'
Dim TTCol As Range
Dim WECol As Range
Dim SMNCol As Range
Dim rng As Range
Dim cel As Range
Dim LR As Long
Dim note As String
note = "Review - Blank Warranty Addition"
Application.ScreenUpdating = False
With Sheets("Export Detail")
'TheSheet.Range.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)'
Set TTCol = .Range("1:1").Find("Transaction Type", , xlValues, xlWhole)
Set WECol = .Range("1:1").Find("WarrantyEnd", , xlValues, xlWhole)
Set SMNCol = .Range("1:1").Find("Site Manager Notes", , xlValues, xlWhole)
LR = .Cells(.Rows.Count, TTCol.Column).End(xlUp).Row
Set rng = .Range(.Cells(2, TTCol.Column), .Cells(LR, TTCol.Column))
For Each cel In rng
If cel.Value = "Addition" Then
If .Cells(cel.Row, WECol.Column) = "" Then
'USE ONLY ONE OF THE FOLLOWING'
'Overwrite Existing Note'
'.Cells(cel.Row, SiteMan.Column).Value = note'
'Insert Note Before Existing Note'
'.Cells(cel.Row, SiteMan.Column).Value = note & " " & .Cells(cel.Row, SiteMan.Column).Value'
'Insert Note After Existing Note'
.Cells(cel.Row, SMNCol.Column).Value = .Cells(cel.Row, SMNCol.Column).Value & " " & note
End If
End If
Next cel
End With
Application.ScreenUpdating = True
'Reactivations'
Dim NCol As Range
On Error Resume Next
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
'Proration'
Dim PCol As Range
Dim ACol As Range
On Error Resume Next
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?"
If PCol Is Nothing Then Exit Sub
End With
'High Dollar Value'
Dim BCol As Range
On Error Resume Next
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
'Low Dollar Value'
Dim BDSCol As Range
On Error Resume Next
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
'Missing Coverage'
Dim CCol As Range
On Error Resume Next
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
'Under Contract'
Dim VCol As Range
On Error Resume Next
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