PDA

View Full Version : Solved: Copy cells in select columns that match other cells in select columns



Nader
03-16-2008, 03:31 AM
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.


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

Bob Phillips
03-16-2008, 04:03 AM
Guessing, but maybe



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

Nader
03-16-2008, 08:09 AM
I tried but didn't work. by the way I made it like this:

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

Bob Phillips
03-16-2008, 09:10 AM
Then I have little idea what you are trying to achieve.

Nader
03-16-2008, 11:59 AM
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
03-16-2008, 04:18 PM
Red cells? Don't see any.:confused:

Nader
03-16-2008, 10:28 PM
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
03-17-2008, 01:11 PM
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.

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

Aussiebear
03-17-2008, 02:54 PM
Nader, would you be so kind as to post your workbook? There is some difficulty in understanding what you are chasing here.

Nader
03-17-2008, 03:34 PM
Here is the workbook. and I'm sorry

Zack Barresse
03-17-2008, 04:22 PM
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
03-17-2008, 04:33 PM
Try this



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

Nader
03-17-2008, 10:41 PM
Try this



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


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
03-18-2008, 02:23 AM
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
03-18-2008, 02:44 AM
He only got it because I didn't know. Now I am applying my policy.

Nader
03-18-2008, 07:34 AM
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.


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


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

Thank you again for help.

Zack Barresse
03-18-2008, 02:52 PM
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

Aussiebear
03-19-2008, 02:37 AM
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.

Simon Lloyd
03-19-2008, 02:55 AM
those who cross post do so for the primary reason that they are dis-satisfied with the progress of their postI 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 ( :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!).

Aussiebear
03-19-2008, 03:03 AM
Fair enough :friends:

Krishna Kumar
03-19-2008, 03:19 AM
Hi,

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

HTH

Bob Phillips
03-19-2008, 03:24 AM
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.

Nader
03-19-2008, 04:35 AM
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.

Nader
03-19-2008, 04:38 AM
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.

Simon Lloyd
03-19-2008, 05:12 AM
Oooooh! Nader, so touchy! in the words of the baird 'thou protesteth too much!'

Nader
03-19-2008, 05:57 AM
Oooooh! Nader, so touchy! in the words of the baird 'thou protesteth too much!'

What does mean "protesteth".

Aussiebear
03-19-2008, 09:45 AM
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.

Nader
03-19-2008, 10:36 AM
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.

Aussiebear
03-19-2008, 10:47 AM
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

Nader
03-19-2008, 05:33 PM
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.

Aussiebear
03-19-2008, 05:44 PM
Thank you Nader. :thumb