Select Cells in a range without deselecting other cells based on a value

Question

I want to select cells on a range based on a value just like (ctrl + mouse click)

If the values of column G contains the word Wage and Sum it will be selected. In the picture's case, 5/15 WAGES SUMMARY and 4/15 WAGES SUMMARY will be selected.

If the count of selection is only 1, then its amount(column j) will be displayed.

If the count of selection are 2 or more, then the dates will be compared. In the picture's case, it is (5/15 and 4/15). The dates will be compared to get the highest. If there are two or more highest date, the amount will be added, if there's only one, then it will be displayed. Here's my code;

Dim ws1 As Worksheet, wsm As Worksheet
Dim wb1 As Workbook
Dim Loc As Range
Dim crt As Integer, r As Long, c As Long, last As Long

ctr = 0
i = 3

Set wb1 = ThisWorkbook
Set ws1 = wb1.Sheets("SHIPNET102")
Set wsm = wb1.Sheets("MACRO TEMPLATE")

last = ws1.Cells(Rows.Count, "A").End(xlUp).Row

Do Until ws1.Cells(i, 7) = ""

    Set Loc = ws1.Cells(i, 7).Find(What:="*WAGE*SUM*")

    If Loc Is Nothing Then

        i = i + 1

    Else

        ctr = ctr + 1
        i = i + 1

    End If

Loop

If ctr > 1 Then

    '?

Else

    r = Loc.Row
    c = Loc.Column + 3
    wsm.Cells(5, 3) = ws1.Cells(r, c)

End If

So far that's my outcome. It only counts number of cell that contains the word Wage and Sum and display the amount if the count is only 1. Any help would be appreciated.

Answer 1

I added some lines for you. Please let me know if it doesn't work.

Dim ws1 As Worksheet, wsm As Worksheet
Dim wb1 As Workbook
Dim Loc As Range
Dim crt As Integer, r As Long, c As Long, last As Long
Dim CellsToSelect As String

ctr = 0
i = 3

Set wb1 = ThisWorkbook
Set ws1 = wb1.Sheets("sheet2")
Set wsm = wb1.Sheets("Sheet1")

last = ws1.Cells(Rows.Count, "A").End(xlUp).Row

Do Until ws1.Cells(i, 7) = ""

    Set Loc = ws1.Cells(i, 7).Find(What:="*WAGE*")

    If Loc Is Nothing Then

        i = i + 1

    Else
        If CellsToSelect = Empty Then
            CellsToSelect = ws1.Cells(i, 7).Address
        Else
            CellsToSelect = CellsToSelect & "," & ws1.Cells(i, 7).Address
        End If

        ctr = ctr + 1
        i = i + 1

    End If

Loop

If ctr > 1 Then

    ws1.Range(CellsToSelect).Select

Else

    r = Loc.Row
    c = Loc.Column + 3
    wsm.Cells(5, 3) = ws1.Cells(r, c)

End If

Use the following codes to compare the dates:

Sub Program()

    Dim Str As String, str2 As String
    Dim MonthS As String, YearS As String
    Dim DateS As Date

    Str = "12/18 WAGES SUMMARY "

    str2 = Trim$(Left$(Str, InStr(Str, " ") - 1))
    MonthS = Trim$(Left$(str2, InStr(str2, "/") - 1))
    YearS = Trim$(Right$(str2, Len(str2) - InStr(str2, "/")))

    DateS = "1/" & MonthS & "/" & YearS

    If DateS > "5/8/16" Then
        a = 1
    Else
        a = 2
    End If

End Sub

Answer 2

If your column G values can be in a variety of formats ("4/15 WAGE SUMMARY", "WAGE SUMMARY 4/15", etc), then you might want to consider using regular expressions to pull the date from the text.

In VBA, regular expressions can be found in the Microsoft VBScript Regular Expressions library, which you can reference from your project by going to Tools > References in the VB editor.

How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops

http://www.regular-expressions.info/vb.html

Once you have the date part of the string extracted, you can convert to a date with the built-in CDate function, and compare two dates to get the newest.

Another issue with the general approach you are using, is that when you repeatedly use Find, you don't save state about cells that were previously examined, which will be necessary for comparing the parsed dates.

I would suggest creating a Function (let's call it GetDate(str as String)) that will take a string parameter and if the string has "WAGE" and "SUMMARY" and a parse-able date, returns the date, otherwise it returns Nothing if the input string does not match.

Insert a column at column H that is filled with the formula =GetDate(G2). Then sort the data by the date in column H descending. Now you can iterate through the rows that match your criteria and sum until no date is found, or the date gets older.