Loop Thru Values in a Column
 

I need to loop thru values in a secific column such as 'AJ'. When I find a
'D', or a 'Q', I want to put the name of the perosn in column A into another
spreadsheet. I find examples of summing values in a column but not locating
cerain values.

Thanks for assistance...
--
Robert Hill

Loop Thru Values in a Column
 

How about:

Sub ordinate()

Set r = Range("AJ:AJ")
Set r2 = Sheets("Sheet2").Range("A1")

LastRow = Cells(Cells.Rows.Count, "AJ").End(xlUp).Row

For i = 1 To LastRow
v = Cells(i, "AJ").Value
If v = "D" Or v = "Q" Then
r2.Value = Cells(i, "A").Value
End If
Next
End Sub

--
Gary's Student
gsnu200705


"Robert" wrote:

I need to loop thru values in a secific column such as 'AJ'. When I find a
'D', or a 'Q', I want to put the name of the perosn in column A into another
spreadsheet. I find examples of summing values in a column but not locating
cerain values.

Thanks for assistance...
--
Robert Hill

Loop Thru Values in a Column
 

I thought I was there but the LastRow is equal to 1 so nothing is found. The
actual data does not start until row 5 and there are blanks.
--
Robert Hill



"Gary''s Student" wrote:

How about:

Sub ordinate()

Set r = Range("AJ:AJ")
Set r2 = Sheets("Sheet2").Range("A1")

LastRow = Cells(Cells.Rows.Count, "AJ").End(xlUp).Row

For i = 1 To LastRow
v = Cells(i, "AJ").Value
If v = "D" Or v = "Q" Then
r2.Value = Cells(i, "A").Value
End If
Next
End Sub

--
Gary's Student
gsnu200705


"Robert" wrote:

I need to loop thru values in a secific column such as 'AJ'. When I find a
'D', or a 'Q', I want to put the name of the perosn in column A into another
spreadsheet. I find examples of summing values in a column but not locating
cerain values.

Thanks for assistance...
--
Robert Hill

Loop Thru Values in a Column
 

Sub GetData()
Dim icol As Long, lastrow As Long
Dim rng As Range, rng1 As Range
Dim rng2 As Range
icol = Cells(1, "IV").End(xlToLeft).Offset(0, 1).Column
lastrow = Cells(Rows.Count, "AJ").End(xlUp).Row
Set rng = Range(Cells(2, icol), Cells(lastrow, icol))
rng.Formula = "=If(or(AJ2=""D"",AJ2=""Q""),na(),false)"
On Error Resume Next
Set rng1 = rng.SpecialCells(xlFormulas, xlErrors)
On Error GoTo 0
If rng1 Is Nothing Then Exit Sub
Set rng2 = Intersect(Columns(1), rng1.EntireRow)
rng2.Copy Destination:=Worksheets("Sheet2").Range("A1")
Columns(icol).Delete
End Sub

this may look a bit more complex, but it should be very fast.

--
Regards,
Tom Ogilvy



"Robert" wrote in message
...
I need to loop thru values in a secific column such as 'AJ'. When I find a
'D', or a 'Q', I want to put the name of the perosn in column A into
another
spreadsheet. I find examples of summing values in a column but not
locating
cerain values.

Thanks for assistance...
--
Robert Hill

Loop Thru Values in a Column
 

Are you certain the column you want searched is AJ??
--
Gary's Student
gsnu200705


"Robert" wrote:

I thought I was there but the LastRow is equal to 1 so nothing is found. The
actual data does not start until row 5 and there are blanks.
--
Robert Hill



"Gary''s Student" wrote:

How about:

Sub ordinate()

Set r = Range("AJ:AJ")
Set r2 = Sheets("Sheet2").Range("A1")

LastRow = Cells(Cells.Rows.Count, "AJ").End(xlUp).Row

For i = 1 To LastRow
v = Cells(i, "AJ").Value
If v = "D" Or v = "Q" Then
r2.Value = Cells(i, "A").Value
End If
Next
End Sub

--
Gary's Student
gsnu200705


"Robert" wrote:

I need to loop thru values in a secific column such as 'AJ'. When I find a
'D', or a 'Q', I want to put the name of the perosn in column A into another
spreadsheet. I find examples of summing values in a column but not locating
cerain values.

Thanks for assistance...
--
Robert Hill

Loop Thru Values in a Column
 

To the Original Poster: Was the sheet with data in columns A and AJ the
active sheet when you ran the macro?

Gary, you might want to advance R2 in your code:

Sub ordinate()
Dim i as long, v as String
Dim r2 as Range, lastrow as Long

Set r2 = Sheets("Sheet2").Range("A1")

LastRow = Cells(Cells.Rows.Count, "AJ").End(xlUp).Row

For i = 5 To LastRow
v = Cells(i, "AJ").Value
If v = "D" Or v = "Q" Then
r2.Value = Cells(i, "A").Value
set r2 = r2(2)
End If
Next
End Sub

--
Regards,
Tom Ogilvy


"Gary''s Student" wrote in message
...
Are you certain the column you want searched is AJ??
--
Gary's Student
gsnu200705


"Robert" wrote:

I thought I was there but the LastRow is equal to 1 so nothing is found.
The
actual data does not start until row 5 and there are blanks.
--
Robert Hill



"Gary''s Student" wrote:

How about:

Sub ordinate()

Set r = Range("AJ:AJ")
Set r2 = Sheets("Sheet2").Range("A1")

LastRow = Cells(Cells.Rows.Count, "AJ").End(xlUp).Row

For i = 1 To LastRow
v = Cells(i, "AJ").Value
If v = "D" Or v = "Q" Then
r2.Value = Cells(i, "A").Value
End If
Next
End Sub

--
Gary's Student
gsnu200705


"Robert" wrote:

I need to loop thru values in a secific column such as 'AJ'. When I
find a
'D', or a 'Q', I want to put the name of the perosn in column A into
another
spreadsheet. I find examples of summing values in a column but not
locating
cerain values.

Thanks for assistance...
--
Robert Hill

Loop Thru Values in a Column
 

I see your point. If there are multiple instances, I am just over-writing
them.
--
Gary''s Student
gsnu200705


"Tom Ogilvy" wrote:

To the Original Poster: Was the sheet with data in columns A and AJ the
active sheet when you ran the macro?

Gary, you might want to advance R2 in your code:

Sub ordinate()
Dim i as long, v as String
Dim r2 as Range, lastrow as Long

Set r2 = Sheets("Sheet2").Range("A1")

LastRow = Cells(Cells.Rows.Count, "AJ").End(xlUp).Row

For i = 5 To LastRow
v = Cells(i, "AJ").Value
If v = "D" Or v = "Q" Then
r2.Value = Cells(i, "A").Value
set r2 = r2(2)
End If
Next
End Sub

--
Regards,
Tom Ogilvy


"Gary''s Student" wrote in message
...
Are you certain the column you want searched is AJ??
--
Gary's Student
gsnu200705


"Robert" wrote:

I thought I was there but the LastRow is equal to 1 so nothing is found.
The
actual data does not start until row 5 and there are blanks.
--
Robert Hill



"Gary''s Student" wrote:

How about:

Sub ordinate()

Set r = Range("AJ:AJ")
Set r2 = Sheets("Sheet2").Range("A1")

LastRow = Cells(Cells.Rows.Count, "AJ").End(xlUp).Row

For i = 1 To LastRow
v = Cells(i, "AJ").Value
If v = "D" Or v = "Q" Then
r2.Value = Cells(i, "A").Value
End If
Next
End Sub

--
Gary's Student
gsnu200705


"Robert" wrote:

I need to loop thru values in a secific column such as 'AJ'. When I
find a
'D', or a 'Q', I want to put the name of the perosn in column A into
another
spreadsheet. I find examples of summing values in a column but not
locating
cerain values.

Thanks for assistance...
--
Robert Hill

Loop Thru Values in a Column
 

The spreadsheet is for O.R. scheduling and it covers a 6 week period (42
days). Column A is for nurses names. Day 1 is column B and the last day is
column AQ. The user will select a day for a report to be run. There are
appoximately 41 nurses on the spreadsheet from A5 to the A44 with some
blanks. Therefor, if the user chooses the day for column AJ, then I need to
list all nurses with a code of 'D' or 'Q' or a variety of other codes that
fall in that column (or day). If I use the code suggested for LastRow, I get
the number 1 and the loop does not get the names. If I change .End(xlUp).row
to .End(xlDown).row the LastRow will be 65536 and the loop will run but I
will have to lop through that number for every codes I need.
Sorry for the length of this but I don't think I explained myself very well
previoulsy.
--
Robert Hill



"Gary''s Student" wrote:

Are you certain the column you want searched is AJ??
--
Gary's Student
gsnu200705


"Robert" wrote:

I thought I was there but the LastRow is equal to 1 so nothing is found. The
actual data does not start until row 5 and there are blanks.
--
Robert Hill



"Gary''s Student" wrote:

How about:

Sub ordinate()

Set r = Range("AJ:AJ")
Set r2 = Sheets("Sheet2").Range("A1")

LastRow = Cells(Cells.Rows.Count, "AJ").End(xlUp).Row

For i = 1 To LastRow
v = Cells(i, "AJ").Value
If v = "D" Or v = "Q" Then
r2.Value = Cells(i, "A").Value
End If
Next
End Sub

--
Gary's Student
gsnu200705


"Robert" wrote:

I need to loop thru values in a secific column such as 'AJ'. When I find a
'D', or a 'Q', I want to put the name of the perosn in column A into another
spreadsheet. I find examples of summing values in a column but not locating
cerain values.

Thanks for assistance...
--
Robert Hill

Loop Thru Values in a Column
 

You would get that behavior if there are no codes in column AJ. If there
are code in AJ, then the code should work with the correction for advancing
R2.

You added explantation has done nothing to explain why you have no codes in
AJ.

This hard codes lastrow at 50

Sub ordinate()
Dim i as long, v as String
Dim r2 as Range, lastrow as Long

Set r2 = Sheets("Sheet2").Range("A1")

LastRow = 50

For i = 5 To LastRow
v = Cells(i, "AJ").Value
If v = "D" Or v = "Q" Then
r2.Value = Cells(i, "A").Value
set r2 = r2(2)
End If
Next
End Sub

but if there are no codes in AJ, then it won't make much difference.

--
Regards,
Tom Ogilvy


"Robert" wrote in message
...
The spreadsheet is for O.R. scheduling and it covers a 6 week period (42
days). Column A is for nurses names. Day 1 is column B and the last day
is
column AQ. The user will select a day for a report to be run. There are
appoximately 41 nurses on the spreadsheet from A5 to the A44 with some
blanks. Therefor, if the user chooses the day for column AJ, then I need
to
list all nurses with a code of 'D' or 'Q' or a variety of other codes that
fall in that column (or day). If I use the code suggested for LastRow, I
get
the number 1 and the loop does not get the names. If I change
.End(xlUp).row
to .End(xlDown).row the LastRow will be 65536 and the loop will run but I
will have to lop through that number for every codes I need.
Sorry for the length of this but I don't think I explained myself very
well
previoulsy.
--
Robert Hill



"Gary''s Student" wrote:

Are you certain the column you want searched is AJ??
--
Gary's Student
gsnu200705


"Robert" wrote:

I thought I was there but the LastRow is equal to 1 so nothing is
found. The
actual data does not start until row 5 and there are blanks.
--
Robert Hill



"Gary''s Student" wrote:

How about:

Sub ordinate()

Set r = Range("AJ:AJ")
Set r2 = Sheets("Sheet2").Range("A1")

LastRow = Cells(Cells.Rows.Count, "AJ").End(xlUp).Row

For i = 1 To LastRow
v = Cells(i, "AJ").Value
If v = "D" Or v = "Q" Then
r2.Value = Cells(i, "A").Value
End If
Next
End Sub

--
Gary's Student
gsnu200705


"Robert" wrote:

I need to loop thru values in a secific column such as 'AJ'. When
I find a
'D', or a 'Q', I want to put the name of the perosn in column A
into another
spreadsheet. I find examples of summing values in a column but not
locating
cerain values.

Thanks for assistance...
--
Robert Hill