Associating two different cells
Closed
andy
-
Jan 21, 2010 at 02:18 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jan 21, 2010 at 09:25 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jan 21, 2010 at 09:25 PM
Related:
- Associating two different cells
- Excel arrow keys not moving cells - Guide
- Excel macro to create new sheet based on value in cells - Guide
- Based on the values in cells b77 ✓ - Excel Forum
- Arrow keys scrolling in Excel ✓ - Excel Forum
- Clear cell contents based on value of another cell range using VBA ✓ - Excel Forum
1 response
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jan 21, 2010 at 09:25 PM
Jan 21, 2010 at 09:25 PM
your data is like this (heading are necessary)
from A1 to B4
NAME DATA
Jim 5
Bob 3
Jim 2
now run this macro and see whether you get what you want
from A1 to B4
NAME DATA
Jim 5
Bob 3
Jim 2
now run this macro and see whether you get what you want
Sub TEST()
Dim cfind As Range, add As String
Dim r As Range, dest As Range, ru As Range, c As Range
Worksheets("sheet1").Activate
On Error Resume Next
Set r = Range(Range("A1"), Range("A1").End(xlDown))
Set dest = Cells(Rows.Count, "A").End(xlUp).Offset(5, 0)
r.AdvancedFilter xlFilterCopy, , dest, True
Set ru = Range(dest.Offset(1, 0), dest.End(xlDown))
For Each c In ru
Set cfind = r.Cells.Find(what:=c.Value, lookat:=xlWhole)
add = cfind.Address
c.Offset(0, 1) = cfind.Offset(0, 1)
Do
Set cfind = r.Cells.FindNext(cfind)
If cfind Is Nothing Then Exit Do
If cfind.Address = add Then Exit Do
Cells(c.Row, Columns.Count).End(xlToLeft).Offset(0, 1) = cfind.Offset(0, 1)
Loop
Next
End Sub
