Solved VB: find the last and pre-last occurrences in the string

March 30, 2017 at 07:20:32
Specs: Windows 64
In column A, I have product ID that appears many times non-sequentially. I need a macro that finds matched ID-groups and pinpoints in those groups “ID-pre-last” and “ID-last” to insert in corresponding column user-defined-date (in column B). For “ID-pre-last” there will be one date, while for “ID-last” there will be another.

The best I could think is to pinpoint the duplicates and paste the same date-values in corresponding cells. But it doesn’t help as the dates have to differ. Could anyone write a loop to solve the issue?

Sub DateManipulation()

Dim lastRow As Long
Dim matchFoundIndex As Long
Dim iVar As Long
Dim WsM As Worksheet
Dim startDate As Variant
Dim endDate As Variant


ThisWorkbook.Sheets("test").Activate

startDate = "31-12-2016"
endDate = "31-12-2018"

Application.ScreenUpdating = False

lastRow = Range("A" & Rows.Count).End(xlUp).Row
For iVar = 3 To lastRow
    If Cells(iVar, 1) <> "" Then
    matchFoundIndex = WorksheetFunction.Match(Cells(iVar, 1), _
                                  Range("A1:A" & lastRow), 0)
    If iVar <> matchFoundIndex Then
    Cells(iVar, 2) = startDate
    Cells(iVar, 3) = endDate
    End If
    End If
Next

Application.ScreenUpdating = True


End Sub


See More: VB: find the last and pre-last occurrences in the string

Report •

✔ Best Answer
April 15, 2017 at 09:53:36
Assuming that your data starts in G1 and that there are no empty rows in your data set, this should work for you. Well, it works for the data that you posted in your example. If your actual is not laid out the same way, it probably won't work.



Sub ChangeLast2Matches()

Dim lRow As Long
Dim iVar As Long
Dim myMatches As Long

ThisWorkbook.Sheets("WsMaster").Activate
Application.ScreenUpdating = False
lRow = Range("G" & Rows.Count).End(xlUp).Row
For iVar = 1 To lRow
'Count matches for current cell
 myMatches = _
   WorksheetFunction.CountIf(Range("G1:G" & lRow), Range("G" & iVar))
'If there is a match, change the last 2 entries in Column L
    If myMatches > 1 Then
     Cells(iVar + myMatches - 1, 12) = "2018-12-31"
     Cells(iVar + myMatches - 2, 12) = "2016-12-31"
'Increase the Row variable to move to next non-matching ID
      iVar = iVar + myMatches - 1
     End If
Next
Application.ScreenUpdating = True
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03



#1
April 12, 2017 at 08:48:34
I have read your post a few times over and cannot understand what you are asking. Perhaps it is just me but i'll need some clarification.

Can you use the pre tags (search the how to section for pre tags) and provide some example data, then provide the expected out come from that data.

This should give us some idea of what you are asking.


Report •

#2
April 15, 2017 at 02:20:35
Hey again

Thank you for helping. I agree it is not simple to understand. Since I wrote my post, I could not find a proper solution so I keept my macro which highlighted the rows to be changed and I made it manually.

Sub CellesHighl()

Dim lRow As Long
Dim matchFoundIndex As Long
Dim iVar As Long
Dim WsM As Worksheet
Dim startDate As Variant
Dim endDate As Variant
Dim preDate As Variant
Dim mStartRow As Range
Dim mEndRow As Range, mPreRow As Integer

ThisWorkbook.Sheets("WsMaster").Activate
Application.ScreenUpdating = False
lRow = Range("G" & Rows.count).End(xlUp).Row
For iVar = 3 To lRow
    If Cells(iVar, 7) <> "" Then
    matchFoundIndex = WorksheetFunction.Match(Cells(iVar, 7), _
                                     Range("G1:G" & lRow), 0)
    If iVar <> matchFoundIndex Then
    Range(Cells(iVar, 11), Cells(iVar, 12)).Interior.ColorIndex = 20
    End If
    End If
Next
Application.ScreenUpdating = True

End Sub

I have data set in the area of A:N, in column G - is item-id-number which I sorted by identical ids in a corresponding groups. (I sorted the table by using a marco but can be also sorted by applying conditional formating). In columns K is a date one and in colunm L - date two.

My original data set looks like:

Row  G         K            L       

1    Y-210SC  2011-11-01  2016-10-31

2    Y-210SC  2011-11-01  2016-10-31

3    Y-210SC  2011-11-01  2016-10-31

4    Y-210SC  2011-11-01  2016-10-31

5    Y-220MU  2014-11-01  2016-10-31

6    Y-225SZ  2015-11-01  2016-10-31

7    Y-225SZ  2015-11-01  2016-10-31

8    Y-236XN  2016-01-01  2016-10-31

After proceeding with macro, the outcome might look like:

Row    G                 K            L

1     Y-210SC        2011-11-01     2016-10-31

2     Y-210SC        2011-11-01     2016-10-31

3  <b>Y-210SC </b>       2011-11-01  <b>2016-12-31</b>

4 <b> Y-210SC </b>       2011-11-01  <b>2018-12-31</b>

5     Y-220MU        2014-11-01     2016-10-31

6  <b>Y-225SZ </b>   2015-11-01  <b>2016-12-31</b>

7  <b>Y-225SZ </b>   2015-11-01  <b>2018-12-31</b>

8     Y-236XN        2016-01-01     2016-10-31


As it is seen from the above, I want my macro to proceed as follows:
if id-number is listed only once, do noting (see row 5 and 8).
if id-number is listed twice and more, find pre-last-id-number and last-id-number and make changes in kolunm L. In corresponding row for pre-last-id-number insert "2016-12-31" and in corresponding row for last-id-number insert "2018-12-31".

Hope that now it is more understandable, and someone can poin into right directions. Honestly, I tried to google the topic on different web-forums, but eventually dropped it since couldn't find a relevant solution.



Report •

#3
April 15, 2017 at 09:03:08
I understand what you are trying to do, but I'm confused about something else.

Your data starts in Row 1, but you don't start checking for matches until Row 3.

For iVar = 3 To lRow

Why is that?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
April 15, 2017 at 09:53:36
✔ Best Answer
Assuming that your data starts in G1 and that there are no empty rows in your data set, this should work for you. Well, it works for the data that you posted in your example. If your actual is not laid out the same way, it probably won't work.



Sub ChangeLast2Matches()

Dim lRow As Long
Dim iVar As Long
Dim myMatches As Long

ThisWorkbook.Sheets("WsMaster").Activate
Application.ScreenUpdating = False
lRow = Range("G" & Rows.Count).End(xlUp).Row
For iVar = 1 To lRow
'Count matches for current cell
 myMatches = _
   WorksheetFunction.CountIf(Range("G1:G" & lRow), Range("G" & iVar))
'If there is a match, change the last 2 entries in Column L
    If myMatches > 1 Then
     Cells(iVar + myMatches - 1, 12) = "2018-12-31"
     Cells(iVar + myMatches - 2, 12) = "2016-12-31"
'Increase the Row variable to move to next non-matching ID
      iVar = iVar + myMatches - 1
     End If
Next
Application.ScreenUpdating = True
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#5
April 15, 2017 at 12:38:40

Hey,

Yes, it is true - I posted the data from the row one, since I dropped the first-name-row and description-second-row from my ERP-system (when I retrieve the data in excel).That's why I had my loop from the third row.

Your macro works really well and I am happy with your help - it makes my work much easier (given that I had already tried it manually). Thank you a lot for helping.


Report •

Ask Question