Hi Guys,
I need your help, I usually record macro, but this one is beyond macro record.
I am trying to pull data from sheet2 and insert them in sheet1. The data I am looking for is the D column in sheet2 (Color)
The challenge that I am having is that if the part in sheet1 exist in sheet2, I want to be able to paste all related colors of this part.
Sorry it is hard to explain what I am trying to do.
So I have attached the excel I am working on.
Thanks a lot
[vba]
Option Explicit
Sub Fruits()
Dim i As Long
Dim x As Boolean
Dim c As Range
Dim ToFind As String
Dim FirstAddress As String
With Sheets(2).Columns("B:B")
'Find rows with data
For i = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
'Set switch
x = True
ToFind = Cells(i, "B")
'Look for value
Set c = .Find(ToFind)
If Not c Is Nothing Then
'Value found; get first address
FirstAddress = c.Address
Do
'with first found value, overwrite existing
If x = True Then
c.Offset(, -1).Resize(, 4).Copy Cells(i, 1)
Else
'with other found values, add to list
c.Offset(, -1).Resize(, 4).Copy
Cells(i, 1).Insert shift:=xlDown
End If
Set c = .FindNext(c)
'Change switch
x = False
'exit when all found values processed
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
Next
End With
End Sub
[/vba]
Last edited by mdmackillop; 01-30-2010 at 04:52 AM. Reason: Code amendment
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
I am sorrybut It gave me a debug error. I tried to understand what is going on but couldn't. The error starts in: [VBA]For i = Range("descript").Cells.Count + 1 To 2 Step -1[/VBA]
Apologies, I created a temporary range name to use in my code. I've amended it to remove this problem.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
an error appeared to be in [vba] c.Offset(, -1).Resize(, 4).Copy Cells(i, 1)[/vba] , can you please tell me what we are trying to do in here?
Never mindOriginally Posted by fadib
it worked. Thanks a lot
I am going to try and look at the code more closely, just to learn from it.
It is very interesting the approach you took, I really like it. Thanks a lot.
I have one question though. how can we tweak the code so that when we copy the data to sheet 1, it falls in the same order as it is in sheet2.
A-1 Apple1 Yellow
A-1 Apple1 Green
A-1 Apple1 Red
instead of
A-1 Apple1 Red
A-1 Apple1 Green
A-1 Apple1 Yellow
Change the switch to a value and use it to offset where the data is inserted.
[vba]Option Explicit
Sub Fruits()
Dim i As Long
Dim x As Long
Dim c As Range
Dim ToFind As String
Dim FirstAddress As String
With Sheets(2).Columns("B:B")
'Find rows with data
For i = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
'Set switch
x = 0
ToFind = Cells(i, "B")
'Look for value
Set c = .Find(ToFind)
If Not c Is Nothing Then
'Value found; get first address
FirstAddress = c.Address
Do
'with first found value, overwrite existing
If x = 0 Then
c.Offset(, -1).Resize(, 4).Copy Cells(i, 1)
Else
'with other found values, add to list
c.Offset(, -1).Resize(, 4).Copy
Cells(i + x, 1).Insert shift:=xlDown
End If
Set c = .FindNext(c)
'Change switch
x = x + 1
'exit when all found values processed
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
Next
End With
End Sub[/vba]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
I am still trying to understand how the switch work. So the whole idea of the switch is to start a count?
How about if I have the data as follows. (Reference Book2)
can we use the switch in this case?
If not, how do we do it?
You seen to have changed all the basic parameters. I now have no idea what the basic purpose is. Can you post a workbook showing expected results and reasoning used?
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
I have no workbook, All I am trying to do is to understand how to tweak the code. In Book1, we had Apple with a quantity of one. we had to replace that using two loops and a switch.
In book2, Apple occupy a row, because the quantity is one. But each row should have a different color. (The color is picked from sheet 2).
In this scenario the number of rows occupied by apple match what is on sheet2.
I don't if this helps.
But one thing I can think of, is to copy what is on sheet 2 into sheet1.
than delete the one that doesn't have a color associated to it. I hope this helps.
Posting Permissions
Reply With Quote