re: Intersection

cpra3617

Board Regular
Joined
May 24, 2007
Messages
62
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:
Code:
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?
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

brian.wethington

Well-known Member
Joined
Jul 20, 2006
Messages
1,739
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

Board Regular
Joined
May 24, 2007
Messages
62
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

Banned
Joined
Apr 11, 2006
Messages
2,328
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).


Code:
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

Board Regular
Joined
May 24, 2007
Messages
62

ADVERTISEMENT

thanks, that works great!
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,029
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:
Code:
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

Banned
Joined
Apr 11, 2006
Messages
2,328
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,161
Messages
5,768,547
Members
425,481
Latest member
ihumanl

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top