Remember Me?

 ryguy7272 external usenet poster First recorded activity by ExcelBanter: Dec 2006 Posts: 2,836 Compare Col A and Col M, if Match, Copy Col N to Col E

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 H external usenet poster First recorded activity by ExcelBanter: Apr 2007 Posts: 11,501 Compare Col A and Col M, if Match, Copy Col N to Col E

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

 joel external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 9,101 Compare Col A and Col M, if Match, Copy Col N to Col E

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

 ryguy7272 external usenet poster First recorded activity by ExcelBanter: Dec 2006 Posts: 2,836 Compare Col A and Col M, if Match, Copy Col N to Col E

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

 joel external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 9,101 Compare Col A and Col M, if Match, Copy Col N to Col E

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

 ryguy7272 external usenet poster First recorded activity by ExcelBanter: Dec 2006 Posts: 2,836 Compare Col A and Col M, if Match, Copy Col N to Col E

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

 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post ryguy7272 Excel Worksheet Functions 1 March 21st 08 05:57 PM saman110 via OfficeKB.com Excel Discussion (Misc queries) 2 February 21st 08 01:28 AM Dan R. Excel Programming 0 May 23rd 07 08:29 PM Dan R. Excel Programming 0 May 23rd 07 08:04 PM Dan R. Excel Programming 1 May 23rd 07 06:20 PM

All times are GMT +1. The time now is 05:14 PM. Copyright ©2004-2021 ExcelBanter.