re: Intersection

[cpra3617]

re: Intersection

I have a list of names in column C and a list of job codes in row 2, column E thru AF. The user enters the name of the person they are considering into cell B2 and then the job codes for that person into cells B3 up to B40. I would like a macro that scans the jobs codes entered in column B and, for each one, puts an X in the cell where the column that corresponds to the job code and the row that corresponds to the job name intersect.

So far I have the following:

Sub testJobs()
Application.ScreenUpdating = False
Dim personName As Variant, codeName As Variant, jobName As String, ff As String

Dim i As Integer, j As Integer, h As Integer, lastRow As Long

personName = UCase(Range("B1").Value)
With Sheets("CallList")
    For h = 35 To 2 Step -1
        If .Cells(h, 2).Value <> "" Then
        codeNumber = .Cells(h, 2).Value
            For i = 650 To 3 Step -1
                If .Cells(i, 3).Value = personName Then
                    For j = 30 To 5 Step -1
                        If .Cells(j, 2).Value = codeNumber Then
                            Set isect = Application.Intersect(personName, codeNumber)
                                isect.Value ="X"
                        
                        End If
                    Next j
                End If
            Next i
        End If
      Next h

End With
        
End Sub

I am not sure how to use the intersection method here... probably other problems also. Can anyone help with this?

 

[brian.wethington]

The most important thing to remember is that intersect requires a range not a value. personName and codeNumber are values. If you used a for Each Ce Range..... then you could find the range of the codenumber with that and use intersect.


I am not sure how your data is set up but there may be some better ways to do this than intersections. Could you post some sample data?

 

[cpra3617]

It's just a very simple spreadsheet. List of names in column C, list of codes in row 2, columns E thru AF. User enters name in B1 and codes in B2-B40. So the intersection point between the first Name in column C and the first code would be cell E3, first Name and second code would be F3. Second name and first code would be E4 etc.

So for example, user enters "John" in cell B1, "239" in cell B2 and "240" in cell B3.
I want to place an "X" in the cell where "John" and "239" intersect and an "X" in the cell where "John" and "240" intersect.

 

[Boller]

Why do you have only 32 Job Code Columns (E:AF) but 39 possible Job Codes in column B (B2:B40) ?

Also, in your first post you mentioned B2(name) and B3:B40(codes); in your second post B1(name) and B2:B40(codes).

Dim rRng As Range, cRng As Range, rw#, cell As Range
[E3:AF65536].ClearContents 'You may not want this line
If Len([B1]) = 0 Then
    MsgBox "There is nothing in B1"
    Exit Sub
End If
If Application.CountA([B2:B40]) = 0 Then
    MsgBox "No job codes have been entered"
    Exit Sub
End If
Set rRng = Columns(3).Find([B1])
If Not rRng Is Nothing Then
    rw = rRng.Row
Else
    MsgBox "The name in B1 is not in column C"
    Exit Sub
End If
For Each cell In Range([B2], [B65536].End(xlUp))
    If Len(cell) <> 0 Then
        Set cRng = [E2:AF2].Find(cell)
        If Not cRng Is Nothing Then
            Cells(rw, cRng.Column) = "X"
        End If
    End If
Next

 

[cpra3617]

thanks, that works great!

 

[tusharm]

There's no need for code. As long as the job names are entered in successive cells in column B, the following will work.

In E3 enter =IF(AND($C3=$B$2,ISNUMBER(MATCH(E$2,OFFSET($B$3,0,0,COUNTA($B:$B)-1,1),0))),"X","")
Copy E3 as far across the row as you have job names in row 2 -- AF2 in your example.
Copy E3:{wherever3} (E3:AF3 in your example) as far down as you have names in column C.

Edit: The above assumes you are not using any other cells in B. If you are, change the -1 in the OFFSET formula appropriately. For example if there is a header in B1, change the -1 to -2.

I have a list of names in column C and a list of job codes in row 2, column E thru AF. The user enters the name of the person they are considering into cell B2 and then the job codes for that person into cells B3 up to B40. I would like a macro that scans the jobs codes entered in column B and, for each one, puts an X in the cell where the column that corresponds to the job code and the row that corresponds to the job name intersect.

So far I have the following:

Sub testJobs()
Application.ScreenUpdating = False
Dim personName As Variant, codeName As Variant, jobName As String, ff As String

Dim i As Integer, j As Integer, h As Integer, lastRow As Long

personName = UCase(Range("B1").Value)
With Sheets("CallList")
    For h = 35 To 2 Step -1
        If .Cells(h, 2).Value <> "" Then
        codeNumber = .Cells(h, 2).Value
            For i = 650 To 3 Step -1
                If .Cells(i, 3).Value = personName Then
                    For j = 30 To 5 Step -1
                        If .Cells(j, 2).Value = codeNumber Then
                            Set isect = Application.Intersect(personName, codeNumber)
                                isect.Value ="X"
                        
                        End If
                    Next j
                End If
            Next i
        End If
      Next h

End With
        
End Sub

I am not sure how to use the intersection method here... probably other problems also. Can anyone help with this?

 

[Boller]

There's no need for code. As long as the job names are entered in successive cells in column B, the following will work.

Unless the OP wants to retain the X's for previously entered names and job codes.