You are welcome.
sCell(1, 1) is the cell at the first row and first column respectively of sCell.
Since sCell is one cell then sCell(1, 1) is sCell.
The above is shorthand for sCell.Cells(1, 1)
sCell(1, 0) is one column to the left and sCell(1, -1) is two columns to the left.
(all on the same row)
These callouts all apply to the range they are attached to.
Separately, you should note that the "Offset" method has a base of (0, 0).
So that...
sCell.Offset(1, 1) would be the cell one row down and one row to the right.
'--
You can verify all of this in a blank workbook by using a message box
to display the address of specific cells...
'--
Sub WhereAreYou()
MsgBox ActiveCell.Address & _
vbCr & ActiveCell(1, 1).Address & _
vbCr & ActiveCell.Offset(1, 1).Address
End Sub
'--
Try changing the (1, 1) designations and see what you get.
Jim Cone
Portland, Oregon USA
"Karen53" <(E-Mail Removed)>
wrote in message
news:8CB978C4-214E-478A-8B6C-(E-Mail Removed)...
Thank you Jim! This is perfect!
I have a question, if you don't mind. I would like to understand this so I
may modify it if any columns are added or deleted.
Set rngS = Range("G4", Cells(Rows.Count, 7).End(xlUp))
Set rngT = Range("A4", Cells(Rows.Count, 1).End(xlUp))
'Find the student name and save the teacher name.
For Each sCell In rngS
If sCell.Value = NameToFind Then
strNames = strNames & sCell(1, -5).Value & ","
End If
Next
Here is my confusion. sCell(1, -5). If the students are in column 7 and
the teachers are in column 1, why is it -5? Why isn't it -6? I am assuming
a negative column count from the column containing rngS.
Also, what does the 1 mean, row? Current row, maybe? Ground zero for the
student column?
--
Thanks for your help.
Karen53
"Jim Cone" wrote:
> Try this code on a copy of your sheet (just in case).
> It adds a X to the "H" column for each teacher with Henry in their class.
> (assumes data starts in row 4)
> You can then filter on the H column...
> '--
> Sub LineThemUp()
> 'Jim Cone - Portland, Oregon USA - August 2009
> Dim rngS As Range
> Dim rngT As Range
> Dim sCell As Range
> Dim tCell As Range
> Dim NameToFind As String
> Dim strNames As String
> Dim arrNames As Variant
> Dim vItem As Variant
>
> NameToFind = "Henry"
> Set rngS = Range("G4", Cells(Rows.Count, 7).End(xlUp))
> Set rngT = Range("A4", Cells(Rows.Count, 1).End(xlUp))
>
> 'Find the student name and save the teacher name.
> For Each sCell In rngS
> If sCell.Value = NameToFind Then
> strNames = strNames & sCell(1, -5).Value & ","
> End If
> Next
>
> arrNames = Split(strNames, ",", -1, vbBinaryCompare)
>
> 'Find the saved teachers name and add X to that row.
> For Each tCell In rngT
> For Each vItem In arrNames
> If vItem Like tCell.Value Then
> tCell(1, 8).Value = "X"
> Exit For
> End If
> Next
> Next
> End Sub
> --
> Jim Cone
> Portland, Oregon USA
>
>
>
>
> "Karen53" <(E-Mail Removed)>
> wrote in message
> news:705C48CC-E73D-4746-BD84-(E-Mail Removed)...
> Hi,
> I don't know as though this is possible but I thought I would give it a go.
>
> Column A Cols B:F Column G
> Teacher Student
>
> Column A will have the same Teacher listed for multiple students in Column G.
> Is it possible to filter to all of the Teachers that have a specific student
> but showing all students in the class as well?
>
> For example filtering to Henry:
>
> Col A Cols B:F Col G
> Nancy Jones Whatever George
> Nancy Jones Henry
> Nancy Jones Mary
> Nancy Jones Susan
> John Henry Albert
> John Henry Lewis
> John Henry Mary
> John Henry Henry
> etc.
>
> The way I think of doing it would be time consuming and would involve
> uploading the data to another sheet. For example, search for the matching
> student. Once a match is found, load the teacher into an array. Then copy
> all rows for all of those teachers onto a sheet.
> Is there a way of just filtering the existing sheet? I need to be able to
> edit data in this filter so another sheet won't do it.
> --
> Thanks for your help.
> Karen53
>
|
Similar Threads
