Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Autosum column values, if separate column values equal certain val | Excel Worksheet Functions | |||
Error loop through #N/A values | Excel Programming | |||
Loop that finds blanks, then subtotals values into different column | Excel Programming | |||
Loop through column headers to search from column name and get cell range | Excel Programming | |||
Calculating values to column D with formula based on values column A | Excel Programming |