VBA search table exclude blank cells

excel-vba vba excel

58 观看


12 作者的声誉

I have created a search table in excel and I want to use a VBA code to search for specific words in the rest of the workbook. Here is an example of my table.

Fruits Store Area

I have set these values as a string, but I am having a trouble with having one item blank and then running the macro. So if i want to find only fruit and area, it wont run because store is left blank. Is there a way I can search the workbook without having each line filled in?

Here is the code I am using to copy the data over.

finalrow = Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 To finalrow
If Cells(i, 1) = Fruits And Cells(i, 2) = Store And Cells(i, 3) = Area  Then
Range(Cells(i, 1), Cells(i, 3)).Copy
ssheet.Select (Workbook sheet to paste)
Range("A100").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
作者: Tara 的来源 发布者: 2017 年 12 月 27 日

回应 (1)


103166 作者的声誉


Use IIF():

If IIF(Fruits<>"",Cells(i, 1) = Fruits,True) And _
   IIF(Store<>"",Cells(i, 2) = Store,True) And _ 
   IIF(Area<>"",Cells(i, 3) = Area,True)  Then

Also avoid the use of Select. Also when just wanting the values just assign the value and skip the clipboard:

With ActiveSheet
    finalrow = .Cells(Rows.Count, 1).End(xlUp).Row

    For i = 1 To finalrow
        If IIf(Fruits <> "", .Cells(i, 1) = Fruits, True) And _
           IIf(Store <> "", .Cells(i, 2) = Store, True) And _
           IIf(Area <> "", .Cells(i, 3) = Area, True) Then

           ssheet.Range("A100").End(xlUp).Offset(1, 0).Resize(1, 3).Value = .Range(.Cells(i, 1), .Cells(i, 3)).Value
        End If
    Next i
End With
作者: Scott Craner 发布者: 27.12.2017 05:06