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

"ryguy7272" wrote:

> 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

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

"ryguy7272" wrote:

> 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

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


"Joel" wrote:

> 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
>
> "ryguy7272" wrote:
>
> > 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

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)




"ryguy7272" wrote:

> 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
>
>
> "Joel" wrote:
>
> > 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
> >
> > "ryguy7272" wrote:
> >
> > > 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

That's it! Thank you so much!!
Ryan--

--
RyGuy


"Joel" wrote:

> 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)
>
>
>
>
> "ryguy7272" wrote:
>
> > 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
> >
> >
> > "Joel" wrote:
> >
> > > 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
> > >
> > > "ryguy7272" wrote:
> > >
> > > > 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

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

"Mike H" wrote:

> 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
>
> "ryguy7272" wrote:
>
> > 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