Find last of multiple entries

[R2ah1ze1l]

  Do While ia < 16383                        'limited to how many columns are available to be populated
  ActiveCell.Offset(0, 1).Select            'shift cell to seach
   If ActiveCell.Value = SN Then
    ia = ia + 1                                    'counter for columns
      Do While ib < 13                         'more than 1 instance
       ActiveCell.Offset(0, 1).Select       'shift cell to seach
        If ActiveCell.Value = SN Then
         ib = ib + 1                               'counter for columns
         GoTo Jump1                             'exit do loop querry satisfied
        Else
         ib = ib + 1                               'keep count if not repeated immediately
          If ib > 10 Then
           ib = 0                                    'no other instance, return to 0
           GoTo Jump1                           'exit do loop querry satisfied
          End If
        End If
      Loop
Jump1:
    curr_set = ia + ib                           'defines column count from 'A'
    GoTo Jump2                                   'exit do loop querry satisfied
   Else
    ia = ia + 1                                      'counter for columns
   End If
  Loop
    '^ Locating SN, checks if 2 matched SN's, uses last found

Above is the code I am currently utilizing and it does work. My issue is, this is only good to find 2 instances and there are times it could go upto 7 instances, how do I assist myselft to get the whole range reviewed and determine the LAST found file?

Later in this VB code I select the whole column this 'SN' appears in (single or 2nd match) and paste to another workbook.

Thanks for any support!

 

[Rick Rothstein]

Above is the code I am currently utilizing and it does work. My issue is, this is only good to find 2 instances and there are times it could go upto 7 instances, how do I assist myselft to get the whole range reviewed and determine the LAST found file?

Your code snippet does not indicate the range you are searching in, so I'll search the entire sheet... replace Cells in my code with a reference to the actual range you want to search. Your code snippet also does not indicate what SN is, so I'll assume it is a variable to which a text string has been assigned. This single line of code will select the last cell (looking bottom up) containing the text assigned to the SN variable...

Cells.Find("SN", , xlValues, , xlRows, xlPrevious, , , False).Select

A
Later in this VB code I select the whole column this 'SN' appears in (single or 2nd match) and paste to another workbook.

If you want to select the entire column for the above located cell...

Cells.Find("SN", , xlValues, , xlRows, xlPrevious, , , False).EntireColumn.Select

 

[R2ah1ze1l]

Sorry for the missing information,
I am searching in row 3 of another excel file for 'SN' a unique name/serial number.
I need to search accross columns not down through rows.

I will work to adapt what you have recently supplied. Thanks @Rick

 

[Rick Rothstein]

Sorry for the missing information,
I am searching in row 3 of another excel file for 'SN' a unique name/serial number.
I need to search accross columns not down through rows.

For a single row (or column for that matter), nothing special has to be done as the final "find" would be the same whether the search was by rows or by columns. Here is the code to select the cell and the column...

Rows(3).Find("SN", , xlValues, , , xlPrevious, , , False).Select

Rows(3).Find("SN", , xlValues, , , xlPrevious, , , False).EntireColumn.Select

If you want to execute these in a workbook other than the one you want to search in, than you would need to prefix a reference to the workbook as well as the worksheet to be searched within that workbook.

 

[R2ah1ze1l]

For a single row (or column for that matter), nothing special has to be done as the final "find" would be the same whether the search was by rows or by columns. Here is the code to select the cell and the column...

Rows(3).Find("SN", , xlValues, , , xlPrevious, , , False).Select

Rows(3).Find("SN", , xlValues, , , xlPrevious, , , False).EntireColumn.Select

If you want to execute these in a workbook other than the one you want to search in, than you would need to prefix a reference to the workbook as well as the worksheet to be searched within that workbook.

Thank you much. I will be trying to tailor this into the routine to make for an improvement.