Running macro on cells that contain formulas

[Guest]

I have three columns, A, B, C. In columns A & B are dates. Data starts on
row 2, headers are in row 1. In column C I have a formula that says,
=IF(COUNTIF($A$2:$A$16,B2)>0,B2,"")
This formula returns all the dates in my range that match. The problem is
that the formula looks row by row and if a date in column B does not match a
date in column A, it leaves a blank in the corresponding row of column C.
I want to write a macro that deletes all blanks in column C, but column C is
never "truly" empty because there is the formula in it.
Can I delete my blank rows based on the blanks in column C with the formula
existing?
Can I use VB to do the work that my formula is doing? If so, how?
Furthermore, if the date shows up more than once in column B, it still
matches it to the first finding in Column A and I end up with two of the same
dates in column C. Ex: 08-oct-04 How can I avoid this?

Here's an example of my columns:
Column A | Column B | Column C |
06-oct-04 12-oct-04 12-oct-04
07-oct-04 08-oct-04 08-oct-04
08-oct-04 03-oct-04
09-oct-04 04-oct-04
10-oct-04 05-oct-04
11-oct-04 06-oct-04 06-oct-04
12-oct-04 07-oct-04 07-oct-04
13-oct-04 08-oct-04 08-oct-04

I realize that if I delete rows 3, 4 & 5 (in this example) that I will lose
the data in columns A & B. That's okay. All I really care about is column C.

I hope this is clear and not confusing.

Thanks for any help.

Sharon

 

[Tom Ogilvy]

Sub BuildC()
Dim rngB as Range, rngA as Range
Dim cellB as Range, rw as Long

set rngB = Range(Cells(2,2),Cells(2,2).End(xldown))
set rngA = Range(Cells(2,1),Cells(2,1).End(xldown))
rw = 2
for each cellB in rngB
if application.Countif(rngA,cellB)>0 then
cells(rw,3).Value = cellB
cells(rw,3).NumberFormat = cellB.Numberformat
rw = rw + 1
end if
Next

End Sub

 

[Guest]

Thanks for the code. I was hoping that I could figure out how to modify what
you've told me so far on my own, but alas, my inexperience keeps holding me
back.
This is what I was trying to figure out how to do without bothering anyone
again:

Column A | Column B | Column C | Column D | Column E | Column
F | Column G
04-oct-04 green yellow
12-oct-04 yellow red
08-oct-04 yellow red
13-oct-04 red red
12-oct-04 green red
14-oct-04 green green
13-oct-04 green yellow
15-oct-04 red red

With the code you gave me, I would get return values of:
12-oct-04
13-oct-04
which is exactly what I wanted. But now I also want to return the text in
column F & G. In my real workbook, I am using R3:999C1:17 and R3:999C19:35
(vs. A2:16 in my original example). I did figure out how to modify the code
you gave me to return the results on Sheet2, but I can only run the macro if
I Sheet1 is the active sheet.

1) What would I add to the code so that the macro would run on Sheet2?
2) How do I expand the code so that the values are returned including
Columns E-G and not just Column E?

Here's your code that I modified to display the results on Sheet2:

Sub CombineDates()
Dim rngB As Range, rngA As Range
Dim cellB As Range, rw As Long

Set rngB = Range(Cells(3, 19), Cells(3, 19).End(xlDown))
Set rngA = Range(Cells(3, 1), Cells(3, 1).End(xlDown))
rw = 2
For Each cellB In rngB
If Application.CountIf(rngA, cellB) > 0 Then
Sheet2.Cells(rw, 1).Value = cellB
Sheet2.Cells(rw, 1).NumberFormat = cellB.NumberFormat
rw = rw + 1
End If
Next

End Sub

Thanks again for any help,

Sharon

 

[Tom Ogilvy]

Sub CombineDates()
Dim rngB As Range, rngA As Range
Dim cellB As Range, rw As Long
With worksheets("Sheet1")
Set rngB = .Range(.Cells(3, 19), .Cells(3, 19).End(xlDown))
Set rngA = .Range(.Cells(3, 1), .Cells(3, 1).End(xlDown))

rw = 2
For Each cellB In rngB
If Application.CountIf(rngA, cellB) > 0 Then
Sheet2.Cells(rw, 1).Value = cellB
Sheet2.Cells(rw, 1).NumberFormat = cellB.NumberFormat
sheet2.Cells(rw,2).Value = .Cells(cellB.row,"F").Value
sheet2.Cells(rw,3).Value = .Cells(cellB.row,"G").Value
rw = rw + 1
End If
Next
End With
End Sub