| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula to count month-specific items | Excel Discussion (Misc queries) | |||
| Count items when specific text and date criteria are met | Excel Worksheet Functions | |||
| Function to count unique items in list | Excel Worksheet Functions | |||
| Function to Count Number of Consecutive Rows with a Specific Criteria? | Excel Worksheet Functions | |||
| Count items between specific hours on a matching date | Excel Worksheet Functions | |||
Linear Mode
