jeudi 6 juin 2019

Check filtered data for one row of visible cells, if no data found move to next sheet and check there

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.

Aucun commentaire:

Enregistrer un commentaire