I accidentally posted this in the Excel-Functions group; sorry all. Anyway,

I'm trying to get this macro to compare values in Column A and Column M,

starting in Cell(2, 13), and if there is a match, copy the value from Column

N (that corresponds to Column M) into Column E. For instance, 4/1/2008 is

in Cell A1 (and the dates go down consecutively). I have 4/6/2008 in M2 and

5,000,000 in N2. How can I get 5,000,000 into E6 (A6 contains 4/6/2008)?

Confused yet? I am.

Sub MatchAandM()

Dim Lrow As Long

Dim Rng As Range, i As Range, xRng As Range

Lrow = Range("A65536").End(xlUp).Row

Set Rng = Range(Cells(2, 13), Cells(Lrow, 2))

For Each i In Rng

Set xRng = Rng.Find(What:=i.Offset(0, -1).Value, _

LookIn:=xlValues, MatchCase:=False)

If xRng Is Nothing Then

'Else

i.Offset(0, -1).Copy i.Offset(0, 1)

End If

Next i

End Sub

Thanks for the help; I truly appreciate it.

Regards,

Ryan--

--

RyGuy

Hi,

I think you said if A2=M2 then make E2 equal to N2

so right click the sheet tab, view code and paste this in

Sub MatchAandM()

Dim Lrow As Long

Dim Rng As Range, i As Range, xRng As Range

Lrow = Cells(Rows.Count, "A").End(xlUp).Row

Set Rng = Range("A2:A" & Lrow)

For Each i In Rng

If i.Value = i.Offset(0, 12).Value Then

i.Offset(0, 4).Value = i.Offset(0, 13).Value

End If

Next

End Sub

Mike

I think you said if A2=M2 then make E2 equal to N2

so right click the sheet tab, view code and paste this in

Sub MatchAandM()

Dim Lrow As Long

Dim Rng As Range, i As Range, xRng As Range

Lrow = Cells(Rows.Count, "A").End(xlUp).Row

Set Rng = Range("A2:A" & Lrow)

For Each i In Rng

If i.Value = i.Offset(0, 12).Value Then

i.Offset(0, 4).Value = i.Offset(0, 13).Value

End If

Next

End Sub

Mike

Sub MatchAandM()

Dim Lrow As Long

Dim RowCount As Long

Dim xRng As Range

Lrow = Range("A" & Rows.Count).End(xlUp).Row

For RowCount = 2 To Lrow

FindVal = Range("A" & RowCount)

Set xRng = Columns("M:M").Find(What:=FindVal, _

LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)

If Not xRng Is Nothing Then

xRng.Offset(0, 1).Copy xRng.Offset(0, -8)

End If

Next RowCount

End Sub

Dim Lrow As Long

Dim RowCount As Long

Dim xRng As Range

Lrow = Range("A" & Rows.Count).End(xlUp).Row

For RowCount = 2 To Lrow

FindVal = Range("A" & RowCount)

Set xRng = Columns("M:M").Find(What:=FindVal, _

LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)

If Not xRng Is Nothing Then

xRng.Offset(0, 1).Copy xRng.Offset(0, -8)

End If

Next RowCount

End Sub

Joel, its close. I realize now that didn't describe the problem well enough.

The dates are arranged in descending order in Col A. 4/1/2008 is in A1,

4/6/2008 is in A6 and 5/1/2008 is in A31. 4//2008 is in M2 and 5/1/2008 is

in M3. 5 is in N2 and 2 is in N3. I was hoping to match the dates in Col M

to those in Col A, and if there is a match, copy/paste the values in Col N to

the corresponding row in Col E. Thus, E6 would contain 5 and E31 would

contain 2.

I'll try to fiddle with your code, but I'm a bit lost with this one. If you

know how to modify the code to do what I was hoping to do, please make the

change and send it along...

--

RyGuy

The dates are arranged in descending order in Col A. 4/1/2008 is in A1,

4/6/2008 is in A6 and 5/1/2008 is in A31. 4//2008 is in M2 and 5/1/2008 is

in M3. 5 is in N2 and 2 is in N3. I was hoping to match the dates in Col M

to those in Col A, and if there is a match, copy/paste the values in Col N to

the corresponding row in Col E. Thus, E6 would contain 5 and E31 would

contain 2.

I'll try to fiddle with your code, but I'm a bit lost with this one. If you

know how to modify the code to do what I was hoping to do, please make the

change and send it along...

--

RyGuy

I think you need only two changes

1) Data in column A starts in row 1 not 2

from

For RowCount = 2 To Lrow

to

For RowCount = 1 To Lrow

2) You want the data in column N to move to the row matching the data in

Column A (not column M)

from

xRng.Offset(0, 1).Copy xRng.Offset(0, -8)

to

xRng.Offset(0, 1).Copy Range("E" & RowCount)

1) Data in column A starts in row 1 not 2

from

For RowCount = 2 To Lrow

to

For RowCount = 1 To Lrow

2) You want the data in column N to move to the row matching the data in

Column A (not column M)

from

xRng.Offset(0, 1).Copy xRng.Offset(0, -8)

to

xRng.Offset(0, 1).Copy Range("E" & RowCount)

hat's it! Thank you so much!!

Ryan--

--

RyGuy

"Joel" wrote:

Ryan--

--

RyGuy

"Joel" wrote:

Mike,

I apologize for piggybacking into Joel's question but I want to do the same

thing but I have a row of data that I need to paste instead of just a cell.

In other words, if M match A then I want to copy N through S in E1. I have

3600 rows of data to match. If a data in M doesn't match any in A, paste N

through S in AA, sort of a collection area.

Thanks

DPingger

I apologize for piggybacking into Joel's question but I want to do the same

thing but I have a row of data that I need to paste instead of just a cell.

In other words, if M match A then I want to copy N through S in E1. I have

3600 rows of data to match. If a data in M doesn't match any in A, paste N

through S in AA, sort of a collection area.

Thanks

DPingger

1. Rows & Cols - copy formulas in Col A to # rows in Col B

2. Thanks Merging Col A and Col B in Col C including blank cells

3. Compare 2 columns if match insert data from 3rd col into new col

4. Compare data in col A+B wih data in col C+D

5. comparing many cols of data to one col

6. If function to match cols and return value of another col

7. matching numbers in col a and col b

8. Find Matching Text In Col A move to Col B

9. If cell in a Col of doc1is = to cell in a Col in doc2, copy cell .

10. If #N/A in Col. AB, copy/paste Items in Col C. to New Sheet

11. Copy Records from Query Col. to Table Col.

12. Works if I copy F Col to A Col

13. how do I make different col widths within same col. on same sheet

14. "Send to" changes mailing label format fom 3-col to 1-col!!!

15. How to change Listview w/different #of col and col header during r

7 post • Page:**1** of **1**