+ Reply to Thread
Results 1 to 9 of 9

Matching cells & returning values

  1. #1
    Sharon
    Guest

    Matching cells & returning values

    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

  2. #2
    gocush
    Guest

    RE: Matching cells & returning values

    Try this:

    Sub CombineDates()
    Dim rngB As Range, rngA As Range
    Dim cellB As Range
    dim i as Integer
    i=1

    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))
    End With

    For i = 1 to Application.CountA(rngB)
    If rngB(i) = rngA(i) Then
    Range(rngB(i),rng(B).Offset(0,18)).Copy _
    Destination:=Sheets("Sheet2").Range("A65536").End(xlUp)(2)
    End if
    Next i

    End Sub



    "Sharon" wrote:

    > 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


  3. #3
    Sharon
    Guest

    RE: Matching cells & returning values

    I copied and pasted it in, but I get an error at the line:

    Range(rngB(i),rng(B).Offset(0,18)).Copy _
    Destination:=Sheets("Sheet2").Range("A65536").End(xlUp)(2)

    I thought maybe it was the "(" before the B in the (i),rng(B) part of the
    code, but that didn't seem to make the error go away.

    Any ideas?

    Thanks,

    Sharon

    "gocush" wrote:

    > Try this:
    >
    > Sub CombineDates()
    > Dim rngB As Range, rngA As Range
    > Dim cellB As Range
    > dim i as Integer
    > i=1
    >
    > 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))
    > End With
    >
    > For i = 1 to Application.CountA(rngB)
    > If rngB(i) = rngA(i) Then
    > Range(rngB(i),rng(B).Offset(0,18)).Copy _
    > Destination:=Sheets("Sheet2").Range("A65536").End(xlUp)(2)
    > End if
    > Next i
    >
    > End Sub
    >
    >
    >
    > "Sharon" wrote:
    >
    > > 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


  4. #4
    Sharon
    Guest

    RE: Matching cells & returning values

    Hi again,

    I figured out how to make the error go away, but now the macro doesn't do
    anything at all.

    Here's what I changed:

    Range(rngB(i), rngB).Offset(0, 18).Copy _

    Here's what it was:

    Range(rngB(i), rng(B).Offset(0, 18)).Copy _

    Any ideas? I am really, really hoping this will work, otherwise, I have to
    quit this project.

    "gocush" wrote:

    > Try this:
    >
    > Sub CombineDates()
    > Dim rngB As Range, rngA As Range
    > Dim cellB As Range
    > dim i as Integer
    > i=1
    >
    > 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))
    > End With
    >
    > For i = 1 to Application.CountA(rngB)
    > If rngB(i) = rngA(i) Then
    > Range(rngB(i),rng(B).Offset(0,18)).Copy _
    > Destination:=Sheets("Sheet2").Range("A65536").End(xlUp)(2)
    > End if
    > Next i
    >
    > End Sub
    >
    >
    >
    > "Sharon" wrote:
    >
    > > 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


  5. #5
    gocush
    Guest

    RE: Matching cells & returning values

    sorry. Try this:

    Range(rngB(i),rngB.Offset(0,18)).Copy _
    Destination:=Sheets("Sheet2").Range("A65536").End(xlUp).Offset(1,0)


    "gocush" wrote:

    > Try this:
    >
    > Sub CombineDates()
    > Dim rngB As Range, rngA As Range
    > Dim cellB As Range
    > dim i as Integer
    > i=1
    >
    > 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))
    > End With
    >
    > For i = 1 to Application.CountA(rngB)
    > If rngB(i) = rngA(i) Then
    > Range(rngB(i),rng(B).Offset(0,18)).Copy _
    > Destination:=Sheets("Sheet2").Range("A65536").End(xlUp)(2)
    > End if
    > Next i
    >
    > End Sub
    >
    >
    >
    > "Sharon" wrote:
    >
    > > 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


  6. #6
    gocush
    Guest

    RE: Matching cells & returning values

    Let's see if I can get this right this time:

    Range(rngB(i),rngB(i).Offset(0,18)).Copy _
    Destination:=Sheets("Sheet2").Range("A65536").End(xlUp).offset(1,0)


    "Sharon" wrote:

    > Hi again,
    >
    > I figured out how to make the error go away, but now the macro doesn't do
    > anything at all.
    >
    > Here's what I changed:
    >
    > Range(rngB(i), rngB).Offset(0, 18).Copy _
    >
    > Here's what it was:
    >
    > Range(rngB(i), rng(B).Offset(0, 18)).Copy _
    >
    > Any ideas? I am really, really hoping this will work, otherwise, I have to
    > quit this project.
    >
    > "gocush" wrote:
    >
    > > Try this:
    > >
    > > Sub CombineDates()
    > > Dim rngB As Range, rngA As Range
    > > Dim cellB As Range
    > > dim i as Integer
    > > i=1
    > >
    > > 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))
    > > End With
    > >
    > > For i = 1 to Application.CountA(rngB)
    > > If rngB(i) = rngA(i) Then
    > > Range(rngB(i),rng(B).Offset(0,18)).Copy _
    > > Destination:=Sheets("Sheet2").Range("A65536").End(xlUp)(2)
    > > End if
    > > Next i
    > >
    > > End Sub
    > >
    > >
    > >
    > > "Sharon" wrote:
    > >
    > > > 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


  7. #7
    Toppers
    Guest

    RE: Matching cells & returning values

    I think it should be:

    Range(rngB(i),rngB(i).Offset(0,18)).Copy _
    Destination:=Sheets("Sheet2").Range("A65536").End(xlUp).Offset(1,0)

    (works for me)

    "gocush" wrote:

    > sorry. Try this:
    >
    > Range(rngB(i),rngB.Offset(0,18)).Copy _
    > Destination:=Sheets("Sheet2").Range("A65536").End(xlUp).Offset(1,0)
    >
    >
    > "gocush" wrote:
    >
    > > Try this:
    > >
    > > Sub CombineDates()
    > > Dim rngB As Range, rngA As Range
    > > Dim cellB As Range
    > > dim i as Integer
    > > i=1
    > >
    > > 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))
    > > End With
    > >
    > > For i = 1 to Application.CountA(rngB)
    > > If rngB(i) = rngA(i) Then
    > > Range(rngB(i),rng(B).Offset(0,18)).Copy _
    > > Destination:=Sheets("Sheet2").Range("A65536").End(xlUp)(2)
    > > End if
    > > Next i
    > >
    > > End Sub
    > >
    > >
    > >
    > > "Sharon" wrote:
    > >
    > > > 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


  8. #8
    Tom Ogilvy
    Guest

    Re: Matching cells & returning values

    This code copies when the date in S matches the Date in A, but only in the
    same row.

    If that is what you want, then it should work with no problem (with the
    correction).

    An alternate interpretation is to copy the row if the date in S matches any
    cell in column A.

    In which case it would be;

    Sub CombineDates()
    Dim rngB As Range, rngA As Range
    Dim cellB As Range
    dim i as Integer
    i=1

    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))
    End With

    For i = 1 to Application.CountA(rngB)
    If countif(rngA,rngB(i)) > 0 Then
    rngB(i).Resize(1,17).Copy _
    Destination:=Sheets("Sheet2").Range("A65536").End(xlUp)(2)
    End if
    Next i

    End Sub

    --
    Regards,
    Tom Ogilvy

    "gocush" <[email protected]/delete> wrote in message
    news:[email protected]...
    > Let's see if I can get this right this time:
    >
    > Range(rngB(i),rngB(i).Offset(0,18)).Copy _
    > Destination:=Sheets("Sheet2").Range("A65536").End(xlUp).offset(1,0)
    >
    >
    > "Sharon" wrote:
    >
    > > Hi again,
    > >
    > > I figured out how to make the error go away, but now the macro doesn't

    do
    > > anything at all.
    > >
    > > Here's what I changed:
    > >
    > > Range(rngB(i), rngB).Offset(0, 18).Copy _
    > >
    > > Here's what it was:
    > >
    > > Range(rngB(i), rng(B).Offset(0, 18)).Copy _
    > >
    > > Any ideas? I am really, really hoping this will work, otherwise, I have

    to
    > > quit this project.
    > >
    > > "gocush" wrote:
    > >
    > > > Try this:
    > > >
    > > > Sub CombineDates()
    > > > Dim rngB As Range, rngA As Range
    > > > Dim cellB As Range
    > > > dim i as Integer
    > > > i=1
    > > >
    > > > 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))
    > > > End With
    > > >
    > > > For i = 1 to Application.CountA(rngB)
    > > > If rngB(i) = rngA(i) Then
    > > > Range(rngB(i),rng(B).Offset(0,18)).Copy _
    > > > Destination:=Sheets("Sheet2").Range("A65536").End(xlUp)(2)
    > > > End if
    > > > Next i
    > > >
    > > > End Sub
    > > >
    > > >
    > > >
    > > > "Sharon" wrote:
    > > >
    > > > > 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




  9. #9
    gocush
    Guest

    Re: Matching cells & returning values

    Tom,
    I like your use of Resize:
    rngB(i).Resize(1,17).Copy

    I need to start using that more often as it is much less cumbersome
    Thanks

    "Tom Ogilvy" wrote:

    > This code copies when the date in S matches the Date in A, but only in the
    > same row.
    >
    > If that is what you want, then it should work with no problem (with the
    > correction).
    >
    > An alternate interpretation is to copy the row if the date in S matches any
    > cell in column A.
    >
    > In which case it would be;
    >
    > Sub CombineDates()
    > Dim rngB As Range, rngA As Range
    > Dim cellB As Range
    > dim i as Integer
    > i=1
    >
    > 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))
    > End With
    >
    > For i = 1 to Application.CountA(rngB)
    > If countif(rngA,rngB(i)) > 0 Then
    > rngB(i).Resize(1,17).Copy _
    > Destination:=Sheets("Sheet2").Range("A65536").End(xlUp)(2)
    > End if
    > Next i
    >
    > End Sub
    >
    > --
    > Regards,
    > Tom Ogilvy
    >
    > "gocush" <[email protected]/delete> wrote in message
    > news:[email protected]...
    > > Let's see if I can get this right this time:
    > >
    > > Range(rngB(i),rngB(i).Offset(0,18)).Copy _
    > > Destination:=Sheets("Sheet2").Range("A65536").End(xlUp).offset(1,0)
    > >
    > >
    > > "Sharon" wrote:
    > >
    > > > Hi again,
    > > >
    > > > I figured out how to make the error go away, but now the macro doesn't

    > do
    > > > anything at all.
    > > >
    > > > Here's what I changed:
    > > >
    > > > Range(rngB(i), rngB).Offset(0, 18).Copy _
    > > >
    > > > Here's what it was:
    > > >
    > > > Range(rngB(i), rng(B).Offset(0, 18)).Copy _
    > > >
    > > > Any ideas? I am really, really hoping this will work, otherwise, I have

    > to
    > > > quit this project.
    > > >
    > > > "gocush" wrote:
    > > >
    > > > > Try this:
    > > > >
    > > > > Sub CombineDates()
    > > > > Dim rngB As Range, rngA As Range
    > > > > Dim cellB As Range
    > > > > dim i as Integer
    > > > > i=1
    > > > >
    > > > > 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))
    > > > > End With
    > > > >
    > > > > For i = 1 to Application.CountA(rngB)
    > > > > If rngB(i) = rngA(i) Then
    > > > > Range(rngB(i),rng(B).Offset(0,18)).Copy _
    > > > > Destination:=Sheets("Sheet2").Range("A65536").End(xlUp)(2)
    > > > > End if
    > > > > Next i
    > > > >
    > > > > End Sub
    > > > >
    > > > >
    > > > >
    > > > > "Sharon" wrote:
    > > > >
    > > > > > 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

    >
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1