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
--
Regards,
Tom Ogilvy
"Sharon" wrote in message
...
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" wrote:
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
--
Regards,
Tom Ogilvy
"Sharon" wrote in message
...
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