Macro to Add Symbol to Column Based on Two Criteria

[Michael151]

Hello all,

Trying to write a macro for Excel 2007 that will add a “~” mark to a column based on two criteria:

I have two columns: Library and Sale Date (labeled in row 1 header, not always next to each other).

For each “T” title, if the Sale Date is before 1970, then replace the “T” with a “~” symbol.

For example:


Before:
<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if !mso]>******** classid="clsid:38481807-CA0E-42D2-BF39-B33AF135CC4D" id=ieooui></object> <style> st1\:*{behavior:url(#ieooui) } </style> <![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman";} </style> <![endif]-->
<table class="MsoNormalTable" style="width: 96pt; margin-left: 5.15pt; border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0" width="128"> <tbody><tr style="height: 12.75pt;"> <td style="width: 48pt; border: 1pt solid windowtext; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="64" nowrap="nowrap"> Library
</td> <td style="width: 48pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="64" nowrap="nowrap"> Sale Date
</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 48pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="64" nowrap="nowrap"> T
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="64" nowrap="nowrap">

2/15/1995​

</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 48pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="64" nowrap="nowrap"> T
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="64" nowrap="nowrap">

1/15/1965​

</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 48pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="64" nowrap="nowrap"> G

</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="64" nowrap="nowrap">

5/10/2007​

</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 48pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="64" nowrap="nowrap"> T
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="64" nowrap="nowrap">

4/5/1960​

</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 48pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="64" nowrap="nowrap"> G

</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="64" nowrap="nowrap">

8/9/1945​

</td> </tr> </tbody></table>
After:
<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if !mso]>******** classid="clsid:38481807-CA0E-42D2-BF39-B33AF135CC4D" id=ieooui></object> <style> st1\:*{behavior:url(#ieooui) } </style> <![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman";} </style> <![endif]--> <table class="MsoNormalTable" style="width: 96pt; margin-left: 5.15pt; border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0" width="128"> <tbody><tr style="height: 12.75pt;"> <td style="width: 48pt; border: 1pt solid windowtext; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="64" nowrap="nowrap"> Library
</td> <td style="width: 48pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="64" nowrap="nowrap"> Sale Date
</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 48pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="64" nowrap="nowrap"> T
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="64" nowrap="nowrap">

2/15/1995​

</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 48pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="64" nowrap="nowrap"> ~
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="64" nowrap="nowrap">

1/15/1965​

</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 48pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="64" nowrap="nowrap"> G

</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="64" nowrap="nowrap">

5/10/2007​

</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 48pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="64" nowrap="nowrap"> ~
</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="64" nowrap="nowrap">

4/5/1960​

</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 48pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="64" nowrap="nowrap"> G

</td> <td style="width: 48pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="64" nowrap="nowrap">

8/9/1945​

</td> </tr> </tbody></table>
Help is appreciated - thank you!

 

[tweedle]

Here's a pretty quick approach...

Sub SetTildesOnSelection()
'Use: Select the cells to evaluate, then run macro
Dim Rng, C As Range
Set Rng = Selection
For Each C In Rng
    If UCase(C.Text) = "T" And C.Offset(, 1) < #1/1/1970# Then
        C = "~"
    End If
Next
End Sub

 

[Michael151]

Thanks Tweedle! one adjustment to your macro - is there any way to write it so that it searches for "Library" and "Sale Date" in the row 1 header? These columns are not always next to each other, which is why I'd like to use the header to identify rather than manually selecting each column. Thanks for your help.

 

[tweedle]

Requirements? What requirements?

Here's the revision...

Sub SetTildes()
Dim Rng, C As Range
Dim LibraryCol, SaleDateCol As Long
With ActiveSheet
'Find the columns
Rows("1:2").Select
Selection.Find(What:="Library", After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
LibraryCol = ActiveCell.Column
Selection.Find(What:="Sale Date", After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
SaleDateCol = ActiveCell.Column
'===================================================
'Find Last Row
LastRow = .Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
'Work bottom to top
For RowIdx = LastRow To 1 Step -1
    If UCase(.Cells(RowIdx, LibraryCol).Text) = "T" And _
    .Cells(RowIdx, SaleDateCol) < #1/1/1970# Then
        .Cells(RowIdx, LibraryCol) = "~"
    End If
Next RowIdx
End With
End Sub

 

[Michael151]

Thanks Tweedle! Is there any way to modify the code if the Library column is in any column? for example, if I put Library in column D and Sale Date in H, the macro does not make the replacement. Thanks for your help.

 

[tweedle]

Yes,
I missed a reference replacement:

Change:

'Find Last Row
LastRow = .Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

to:

'Find Last Row
LastRow = .Cells(Rows.Count, [B]LibraryCol[/B]).End(xlUp).Offset(1, 0).Row

 

[Michael151]

Thanks Tweedle, that worked. Appreciate the help.