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

[Nader]

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]

[Bob Phillips]

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]

[Nader]

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]

[Bob Phillips]

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

[Nader]

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

[Norie]

Red cells? Don't see any.

[Nader]

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.

[Nader]

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]

[Aussiebear]

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

[Nader]

Here is the workbook. and I'm sorry

[Zack Barresse]

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.

[Bob Phillips]

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]

[Nader]

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.

[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!

[Bob Phillips]

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

[Nader]

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.

[Zack Barresse]

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!

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.

[Aussiebear]

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 ( ), but offer an apology to those members of the forum who have gone out of their way to assist.

[Simon Lloyd]

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!

I strongly believe that those who cross post without indicating a linkage should not be apologising the gunslinger ( ), 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!).

[Aussiebear]

Fair enough