Count function for two specific items in vba

C

Cynthia Gregory

I have a macro that compares two columns of data and puts either "OK" or
"Name in Col B is not in Col A" in the third column. Then it compares Col A
with Col B and does the same thing by placing the "OK" or "Name in Col A is
not in Col B" in the fourth column. Column A and Column B will have
different rows of data also. My data will be different on a daily basis so
I do not know where the last row of data will be. After the first macro has
run, I would like to place a count function on the last cell in column C and
also in column D to count the "OK"'s and also the "Names....". Can someone
please help me with this.

Here is what I have so far.

Sub compare()
Range("a1").Select
While Not IsEmpty(ActiveCell)
If WorksheetFunction.CountIf(Range("b:b"), ActiveCell) > 0 Then
ActiveCell.Offset(0, 3) = "OK"
Else
ActiveCell.Offset(0, 3) = "Name in Col A is not in Col B"
End If
ActiveCell.Offset(1, 0).Select
Wend
Range("b1").Select
While Not IsEmpty(ActiveCell)
If WorksheetFunction.CountIf(Range("a:a"), ActiveCell) > 0 Then
ActiveCell.Offset(0, 1) = "OK"
Else
ActiveCell.Offset(0, 1) = "Name in Col B is not in Col A"
End If
ActiveCell.Offset(1, 0).Select
Wend
End Sub

--
Thanks,
Cynthia G.

"Information is just signs and numbers, while knowledge
involves their meaning. What we want is knowledge, but what
we get is information."
--Heinz R. Pagels
 
T

Tom Ogilvy

Sub compare()
Range("a1").Select
While Not IsEmpty(ActiveCell)
If WorksheetFunction.CountIf(Range("b:b"), ActiveCell) > 0 Then
ActiveCell.Offset(0, 3) = "OK"
Else
ActiveCell.Offset(0, 3) = "Name in Col A is not in Col B"
End If
ActiveCell.Offset(1, 0).Select
Wend
set maxCell = ActiveCell
Range("b1").Select
While Not IsEmpty(ActiveCell)
If WorksheetFunction.CountIf(Range("a:a"), ActiveCell) > 0 Then
ActiveCell.Offset(0, 1) = "OK"
Else
ActiveCell.Offset(0, 1) = "Name in Col B is not in Col A"
End If
ActiveCell.Offset(1, 0).Select
Wend
if maxCell.row < ActiveCell.row then
lastRow = ActiveCell.row + 1
else
lastRow = MaxCell.row + 1
End if
Cells(lastRow,3).FormulaR1C1 = "=Countif(R1C:R[-1]C,""OK"")"
Cells(lastRow+1,3).FormulaR1C1 = _
"=Countif(R1C:R[-2]C,""Name in Col A is not in Col B"")"
Cells(lastRow,4).FormulaR1C1 = "=Countif(R1C:R[-1]C,""OK"")"
Cells(lastRow+1,4).FormulaR1C1 = _
"=Countif(R1C:R[-2]C,""Name in Col B is not in Col A"")"
End Sub
 
C

Cynthia Gregory

Tom....thank you. This is wonderful!!! You have helped me out numerous
times and am so appreciative of your willingness to share your knowledge.
Have a wonderful blessed holiday season!!

--
Thanks,
Cynthia G.

"Information is just signs and numbers, while knowledge
involves their meaning. What we want is knowledge, but what
we get is information."
--Heinz R. Pagels

Tom Ogilvy said:
Sub compare()
Range("a1").Select
While Not IsEmpty(ActiveCell)
If WorksheetFunction.CountIf(Range("b:b"), ActiveCell) > 0 Then
ActiveCell.Offset(0, 3) = "OK"
Else
ActiveCell.Offset(0, 3) = "Name in Col A is not in Col B"
End If
ActiveCell.Offset(1, 0).Select
Wend
set maxCell = ActiveCell
Range("b1").Select
While Not IsEmpty(ActiveCell)
If WorksheetFunction.CountIf(Range("a:a"), ActiveCell) > 0 Then
ActiveCell.Offset(0, 1) = "OK"
Else
ActiveCell.Offset(0, 1) = "Name in Col B is not in Col A"
End If
ActiveCell.Offset(1, 0).Select
Wend
if maxCell.row < ActiveCell.row then
lastRow = ActiveCell.row + 1
else
lastRow = MaxCell.row + 1
End if
Cells(lastRow,3).FormulaR1C1 = "=Countif(R1C:R[-1]C,""OK"")"
Cells(lastRow+1,3).FormulaR1C1 = _
"=Countif(R1C:R[-2]C,""Name in Col A is not in Col B"")"
Cells(lastRow,4).FormulaR1C1 = "=Countif(R1C:R[-1]C,""OK"")"
Cells(lastRow+1,4).FormulaR1C1 = _
"=Countif(R1C:R[-2]C,""Name in Col B is not in Col A"")"
End Sub

--
Regards,
Tom Ogilvy

Cynthia Gregory said:
I have a macro that compares two columns of data and puts either "OK" or
"Name in Col B is not in Col A" in the third column. Then it compares Col A
with Col B and does the same thing by placing the "OK" or "Name in Col A is
not in Col B" in the fourth column. Column A and Column B will have
different rows of data also. My data will be different on a daily basis so
I do not know where the last row of data will be. After the first macro has
run, I would like to place a count function on the last cell in column C and
also in column D to count the "OK"'s and also the "Names....". Can someone
please help me with this.

Here is what I have so far.

Sub compare()
Range("a1").Select
While Not IsEmpty(ActiveCell)
If WorksheetFunction.CountIf(Range("b:b"), ActiveCell) > 0 Then
ActiveCell.Offset(0, 3) = "OK"
Else
ActiveCell.Offset(0, 3) = "Name in Col A is not in Col B"
End If
ActiveCell.Offset(1, 0).Select
Wend
Range("b1").Select
While Not IsEmpty(ActiveCell)
If WorksheetFunction.CountIf(Range("a:a"), ActiveCell) > 0 Then
ActiveCell.Offset(0, 1) = "OK"
Else
ActiveCell.Offset(0, 1) = "Name in Col B is not in Col A"
End If
ActiveCell.Offset(1, 0).Select
Wend
End Sub

--
Thanks,
Cynthia G.

"Information is just signs and numbers, while knowledge
involves their meaning. What we want is knowledge, but what
we get is information."
--Heinz R. Pagels
Click to expand...
Click to expand...
 
T

Tom Ogilvy

I had the "Not In Column ??" in the wrong order in my formulas. Here is a
corrected version:

Sub compare()
Range("a1").Select
While Not IsEmpty(ActiveCell)
If WorksheetFunction.CountIf(Range("b:b"), ActiveCell) > 0 Then
ActiveCell.Offset(0, 3) = "OK"
Else
ActiveCell.Offset(0, 3) = "Name in Col A is not in Col B"
End If
ActiveCell.Offset(1, 0).Select
Wend
Set MaxCell = ActiveCell
Range("b1").Select
While Not IsEmpty(ActiveCell)
If WorksheetFunction.CountIf(Range("a:a"), ActiveCell) > 0 Then
ActiveCell.Offset(0, 1) = "OK"
Else
ActiveCell.Offset(0, 1) = "Name in Col B is not in Col A"
End If
ActiveCell.Offset(1, 0).Select
Wend
If MaxCell.Row < ActiveCell.Row Then
lastRow = ActiveCell.Row + 1
Else
lastRow = MaxCell.Row + 1
End If
Cells(lastRow, 3).FormulaR1C1 = "=Countif(R1C:R[-1]C,""OK"")"
Cells(lastRow + 1, 3).FormulaR1C1 = _
"=Countif(R1C:R[-2]C,""Name in Col B is not in Col A"")"
Cells(lastRow, 4).FormulaR1C1 = "=Countif(R1C:R[-1]C,""OK"")"
Cells(lastRow + 1, 4).FormulaR1C1 = _
"=Countif(R1C:R[-2]C,""Name in Col A is not in Col B"")"
End Sub



Happy Holidays to you as well.

--
Regards,
Tom Ogilvy

Cynthia Gregory said:
Tom....thank you. This is wonderful!!! You have helped me out numerous
times and am so appreciative of your willingness to share your knowledge.
Have a wonderful blessed holiday season!!

--
Thanks,
Cynthia G.

"Information is just signs and numbers, while knowledge
involves their meaning. What we want is knowledge, but what
we get is information."
--Heinz R. Pagels

Tom Ogilvy said:
Sub compare()
Range("a1").Select
While Not IsEmpty(ActiveCell)
If WorksheetFunction.CountIf(Range("b:b"), ActiveCell) > 0 Then
ActiveCell.Offset(0, 3) = "OK"
Else
ActiveCell.Offset(0, 3) = "Name in Col A is not in Col B"
End If
ActiveCell.Offset(1, 0).Select
Wend
set maxCell = ActiveCell
Range("b1").Select
While Not IsEmpty(ActiveCell)
If WorksheetFunction.CountIf(Range("a:a"), ActiveCell) > 0 Then
ActiveCell.Offset(0, 1) = "OK"
Else
ActiveCell.Offset(0, 1) = "Name in Col B is not in Col A"
End If
ActiveCell.Offset(1, 0).Select
Wend
if maxCell.row < ActiveCell.row then
lastRow = ActiveCell.row + 1
else
lastRow = MaxCell.row + 1
End if
Cells(lastRow,3).FormulaR1C1 = "=Countif(R1C:R[-1]C,""OK"")"
Cells(lastRow+1,3).FormulaR1C1 = _
"=Countif(R1C:R[-2]C,""Name in Col A is not in Col B"")"
Cells(lastRow,4).FormulaR1C1 = "=Countif(R1C:R[-1]C,""OK"")"
Cells(lastRow+1,4).FormulaR1C1 = _
"=Countif(R1C:R[-2]C,""Name in Col B is not in Col A"")"
End Sub

--
Regards,
Tom Ogilvy

Cynthia Gregory said:
I have a macro that compares two columns of data and puts either "OK" or
"Name in Col B is not in Col A" in the third column. Then it compares
Click to expand...
Col
A
with Col B and does the same thing by placing the "OK" or "Name in Col
Click to expand...
A
is
not in Col B" in the fourth column. Column A and Column B will have
different rows of data also. My data will be different on a daily
Click to expand...
basis
so
I do not know where the last row of data will be. After the first
Click to expand...
macro
has
run, I would like to place a count function on the last cell in column
Click to expand...
C
and
also in column D to count the "OK"'s and also the "Names....". Can someone
please help me with this.

Here is what I have so far.

Sub compare()
Range("a1").Select
While Not IsEmpty(ActiveCell)
If WorksheetFunction.CountIf(Range("b:b"), ActiveCell) > 0 Then
ActiveCell.Offset(0, 3) = "OK"
Else
ActiveCell.Offset(0, 3) = "Name in Col A is not in Col B"
End If
ActiveCell.Offset(1, 0).Select
Wend
Range("b1").Select
While Not IsEmpty(ActiveCell)
If WorksheetFunction.CountIf(Range("a:a"), ActiveCell) > 0 Then
ActiveCell.Offset(0, 1) = "OK"
Else
ActiveCell.Offset(0, 1) = "Name in Col B is not in Col A"
End If
ActiveCell.Offset(1, 0).Select
Wend
End Sub

--
Thanks,
Cynthia G.

"Information is just signs and numbers, while knowledge
involves their meaning. What we want is knowledge, but what
we get is information."
--Heinz R. Pagels
Click to expand...
Click to expand...
Click to expand...
 
C

Cynthia Gregory

Yea....I caught that. I fixed it and did not want to mention it to you.
Thanks again. You are great!!

--
Thanks,
Cynthia G.

"Information is just signs and numbers, while knowledge
involves their meaning. What we want is knowledge, but what
we get is information."
--Heinz R. Pagels

Tom Ogilvy said:
I had the "Not In Column ??" in the wrong order in my formulas. Here is a
corrected version:

Sub compare()
Range("a1").Select
While Not IsEmpty(ActiveCell)
If WorksheetFunction.CountIf(Range("b:b"), ActiveCell) > 0 Then
ActiveCell.Offset(0, 3) = "OK"
Else
ActiveCell.Offset(0, 3) = "Name in Col A is not in Col B"
End If
ActiveCell.Offset(1, 0).Select
Wend
Set MaxCell = ActiveCell
Range("b1").Select
While Not IsEmpty(ActiveCell)
If WorksheetFunction.CountIf(Range("a:a"), ActiveCell) > 0 Then
ActiveCell.Offset(0, 1) = "OK"
Else
ActiveCell.Offset(0, 1) = "Name in Col B is not in Col A"
End If
ActiveCell.Offset(1, 0).Select
Wend
If MaxCell.Row < ActiveCell.Row Then
lastRow = ActiveCell.Row + 1
Else
lastRow = MaxCell.Row + 1
End If
Cells(lastRow, 3).FormulaR1C1 = "=Countif(R1C:R[-1]C,""OK"")"
Cells(lastRow + 1, 3).FormulaR1C1 = _
"=Countif(R1C:R[-2]C,""Name in Col B is not in Col A"")"
Cells(lastRow, 4).FormulaR1C1 = "=Countif(R1C:R[-1]C,""OK"")"
Cells(lastRow + 1, 4).FormulaR1C1 = _
"=Countif(R1C:R[-2]C,""Name in Col A is not in Col B"")"
End Sub



Happy Holidays to you as well.

--
Regards,
Tom Ogilvy

Cynthia Gregory said:
Tom....thank you. This is wonderful!!! You have helped me out numerous
times and am so appreciative of your willingness to share your knowledge.
Have a wonderful blessed holiday season!!

--
Thanks,
Cynthia G.

"Information is just signs and numbers, while knowledge
involves their meaning. What we want is knowledge, but what
we get is information."
--Heinz R. Pagels

Tom Ogilvy said:
Sub compare()
Range("a1").Select
While Not IsEmpty(ActiveCell)
If WorksheetFunction.CountIf(Range("b:b"), ActiveCell) > 0 Then
ActiveCell.Offset(0, 3) = "OK"
Else
ActiveCell.Offset(0, 3) = "Name in Col A is not in Col B"
End If
ActiveCell.Offset(1, 0).Select
Wend
set maxCell = ActiveCell
Range("b1").Select
While Not IsEmpty(ActiveCell)
If WorksheetFunction.CountIf(Range("a:a"), ActiveCell) > 0 Then
ActiveCell.Offset(0, 1) = "OK"
Else
ActiveCell.Offset(0, 1) = "Name in Col B is not in Col A"
End If
ActiveCell.Offset(1, 0).Select
Wend
if maxCell.row < ActiveCell.row then
lastRow = ActiveCell.row + 1
else
lastRow = MaxCell.row + 1
End if
Cells(lastRow,3).FormulaR1C1 = "=Countif(R1C:R[-1]C,""OK"")"
Cells(lastRow+1,3).FormulaR1C1 = _
"=Countif(R1C:R[-2]C,""Name in Col A is not in Col B"")"
Cells(lastRow,4).FormulaR1C1 = "=Countif(R1C:R[-1]C,""OK"")"
Cells(lastRow+1,4).FormulaR1C1 = _
"=Countif(R1C:R[-2]C,""Name in Col B is not in Col A"")"
End Sub

--
Regards,
Tom Ogilvy

I have a macro that compares two columns of data and puts either "OK" or
"Name in Col B is not in Col A" in the third column. Then it compares Col
A
with Col B and does the same thing by placing the "OK" or "Name in Col A
is
not in Col B" in the fourth column. Column A and Column B will have
different rows of data also. My data will be different on a daily basis
so
I do not know where the last row of data will be. After the first macro
has
run, I would like to place a count function on the last cell in column C
and
also in column D to count the "OK"'s and also the "Names....". Can
someone
please help me with this.

Here is what I have so far.

Sub compare()
Range("a1").Select
While Not IsEmpty(ActiveCell)
If WorksheetFunction.CountIf(Range("b:b"), ActiveCell) > 0 Then
ActiveCell.Offset(0, 3) = "OK"
Else
ActiveCell.Offset(0, 3) = "Name in Col A is not in Col B"
End If
ActiveCell.Offset(1, 0).Select
Wend
Range("b1").Select
While Not IsEmpty(ActiveCell)
If WorksheetFunction.CountIf(Range("a:a"), ActiveCell) > 0 Then
ActiveCell.Offset(0, 1) = "OK"
Else
ActiveCell.Offset(0, 1) = "Name in Col B is not in Col A"
End If
ActiveCell.Offset(1, 0).Select
Wend
End Sub

--
Thanks,
Cynthia G.

"Information is just signs and numbers, while knowledge
involves their meaning. What we want is knowledge, but what
we get is information."
--Heinz R. Pagels
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with formula in vb coding 5
Can Vba workout if a selection is part of a group? 0
VLOOKUP Insert & Copy 24
Copying data from Sheet1 which is on multiple rows for each customerto Sheet2 on single row. 2
range addressing var issue 4
Perplexing IF statement 4
active cell control using scripts 1
Named Range maker code 9

Top