S
Sharon
Hi,
I received this code from the discussion forum last week and have modified
it a bit to cover all the columns/rows that I'm working with. However, it's
not doing what I wanted it to do (although it's close!) and am stumped.
Here's an example of what my workbook looks like: (A3:AI400)
Sheet1
Columns R (blank column, being used as a spacer)
A B C ...Q S T U V...AI
date text...... date text...........
So, dates are in columns A & S and text is in columns B-Q, starting at row 3
- 400.
I want to compare the dates in column S to the dates in Column A. If a
match is found, display the result of the match (based on column S) on
Sheet2. The same format as Sheet1 is in Sheet2 with the exception that the
data will begin on Row 2 instead of Row 3. The code that I have, basically
copies all the data from S3:AI499 and pastes it on Sheet2, regardless if the
dates match. I only want it to return dates that match and the rest of the
information in the row (columns T-AI). Additionally, this macro only works
if Sheet1 is active and I want it to be able to run if Sheet2 is active.
What should I add to the code to make this happen? I thought that the With
statement should resolve that. ???
Here's the code:
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))
Set cellB = Range(Cells(3, 19), Cells(3, 19).End(xlDown))
rw = 2
For Each cellB In rngB
If Application.CountIf(rngB, cellB) > 0 Then
Sheet2.Cells(rw, 19).Value = cellB
Sheet2.Cells(rw, 19).NumberFormat = cellB.NumberFormat
Sheet2.Cells(rw, 19).Value = Cells(cellB.Row, "S").Value
Sheet2.Cells(rw, 20).Value = Cells(cellB.Row, "T").Value
Sheet2.Cells(rw, 21).Value = Cells(cellB.Row, "U").Value
Sheet2.Cells(rw, 22).Value = Cells(cellB.Row, "V").Value
Sheet2.Cells(rw, 23).Value = Cells(cellB.Row, "W").Value
Sheet2.Cells(rw, 24).Value = Cells(cellB.Row, "X").Value
Sheet2.Cells(rw, 25).Value = Cells(cellB.Row, "Y").Value
Sheet2.Cells(rw, 26).Value = Cells(cellB.Row, "Z").Value
Sheet2.Cells(rw, 27).Value = Cells(cellB.Row, "AA").Value
Sheet2.Cells(rw, 28).Value = Cells(cellB.Row, "AB").Value
Sheet2.Cells(rw, 29).Value = Cells(cellB.Row, "AC").Value
Sheet2.Cells(rw, 30).Value = Cells(cellB.Row, "AD").Value
Sheet2.Cells(rw, 31).Value = Cells(cellB.Row, "AE").Value
Sheet2.Cells(rw, 32).Value = Cells(cellB.Row, "AF").Value
Sheet2.Cells(rw, 33).Value = Cells(cellB.Row, "AG").Value
Sheet2.Cells(rw, 34).Value = Cells(cellB.Row, "AH").Value
Sheet2.Cells(rw, 35).Value = Cells(cellB.Row, "AI").Value
rw = rw + 1
End If
Next
End With
End Sub
Can I attach my document to my post? That would make things a lot easier to
explain.
Thanks for any help,
Sharon
I received this code from the discussion forum last week and have modified
it a bit to cover all the columns/rows that I'm working with. However, it's
not doing what I wanted it to do (although it's close!) and am stumped.
Here's an example of what my workbook looks like: (A3:AI400)
Sheet1
Columns R (blank column, being used as a spacer)
A B C ...Q S T U V...AI
date text...... date text...........
So, dates are in columns A & S and text is in columns B-Q, starting at row 3
- 400.
I want to compare the dates in column S to the dates in Column A. If a
match is found, display the result of the match (based on column S) on
Sheet2. The same format as Sheet1 is in Sheet2 with the exception that the
data will begin on Row 2 instead of Row 3. The code that I have, basically
copies all the data from S3:AI499 and pastes it on Sheet2, regardless if the
dates match. I only want it to return dates that match and the rest of the
information in the row (columns T-AI). Additionally, this macro only works
if Sheet1 is active and I want it to be able to run if Sheet2 is active.
What should I add to the code to make this happen? I thought that the With
statement should resolve that. ???
Here's the code:
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))
Set cellB = Range(Cells(3, 19), Cells(3, 19).End(xlDown))
rw = 2
For Each cellB In rngB
If Application.CountIf(rngB, cellB) > 0 Then
Sheet2.Cells(rw, 19).Value = cellB
Sheet2.Cells(rw, 19).NumberFormat = cellB.NumberFormat
Sheet2.Cells(rw, 19).Value = Cells(cellB.Row, "S").Value
Sheet2.Cells(rw, 20).Value = Cells(cellB.Row, "T").Value
Sheet2.Cells(rw, 21).Value = Cells(cellB.Row, "U").Value
Sheet2.Cells(rw, 22).Value = Cells(cellB.Row, "V").Value
Sheet2.Cells(rw, 23).Value = Cells(cellB.Row, "W").Value
Sheet2.Cells(rw, 24).Value = Cells(cellB.Row, "X").Value
Sheet2.Cells(rw, 25).Value = Cells(cellB.Row, "Y").Value
Sheet2.Cells(rw, 26).Value = Cells(cellB.Row, "Z").Value
Sheet2.Cells(rw, 27).Value = Cells(cellB.Row, "AA").Value
Sheet2.Cells(rw, 28).Value = Cells(cellB.Row, "AB").Value
Sheet2.Cells(rw, 29).Value = Cells(cellB.Row, "AC").Value
Sheet2.Cells(rw, 30).Value = Cells(cellB.Row, "AD").Value
Sheet2.Cells(rw, 31).Value = Cells(cellB.Row, "AE").Value
Sheet2.Cells(rw, 32).Value = Cells(cellB.Row, "AF").Value
Sheet2.Cells(rw, 33).Value = Cells(cellB.Row, "AG").Value
Sheet2.Cells(rw, 34).Value = Cells(cellB.Row, "AH").Value
Sheet2.Cells(rw, 35).Value = Cells(cellB.Row, "AI").Value
rw = rw + 1
End If
Next
End With
End Sub
Can I attach my document to my post? That would make things a lot easier to
explain.
Thanks for any help,
Sharon