Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Matching cells & returning values
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 183
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 252
Default 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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 183
Default 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
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 183
Default 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
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 252
Default 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
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 252
Default 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
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4,339
Default 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
Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default 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" /delete wrote in message
...
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 With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 252
Default 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" /delete wrote in message
...
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 With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
VBA: Matching cells across columns, and returning equal values Babymech Excel Discussion (Misc queries) 0 January 26th 09 04:41 PM
Matching & Returning values DebbieV Excel Discussion (Misc queries) 3 March 6th 08 11:00 AM
Matching and returning values to a master sheet in Excel 2007 Pete Excel Worksheet Functions 0 February 7th 08 03:36 PM
Matching numbers in an Array and returning values for matched numb Tiger Excel Discussion (Misc queries) 8 April 26th 07 06:14 AM
returning all matching values in column A that have the same value for columnB [email protected] Excel Worksheet Functions 3 August 30th 06 06:51 PM


All times are GMT +1. The time now is 08:00 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement