Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Huge problem with comparing cells in different ranges and sheets
Hi!
Could anyone help me with this one. I have two sheets: "Report" and "Data". On Report-sheet I have two columns AE and AF. On AE there are 13 names in random order 40 times on area "AE1:AE40" and on AF i have random values on area "AF1:AF40". On Data-sheet I have same 13 names on area "A1:A13". I need macro that will copy value from AF1 to AF40 if the name on AE1 is same as A1. And so on...Something like this: Report-sheet Data-sheet DA 2354 DA 2354 98 887 EA 3321 DB FG 3214 EA 3321 DA 98 ... DA 887 ... I've been strugling with this one over 3 days, so any help will be appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Huge problem with comparing cells in different ranges and sheets
1. Do you want the pasted values to begin in column B of "Data"?
2. Do you want the values pasted in their individual cells horizontally for each name? Or 3. Do you want the values pasted in the same cell, delimited with a space? "Monoxito" wrote: Hi! Could anyone help me with this one. I have two sheets: "Report" and "Data". On Report-sheet I have two columns AE and AF. On AE there are 13 names in random order 40 times on area "AE1:AE40" and on AF i have random values on area "AF1:AF40". On Data-sheet I have same 13 names on area "A1:A13". I need macro that will copy value from AF1 to AF40 if the name on AE1 is same as A1. And so on...Something like this: Report-sheet Data-sheet DA 2354 DA 2354 98 887 EA 3321 DB FG 3214 EA 3321 DA 98 ... DA 887 ... I've been strugling with this one over 3 days, so any help will be appreciated. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Huge problem with comparing cells in different ranges and sheets
Sub copylikecells() 'from the destination sheet list in col E For i = 2 To Cells(Rows.Count, "e").End(xlUp).Row With Sheets("Sheet22").Columns(2)'modify to suit source Set c = .Find(Cells(i, "e").Value, LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do lc = Cells(i, Columns.Count).End(xlToLeft).Column + 1 c.Offset(, 1).Copy Cells(i, lc) Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With Next End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Monoxito" wrote in message ... Hi! Could anyone help me with this one. I have two sheets: "Report" and "Data". On Report-sheet I have two columns AE and AF. On AE there are 13 names in random order 40 times on area "AE1:AE40" and on AF i have random values on area "AF1:AF40". On Data-sheet I have same 13 names on area "A1:A13". I need macro that will copy value from AF1 to AF40 if the name on AE1 is same as A1. And so on...Something like this: Report-sheet Data-sheet DA 2354 DA 2354 98 887 EA 3321 DB FG 3214 EA 3321 DA 98 ... DA 887 ... I've been strugling with this one over 3 days, so any help will be appreciated. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Huge problem with comparing cells in different ranges and sheets
Assuming you want the values to be listed in individual cells horizontally
starting in columb B. Sub cpyVpstH() Dim lstCol As Long lstCol = Worksheets("Data").Cells(1, Columns.Count).End(xlToLeft).Column Set srcRng = Worksheets("Data").Range("$A$1:$A$13") Set Wksr = Worksheets("Report") For Each C In srcRng If Not C Is Nothing Then x = C.Address End If a = 1 For i = 1 To 40 If C = Wksr.Cells(i, 31) Then Wksr.Cells(i, 31).Offset(0, 1).Copy Cells(Range(x).Row, lstCol + a) a = a + 1 End If Next i Next C End Sub "Monoxito" wrote: Hi! Could anyone help me with this one. I have two sheets: "Report" and "Data". On Report-sheet I have two columns AE and AF. On AE there are 13 names in random order 40 times on area "AE1:AE40" and on AF i have random values on area "AF1:AF40". On Data-sheet I have same 13 names on area "A1:A13". I need macro that will copy value from AF1 to AF40 if the name on AE1 is same as A1. And so on...Something like this: Report-sheet Data-sheet DA 2354 DA 2354 98 887 EA 3321 DB FG 3214 EA 3321 DA 98 ... DA 887 ... I've been strugling with this one over 3 days, so any help will be appreciated. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Huge problem with comparing cells in different ranges and sheets
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Huge problem with comparing cells in different ranges and shee
Thanks both of you for your trouble. I think it works, but it just doesn't
paste anything. Could you help me with that one? And yes, values should be in individual cells. I need to make dynamic graph out of this and it has quite a lot of data on it. "JLGWhiz" kirjoitti: Assuming you want the values to be listed in individual cells horizontally starting in columb B. Sub cpyVpstH() Dim lstCol As Long lstCol = Worksheets("Data").Cells(1, Columns.Count).End(xlToLeft).Column Set srcRng = Worksheets("Data").Range("$A$1:$A$13") Set Wksr = Worksheets("Report") For Each C In srcRng If Not C Is Nothing Then x = C.Address End If a = 1 For i = 1 To 40 If C = Wksr.Cells(i, 31) Then Wksr.Cells(i, 31).Offset(0, 1).Copy Cells(Range(x).Row, lstCol + a) a = a + 1 End If Next i Next C End Sub "Monoxito" wrote: Hi! Could anyone help me with this one. I have two sheets: "Report" and "Data". On Report-sheet I have two columns AE and AF. On AE there are 13 names in random order 40 times on area "AE1:AE40" and on AF i have random values on area "AF1:AF40". On Data-sheet I have same 13 names on area "A1:A13". I need macro that will copy value from AF1 to AF40 if the name on AE1 is same as A1. And so on...Something like this: Report-sheet Data-sheet DA 2354 DA 2354 98 887 EA 3321 DB FG 3214 EA 3321 DA 98 ... DA 887 ... I've been strugling with this one over 3 days, so any help will be appreciated. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Huge problem with comparing cells in different ranges and shee
Yes ofcourse, now it works very well. I misread and misunderstood the code
wrong. Thank you very much for your trouble. "Don Guillett" kirjoitti: My posting was tested. You just need to adjust to YOUR data layout. It assumes that you focus(cursor) is on the sheet of the destination list. It assumes that this list starts on row 2 of col E. It assumes that your source data is on sheet22 in col B and that the numbers desired are in col C. Sub copylikecells() 'from the destination sheet list in col E For i = 2 To Cells(Rows.Count, "e").End(xlUp).Row With Sheets("Sheet22").Columns(2)'modify to suit source Set c = .Find(Cells(i, "e").Value, LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do lc = Cells(i, Columns.Count).End(xlToLeft).Column + 1 c.Offset(, 1).Copy Cells(i, lc) Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With Next End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Monoxito" wrote in message ... Thanks both of you for your trouble. I think it works, but it just doesn't paste anything. Could you help me with that one? And yes, values should be in individual cells. I need to make dynamic graph out of this and it has quite a lot of data on it. "JLGWhiz" kirjoitti: Assuming you want the values to be listed in individual cells horizontally starting in columb B. Sub cpyVpstH() Dim lstCol As Long lstCol = Worksheets("Data").Cells(1, Columns.Count).End(xlToLeft).Column Set srcRng = Worksheets("Data").Range("$A$1:$A$13") Set Wksr = Worksheets("Report") For Each C In srcRng If Not C Is Nothing Then x = C.Address End If a = 1 For i = 1 To 40 If C = Wksr.Cells(i, 31) Then Wksr.Cells(i, 31).Offset(0, 1).Copy Cells(Range(x).Row, lstCol + a) a = a + 1 End If Next i Next C End Sub "Monoxito" wrote: Hi! Could anyone help me with this one. I have two sheets: "Report" and "Data". On Report-sheet I have two columns AE and AF. On AE there are 13 names in random order 40 times on area "AE1:AE40" and on AF i have random values on area "AF1:AF40". On Data-sheet I have same 13 names on area "A1:A13". I need macro that will copy value from AF1 to AF40 if the name on AE1 is same as A1. And so on...Something like this: Report-sheet Data-sheet DA 2354 DA 2354 98 887 EA 3321 DB FG 3214 EA 3321 DA 98 ... DA 887 ... I've been strugling with this one over 3 days, so any help will be appreciated. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Huge problem with comparing cells in different ranges and shee
Glad you got it sorted out.
-- Don Guillett Microsoft MVP Excel SalesAid Software "Monoxito" wrote in message ... Yes ofcourse, now it works very well. I misread and misunderstood the code wrong. Thank you very much for your trouble. "Don Guillett" kirjoitti: My posting was tested. You just need to adjust to YOUR data layout. It assumes that you focus(cursor) is on the sheet of the destination list. It assumes that this list starts on row 2 of col E. It assumes that your source data is on sheet22 in col B and that the numbers desired are in col C. Sub copylikecells() 'from the destination sheet list in col E For i = 2 To Cells(Rows.Count, "e").End(xlUp).Row With Sheets("Sheet22").Columns(2)'modify to suit source Set c = .Find(Cells(i, "e").Value, LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do lc = Cells(i, Columns.Count).End(xlToLeft).Column + 1 c.Offset(, 1).Copy Cells(i, lc) Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With Next End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Monoxito" wrote in message ... Thanks both of you for your trouble. I think it works, but it just doesn't paste anything. Could you help me with that one? And yes, values should be in individual cells. I need to make dynamic graph out of this and it has quite a lot of data on it. "JLGWhiz" kirjoitti: Assuming you want the values to be listed in individual cells horizontally starting in columb B. Sub cpyVpstH() Dim lstCol As Long lstCol = Worksheets("Data").Cells(1, Columns.Count).End(xlToLeft).Column Set srcRng = Worksheets("Data").Range("$A$1:$A$13") Set Wksr = Worksheets("Report") For Each C In srcRng If Not C Is Nothing Then x = C.Address End If a = 1 For i = 1 To 40 If C = Wksr.Cells(i, 31) Then Wksr.Cells(i, 31).Offset(0, 1).Copy Cells(Range(x).Row, lstCol + a) a = a + 1 End If Next i Next C End Sub "Monoxito" wrote: Hi! Could anyone help me with this one. I have two sheets: "Report" and "Data". On Report-sheet I have two columns AE and AF. On AE there are 13 names in random order 40 times on area "AE1:AE40" and on AF i have random values on area "AF1:AF40". On Data-sheet I have same 13 names on area "A1:A13". I need macro that will copy value from AF1 to AF40 if the name on AE1 is same as A1. And so on...Something like this: Report-sheet Data-sheet DA 2354 DA 2354 98 887 EA 3321 DB FG 3214 EA 3321 DA 98 ... DA 887 ... I've been strugling with this one over 3 days, so any help will be appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing two ranges on different sheets and copy matching results to new worksheet | Excel Programming | |||
Comparing data in cells on 2 sheets | Excel Discussion (Misc queries) | |||
Huge sheet into smaller sheets | Excel Programming | |||
Huge sheet into smaller sheets | Links and Linking in Excel | |||
problem with Rows.Count and comparing cells | Excel Programming |