0

I'm new to VBA so there may be an obvious answer but I've been staring at this code for hours and can't figure it out.

I'm running data analysis on several hundred samples that were tested using two machines, so the serial numbers of the parts are separated into two different databases (imported into two different sheets.) I'd like to filter Machine 1 database using the serial number and if there are no results, try filtering through the Machine 2 database.

I have tried using if statements and the autofilter command to check the first sheet and if there are no visible cells, move on to the second sheet.

'Check for Visible Cells and Copy in Machine 1
If Cells.SpecialCells(xlCellTypeVisible).Rows.Count = 1 Then
     Cells.Select
     Selection.SpecialCells(xlCellTypeVisible).Select
     Selection.Copy
     Sheets("Copy Sheet").Range("$A$1").PasteSpecial Paste:=xlPasteValues

'Designate Sheet

     BoreData = "Bore Data M1"

ElseIf Cells.SpecialCells(xlCellTypeVisible).Rows.Count < 1 Then

'Check for Visible Cells and Copy in Machine 2
     Sheets("Block Data M2").Activate
     Range("$A$1:$J$1").AutoFilter Field:=2, Criteria1:=SerialLHA & "RR"
     Cells.Select
     Selection.SpecialCells(xlCellTypeVisible).Select
     Selection.Copy
     Sheets("Copy Sheet").Range("$A$1").PasteSpecial Paste:=xlPasteValues

'Designate Sheet      
      BoreData = "Bore Data M2"

End If

There are no error messages, but if there is no filtered data in Machine 1 Sheet, it will still copy the header row and never move on to Machine 2 Sheet.

I have also tried If Cells.SpecialCells(xlCellTypeVisible).Rows.Count = 1, Elseif Cells.SpecialCells(xlCellTypeVisible).Rows.Count <> 1, Then... and this results in the same situation.

rnorkett
  • 3
  • 3
  • Have you tried `Find()` or `Application.Match()` to determine if your serial number exists? If it exists, you can filter after (if you still need it). Both of those will allow you to use `.Row` to copy/paste to a destination location. – Cyril Jun 06 '19 at 19:25
  • Additonally... do your best to [avoid .`Select`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – Cyril Jun 06 '19 at 19:26
  • @Cyril - I have not tried that, I will do so. Thanks for the advice - is there a reason why Select. should be avoided? – rnorkett Jun 06 '19 at 19:28
  • Morkett, that's a link with a very elaborate explanation and description. Take a look and it will help you immensely in the future. – Cyril Jun 06 '19 at 19:43

0 Answers0