Solved: Copy cells in select columns that match other cells in select columns

I tried this code to copy the item in the columns 1,2 ,7,8,9,10 on condition that belong to match cells in columns 3,4,5,6 in same row as it show in the pic. but it didn't succeed with me.

[vba]
Dim MyRange1 As Range
Dim MyRange2 As Range
Dim cell As Range
Dim cellA As Range
Dim i As Long
Set MyRange1 = Sheets(1).Range("C1")
Set MyRange2 = Sheets(1).Range("L1")
For i = 1 To UsedRange.Rows.Count
For Each cell In MyRange1
For Each cellA In MyRange2
If cell.Value = TextBox1.Text And cell.Offset(0, 1).Value = TextBox2.Text _
And cell.Offset(0, 2).Value = TextBox3.Text And cell.Offset(0, 3).Value = TextBox4.Text Then
cellA(i, 12).Value = cell.Offset(i, -2).Value
cellA(i, 13).Value = cell.Offset(i, -1).Value
cellA(i, 14).Value = cell.Offset(i, 7).Value
cellA(i, 15).Value = cell.Offset(i, 8).Value
cellA(i, 16).Value = cell.Offset(i, 9).Value
cellA(i, 17).Value = cell.Offset(i, 10).Value
End If

Next cellA
Next cell
Next i

[/vba]

Guessing, but maybe

[vba]

Dim MyRange1 As Range
Dim MyRange2 As Range
Dim cell As Range
Dim cellA As Range
Dim i As Long
Set MyRange1 = Sheets(1).Range("C1")
Set MyRange2 = Sheets(1).Range("L1")
For i = 1 To UsedRange.Rows.Count
For Each cell In MyRange1
For Each cellA In MyRange2
If cell.Value = TextBox1.Text And cell.Offset(0, 1).Value = TextBox2.Text _
And cell.Offset(0, 2).Value = TextBox3.Text And cell.Offset(0, 3).Value = TextBox4.Text Then
cells(i, 12).Value = cell.Offset(i, -2).Value
cells(i, 13).Value = cell.Offset(i, -1).Value
cells(i, 14).Value = cell.Offset(i, 7).Value
cells(i, 15).Value = cell.Offset(i, 8).Value
cells(i, 16).Value = cell.Offset(i, 9).Value
cells(i, 17).Value = cell.Offset(i, 10).Value
End If

Next cellA
Next cell
Next i
[/vba]

I tried but didn't work. by the way I made it like this:
[VBA]
Dim MyRange1 As Range
Dim cell As Range
Dim i As Long
Set MyRange1 = Sheets(1).Range("C1")
For i = 1 To UsedRange.Rows.Count
For Each cell In MyRange1
If cell.Value = TextBox1.Text And cell.Offset(0, 1).Value = TextBox2.Text _
And cell.Offset(0, 2).Value = TextBox3.Text And cell.Offset(0, 3).Value = TextBox4.Text Then
Cells(i, 12).Value = cell.Offset(i, -2).Value
Cells(i, 13).Value = cell.Offset(i, -1).Value
Cells(i, 14).Value = cell.Offset(i, 7).Value
Cells(i, 15).Value = cell.Offset(i, 8).Value
Cells(i, 16).Value = cell.Offset(i, 9).Value
Cells(i, 17).Value = cell.Offset(i, 10).Value
End If


Next cell
Next i
[/VBA]

Then I have little idea what you are trying to achieve.

If you took a look at the pic. each cells after the last red cell and before the red cell they arange in the columns as I signed by arangement. on condition they belong to rwos that contain 4 columns match (C,D,E,F).

The red cells in the coulumns are match so the code will copy the cells in the columns : A,B,G,H,I,J . as it show in the end of the pic. I hope I'm clear.
Thank you for help

Red cells? Don't see any.:confused:

I mean red item (K,D,D,K). I'll try to explain again:

this code is to arrange the items in the columns 1,2,7,8.9.10 on condition that match in 4 columns 3,4,5,6.

Example: I set in the textbox the item that belong to the columns 3,4,5,6. (K,D,D,K). So the code must copy the item in the columns 1,2,7,8.9.10 in three rows ( that the columns 3,4,5,6. contain these item K,D,D,K) as ite show in the end of the pic ( I signed by "Arrangement"). I hope I'm clear.

I tred this code and it works well but only for the items which don't contain blank rows between them. so the problem that I faced now is that arrange the output items in the same rwos with input items and I want to make this code or another code work with if there are a balnk rows between the items as it show in the pic above.

[VBA]Dim i As Long
For i = 1 To UsedRange.Rows.Count

For j = 1 To UsedRange.Rows.Count

Cells(i, 12).Value = Cells(j, 1).Value
Cells(i, 13).Value = Cells(j, 2).Value
Cells(i, 14).Value = Cells(j, 7).Value
Cells(i, 15).Value = Cells(j, 8).Value
Cells(i, 16).Value = Cells(j, 9).Value
Cells(i, 17).Value = Cells(j, 10).Value


Next j
Next i[/VBA]

Nader, would you be so kind as to post your workbook? There is some difficulty in understanding what you are chasing here.

Here is the workbook. and I'm sorry

Please stop cross-posting...

http://www.xtremevbtalk.com/showthread.php?t=294442
http://www.vbforums.com/showthread.php?t=513747

If I keep catching you do this I will start deleting your posts. If you post the same question on another forum, at least post the link, so that our members know what they are getting into.

Try this

[vba]

Private Sub CommandButton1_Click()
Dim mpStartRow As Long
Dim mpRow As Range
Dim MyRange1 As Range
Dim MyRange2 As Range
Dim cell As Range
Dim cellA As Range
Dim i As Long, j As Long

Set MyRange1 = Sheets(1).Range("C1")
Set MyRange2 = Sheets(1).Range("L1")

mpStartRow = UsedRange.Cells(1, 1).Row
For i = 1 To UsedRange.Rows.Count
If Cells(mpStartRow - 1 + i, "C").Value = TextBox1.Text And _
Cells(mpStartRow - 1 + i, "D").Value = TextBox2.Text And _
Cells(mpStartRow - 1 + i, "E").Value = TextBox3.Text And _
Cells(mpStartRow - 1 + i, "F").Value = TextBox4.Text Then
Cells(mpStartRow - 1 + i, "L").Value = Cells(mpStartRow - 1 + i, "A").Value
Cells(mpStartRow - 1 + i, "M").Value = Cells(mpStartRow - 1 + i, "B").Value
Cells(mpStartRow - 1 + i, "N").Value = Cells(mpStartRow - 1 + i, "G").Value
Cells(mpStartRow - 1 + i, "O").Value = Cells(mpStartRow - 1 + i, "H").Value
Cells(mpStartRow - 1 + i, "P").Value = Cells(mpStartRow - 1 + i, "I").Value
Cells(mpStartRow - 1 + i, "Q").Value = Cells(mpStartRow - 1 + i, "J").Value
End If
Next i
End Sub
[/vba]

Quote:

---

Originally Posted by xld

Try this

[vba]

Private Sub CommandButton1_Click()
Dim mpStartRow As Long
Dim mpRow As Range
Dim MyRange1 As Range
Dim MyRange2 As Range
Dim cell As Range
Dim cellA As Range
Dim i As Long, j As Long

Set MyRange1 = Sheets(1).Range("C1")
Set MyRange2 = Sheets(1).Range("L1")

mpStartRow = UsedRange.Cells(1, 1).Row
For i = 1 To UsedRange.Rows.Count
If Cells(mpStartRow - 1 + i, "C").Value = TextBox1.Text And _
Cells(mpStartRow - 1 + i, "D").Value = TextBox2.Text And _
Cells(mpStartRow - 1 + i, "E").Value = TextBox3.Text And _
Cells(mpStartRow - 1 + i, "F").Value = TextBox4.Text Then
Cells(mpStartRow - 1 + i, "L").Value = Cells(mpStartRow - 1 + i, "A").Value
Cells(mpStartRow - 1 + i, "M").Value = Cells(mpStartRow - 1 + i, "B").Value
Cells(mpStartRow - 1 + i, "N").Value = Cells(mpStartRow - 1 + i, "G").Value
Cells(mpStartRow - 1 + i, "O").Value = Cells(mpStartRow - 1 + i, "H").Value
Cells(mpStartRow - 1 + i, "P").Value = Cells(mpStartRow - 1 + i, "I").Value
Cells(mpStartRow - 1 + i, "Q").Value = Cells(mpStartRow - 1 + i, "J").Value
End If
Next i
End Sub
[/vba]

---

It work well but the new problem that I faced is the output item arrange in separate rows,as it show in the pic below. I want there are no any blank rows between them as I signed in the pic below.

Nader, no acknowledgement to Firefytr? you are not new to this forum or any other for that matter so you know the rules by now!

You are very lucky to be having continued help!

He only got it because I didn't know. Now I am applying my policy.

I nthe first for the cross posting I did that for to reasons:
1- I didn't have a soulition on this forum.
2- I didn't understand how to send cross posting althouhg I read this article of cross postin http://www.excelguru.ca/node/7

I know you are nerveous because of my ignoring. I didn't ignore anything but as I told you. I'm so sorry, but may you help me please and tell me how to cross posting when I have like this problem it's mean what I must to do by this topic to make it cross posting.




the second:
I made some details on the code ( by add j counter to the output item), and it succeed with me.

[vba]
Dim mpStartRow As Long
Dim i As Long, j As Long
j = 1
mpStartRow = UsedRange.Cells(1, 1).Row
For i = 1 To UsedRange.Rows.Count
If Cells(mpStartRow - 1 + i, "C").Value = TextBox1.Text And _
Cells(mpStartRow - 1 + i, "D").Value = TextBox2.Text And _
Cells(mpStartRow - 1 + i, "E").Value = TextBox3.Text And _
Cells(mpStartRow - 1 + i, "F").Value = TextBox4.Text Then

Cells(j, "L").Value = Cells(mpStartRow - 1 + i, "A").Value
Cells(j, "M").Value = Cells(mpStartRow - 1 + i, "B").Value
Cells(j, "N").Value = Cells(mpStartRow - 1 + i, "G").Value
Cells(j, "O").Value = Cells(mpStartRow - 1 + i, "H").Value
Cells(j, "P").Value = Cells(mpStartRow - 1 + i, "I").Value
Cells(j, "Q").Value = Cells(mpStartRow - 1 + i, "J").Value
j = j + 1
End If
Next i
[/vba]

By the way xld why: mpStartRow and why mpStartRow - 1 + i

Thank you again for help.

Quote:

---

Originally Posted by Simon Lloyd

Nader, no acknowledgement to Firefytr? you are not new to this forum or any other for that matter so you know the rules by now!

You are very lucky to be having continued help!

---

LOL! No worries Simon, s/he did in fact acknowledge me via pm. Even if s/he did not, that is fine, the information was given, which I'm plenty cool with. And yeah, don't make me break off a can of whoop a$$!! LOL! :devil2:

I don't think Nader was aware of the ramifications of cross-posting(s), which s/he seems to be well aware of now. Like I said, I'm very comfortable with Nader. :yes

Sorry Zack but I don't agree so easily with your point of view. The issue of cross posting without a linkage, has been raised with increasing frequency over the last couple of months. We all know why we should post the link...

Either we value the assistance given by the members of this forum or we don't.

Personally I believe that, those who cross post do so for the primary reason that they are dis-satisfied with the progress of their post. In this case, Nader's post was difficult to understand, from either its intent or layout, before the workbook was provided. Maybe if it had been presented in a different layout, the responce may have been different.

I strongly believe that those who cross post without indicating a linkage should not be apologising the gunslinger ( :fireman2:), but offer an apology to those members of the forum who have gone out of their way to assist.

Quote:

---

Originally Posted by Aussiebear

those who cross post do so for the primary reason that they are dis-satisfied with the progress of their post

---

I have to disagree, when i started out with this terrible addiction i would post in a couple of forums because i wanted the answer NOW!, but finding that the world doesn't work like that and the fact that i had my wrists slapped (thanks Bob!) i stopped and realised why, from then on i only ever posed one question in one forum!

Quote:

---

Originally Posted by Aussiebear

I strongly believe that those who cross post without indicating a linkage should not be apologising the gunslinger ( :fireman2:), but offer an apology to those members of the forum who have gone out of their way to assist.

---

this i do agree with, i have nothing against crossposting but it does really annoy me when the appropriate protocol isn't followed, i'm a mod at another forum and new posters get a polite "shove" in the right direction, the more seasoned get a warning and the habitual get their posts locked (i rarely have to do the latter!).

Fair enough :friends:

Hi,

[vba]Private Sub CommandButton1_Click()
Dim i As Long, j As Long, a, w(), s, Str As String
a = Range("a3:j" & Range("a" & Rows.Count).End(xlUp).Row)
ReDim w(1 To UBound(a, 1), 1 To 6)
Str = Me.TextBox1 & Me.TextBox2 & Me.TextBox3 & Me.TextBox4
For i = 1 To UBound(a, 1)
With Application
s = Replace(Join(.Index(a, i, 0), "|"), "|", "")
End With
If Mid$(s, 3, 4) = Str Then
j = j + 1: w(j, 1) = a(i, 1): w(j, 2) = a(i, 2)
w(j, 3) = a(i, 7): w(j, 4) = a(i, 8)
w(j, 5) = a(i, 9): w(j, 6) = a(i, 10)
End If
Next
If j > 0 Then
With Range("L4")
For i = 1 To 4
.Offset(-2, i).Value = Mid$(Str, i, 1)
Next
.Resize(j, 6).Value = w
End With
End If
End Sub[/vba]

HTH

I think Simon is right, and that is certainly my take on it. It is not dissatisfaction, they should b*##%r off if they aren't happy, but more an impatience and a general view that we are here to serve them, so they are justified (in their minds) in wasting as much time of people as they can.

Personally, as I keep saying, my policy is that once I am aware that a question is cross-posted, I am outta there, I a m not going to even potentially waste my time, there are more deserving cases. And of course, serial cross-posters will just get ignored.

OzGrid bans cross-posters. I hope that VBAX never gets draconian like that, the members can make their own choices.

Quote:

---

Originally Posted by firefytr

LOL! No worries Simon, s/he did in fact acknowledge me via pm. Even if s/he did not, that is fine, the information was given, which I'm plenty cool with. And yeah, don't make me break off a can of whoop a$$!! LOL! :devil2:

I don't think Nader was aware of the ramifications of cross-posting(s), which s/he seems to be well aware of now. Like I said, I'm very comfortable with Nader. :yes

---

I acknowledge by my mistake taht I did cross posting many times, and I'm so sorry.

Quote:

---

Originally Posted by Aussiebear

Nader, would you be so kind as to post your workbook? There is some difficulty in understanding what you are chasing here.

---

I acknowledge for firefytr by my mistake taht I did cross posting many times, and I'm so sorry firefytr.

Oooooh! Nader, so touchy! in the words of the baird 'thou protesteth too much!'

Quote:

---

Originally Posted by Simon Lloyd

Oooooh! Nader, so touchy! in the words of the baird 'thou protesteth too much!'

---

What does mean "protesteth".

Nader, we are not saying you should not cross post. If you feel the need to cross post then simply add a link to the other forum, so that those members who feel inclined to assist you can see what help has been offered to date.

I read the role of cross posting in this link http://www.excelguru.ca/node/7
But I couldn't understand this item: - Ask people to post their replies in the original forum.
How the people will send the post in the original forum if they are not memebers in it.

Whilst it's true that not all the people who are trying to assist you, may be able to gain access to another forum, we have sufficent members here who frequent a number of forums. They may be able to assist.

Why not try to post a link now? This way a lot of the "heat" in this matter will simply disappear. Copy the URL address of the thread in the other forum, to a new reply here and see how it goes?

I'm sure others here will thank you for the effort

Quote:

---

Originally Posted by Aussiebear

Whilst it's true that not all the people who are trying to assist you, may be able to gain access to another forum, we have sufficent members here who frequent a number of forums. They may be able to assist.

Why not try to post a link now? This way a lot of the "heat" in this matter will simply disappear. Copy the URL address of the thread in the other forum, to a new reply here and see how it goes?

I'm sure others here will thank you for the effort

---

I did that and here is the link: http://www.mrexcel.com/forum/showthread.php?t=309951

Thank you all.

Thank you Nader. :thumb