ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Count function for two specific items in vba (https://www.excelbanter.com/excel-programming/318718-count-function-two-specific-items-vba.html)

Cynthia Gregory
Count function for two specific items in vba
 
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

Tom Ogilvy
Count function for two specific items in vba
 
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" wrote in message
...
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

Cynthia Gregory
Count function for two specific items in vba
 
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" wrote in message
...
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" wrote in message
...
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

Tom Ogilvy
Count function for two specific items in vba
 
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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

Cynthia Gregory
Count function for two specific items in vba
 
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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


All times are GMT +1. The time now is 08:10 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com