Nested FOR loop to comare 2 diffrent cell.

[ittan]

Hi,

I want to:
1. Compare between 2 cells in first row of a big table.
2. If it matches (red color is the match words) copy the content of 'CustomerB to CustomerA' row by row.
3. Code inside the curly bracket {} , i'm not sure how to do this.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]
[TABLE="width: 188"]
<tbody>[TR]
[TD]CustomerA Dose Step 2 Target
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 188"]
<tbody>[TR]
[TD]CustomerA Dose Step 1 Target
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]CustomerB Dose Step 2 Target
[/TD]
[TD]CustomerB Dose Step 1 Target
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]4
[/TD]
[TD]6
[/TD]
[TD]8
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]5
[/TD]
[TD]7
[/TD]
[TD]9
[/TD]
[/TR]
</tbody>[/TABLE]

For i = 17 To 298 Step 1 'range of CustomerA
    For j = 300 To 385 Step 1 'range of CustomerB

      if { cells(1 , i) = cells(1 , j) } Then     '(I am not comparing two cells entirely, I'm comparing the last past of the value. not sure how to do this)
         
           for x = 2 To LastColumn
                cells (x , i) = cells (x , j)
           next x
      next j
 next i

thanks in advance.

 

[Momentman]

Pleas e give an example a match (what the word are and where they are0 and what exactly you want to do. Still unclear abt ur request

 

[ittan]

Pleas e give an example a match (what the word are and where they are0 and what exactly you want to do. Still unclear abt ur request

i know it sounds bit confusing.
how do i put it?

i have an excell sheet with 385 columns
from column 17 to 298 its customerA
from 300 to 385 its customerB
eg:
cell(17,1) is "CustomerA {parameter}"
some where in the range 300 to 385, there is another CustomerB with the same "{parameter}"
If those words match, Then copy the content of 'CustomerB {parameter}' to 'CustomerA {parameter}' row by row.
The{parameter} changes in every cell.

 

[Momentman]

this should work, although I think I need to exit the loop at some point, give it a try

Sub findparam()
    For I = 17 To 288
        For j = 300 To 385
            a = InStr(1, Cells(1, I), " ")
            txt1 = Mid(Cells(1, I), a + 1, Len(Cells(1, I)) - a)
            b = InStr(1, Cells(1, j), " ")
            txt2 = Mid(Cells(1, j), a + 1, Len(Cells(1, j)) - a)
            
                If txt1 = txt2 Then
                    For k = 2 To Cells(Rows.Count, j).End(xlUp).Row
                        Cells(k, I) = Cells(k, j)
                    Next k
                    
                End If
            
        Next j
    Next I
            
End Sub

I also think that the 3rd For loop can be avoided, since you are just copying from one range to another, so we can use something like

Range("A1").copy [destination]

 

[Momentman]

Well, this is more like it

Sub findparam()
    For I = 17 To 298
        For j = 300 To 385
            a = InStr(1, Cells(1, I), " ")
            txt1 = Mid(Cells(1, I), a + 1, Len(Cells(1, I)) - a)
            b = InStr(1, Cells(1, j), " ")
            txt2 = Mid(Cells(1, j), a + 1, Len(Cells(1, j)) - a)
            
                If txt1 = txt2 Then
                
                    For k = 2 To Cells(Rows.Count, j).End(xlUp).Row
                        Cells(k, I) = Cells(k, j)
                    Next k
        Exit For
                    
                End If
            
        Next j
    Next I
            
End Sub

 

[ittan]

YES. it works like a champ. thanks a lot. i used the 3rd loop to run an If statement. for some other purpose. thanks again.

 

[Momentman]

You are very much welcome. Thanks for the feedback