+ Reply to Thread
Results 1 to 2 of 2

vba COUNTIF Problem

Hybrid View

  1. #1
    Registered User
    Join Date
    07-17-2012
    Location
    Malaysia
    MS-Off Ver
    Excel 2007
    Posts
    48

    vba COUNTIF Problem

    i'm using excel 2003 and i know excel 2007 can use countifs on the multiple condition.
    but i only want to use countif on my vba.

    Could anyone see what happen?
    My logic is sheet1(no) = sheet2(no) & sheet1(amount) <> sheet2(amount) then copy sheet2 info into sheet 3

    Sub Test()
    
      With Worksheets("sheet3")
            
            Worksheets("sheet2").Activate
            .Range("A1:L1").Value = Range("A1:C1").Value
            lastrow = Worksheets("sheet2").Cells(Rows.Count, 1).End(xlUp).Row
            For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
                Application.StatusBar = "Application Process 1 of 3 Row: " & i
                If WorksheetFunction.CountIf(Worksheets("sheet1").Range("A1:A" & lastrow), Cells(i, 1)) > 0 And _
                WorksheetFunction.CountIf(Worksheets("sheet1").Range("A1:A" & lastrow), Cells(i, 1) = 0 And _
                Worksheets("sheet1").Range("C1:C" & lastrow), Cells(i, 2)) = 0 Then
                   Cells(i, 1).Resize(1, 3).Copy Destination:=.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
                End If
            Next i
    
    End Sub
    Attached Files Attached Files

  2. #2
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,138

    Re: vba COUNTIF Problem

    Looks like the last WSFunct is missing.

    Sub Test()
    
      With Worksheets("sheet3")
            
            Worksheets("sheet2").Activate
            .Range("A1:L1").Value = Range("A1:C1").Value
            lastrow = Worksheets("sheet2").Cells(Rows.Count, 1).End(xlUp).Row
            For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
                Application.StatusBar = "Application Process 1 of 3 Row: " & i
                If WorksheetFunction.CountIf(Worksheets("sheet1").Range("A1:A" & lastrow), Cells(i, 1)) > 0 And _
                WorksheetFunction.CountIf(Worksheets("sheet1").Range("A1:A" & lastrow), Cells(i, 1)) = 0 And _
                WorksheetFunction.CountIf(Worksheets("sheet1").Range("C1:C" & lastrow), Cells(i, 2)) = 0 Then
                   Cells(i, 1).Resize(1, 3).Copy Destination:=.Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
                End If
            Next i
    End With
    End Sub
    David
    (*) Reputation points appreciated.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1