Hello,
I am trying to come up some code to search a workbook for numbers and
then put them in a column on the last page but if the number already exists,
do nothing. I need it to do this everytime I run the macro. Any ideas?

here is an example:

look for "day" if true then i want the number in column j on that page to go
to the last page in column k, but if the number is already there then do
nothing. if it is not there then the next cell down.

Does this make any sense? I think I confused myself.

Any help is appreciated

Thank you

A little clarification please...

1. When you say "search the workbook", do you mean search on every worksheet
in the workbook except for the last worksheet? Or did you actually mean to
search on a single worksheet? If only a single worksheet, what is that
worksheet's name?

2. What range on the worksheet(s) is to be searched? A single column or row?
Multiple columns or rows? Which column(s) or row(s) are they?

3. The **only** thing you want placed in Column K on the last worksheet is a
number? Nothing else related to the found text is going to be put on that
worksheet... just a column of numbers???

4. What is the worksheet name of your "last page"?

--
Rick (MVP - Excel)


"AwesomeSean" wrote in message
...
Hello,
I am trying to come up some code to search a workbook for numbers and
then put them in a column on the last page but if the number already
exists,
do nothing. I need it to do this everytime I run the macro. Any ideas?

here is an example:

look for "day" if true then i want the number in column j on that page to
go
to the last page in column k, but if the number is already there then do
nothing. if it is not there then the next cell down.

Does this make any sense? I think I confused myself.

Any help is appreciated

Thank you

Thank you for the reply. Here is a little better explanation (I think)

I have a workbook with 7 pages I want to search for "Rescheduled" in column
D (Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday, Other) If
"Rescheduled" appears in column D, Then look at column C to see if it says
"scheduled or unscheduled" if all this is true then I want the work order
number in column G on that same line to go to 'WTD CHART DATA' page in a box
I made where the upper left corner starts on K140 to L165 (2 column box)

There are 2 columns in the box. K141 says scheduled and L141 says unscheduled.
I want the work order number (from row G on the other pages) to go in the
appropiate column.

Here is the other part.
If there are already work order numbers in there I don't want to keep
duplicating them so if the number is already there then do nothing. If the
work order is not there then go to the next empty cell down.

Did that help or did I make it worse?

Thank you, Thank you, Thank you

Try the following macro. I wasn't entirely clear on which sheets needed to
be processed (you said there were 7 sheets and then listed 8 names), so you
should check the names I used inside the Array function call on the fourth
line of active code where I assign the output from the Array function call
to the SearchSheet variable. Also, I wasn't sure where your "scheduled" and
"unscheduled" headers were on the 'WTD CHART DATA' sheet, so I assumed they
were in K141 and L141 with the data to be listed under them. There are
ranges that would have to be adjusted if that guess was wrong. Let me know
if you need help with that part. Also, the "scheduled" and "unscheduled"
headers are needed as my code needs to find their text when figuring out
where to put the "order number". Anyway, give this macro a try and let me
know how it works out...

Sub ProcessReschedules()
Dim X As Long, C As Range, FirstAddress As String, SearchSheet As Variant
Const SearchWord As String = "Rescheduled"
Const Destination As String = "WTD CHART DATA"
SearchSheet = Array("Monday", "Tuesday", "Wednesday", "Thursday", _
"Friday", "Saturday", "Sunday", "Other")
On Error Resume Next
For X = LBound(SearchSheet) To UBound(SearchSheet)
With Worksheets(SearchSheet(X))
Set C = .Range("D:D").Find(SearchWord, After:=.Cells( _
Rows.Count, "D"), LookIn:=xlValues)
If Not C Is Nothing Then
FirstAddress = C.Address
Do
If LCase(C.Offset(0, -1).Value) = "scheduled" Or LCase( _
C.Offset(0, -1).Value) = "scheduled" Then
Worksheets(Destination).Cells(Worksheets(Destinati on). _
Range("K141:K" & Rows.Count).Find(What:="*", _
SearchOrder:=xlRows, SearchDirection:=xlPrevious, _
LookIn:=xlValues).Row + 1, "K").Value = C.Offset(0, 3).Value
ElseIf LCase(C.Offset(0, -1).Value) = "unscheduled" Then
Worksheets(Destination).Cells(Worksheets(Destinati on). _
Range("L141:L" & Rows.Count).Find(What:="*", _
SearchOrder:=xlRows, SearchDirection:=xlPrevious, _
LookIn:=xlValues).Row + 1, "L").Value = C.Offset(0, 3).Value
End If
Set C = .Range("D:D").Find(SearchWord, After:=C, LookIn:=xlValues)
Loop While Not C Is Nothing And C.Address < FirstAddress
End If
End With
Next
End Sub

--
Rick (MVP - Excel)


"AwesomeSean" wrote in message
...
Thank you for the reply. Here is a little better explanation (I think)

I have a workbook with 7 pages I want to search for "Rescheduled" in
column
D (Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday, Other)
If
"Rescheduled" appears in column D, Then look at column C to see if it says
"scheduled or unscheduled" if all this is true then I want the work order
number in column G on that same line to go to 'WTD CHART DATA' page in a
box
I made where the upper left corner starts on K140 to L165 (2 column box)

There are 2 columns in the box. K141 says scheduled and L141 says
unscheduled.
I want the work order number (from row G on the other pages) to go in the
appropiate column.

Here is the other part.
If there are already work order numbers in there I don't want to keep
duplicating them so if the number is already there then do nothing. If the
work order is not there then go to the next empty cell down.

Did that help or did I make it worse?

Thank you, Thank you, Thank you

Macro does not run at all. I made a seperate module and a button to assign it
this macro and it does nothing.

You were right about the 8 names and the work order numbers needed to start
on k142 and l142 so you were right there. I really appreciate the help and
time you took to write this. Now if I can just figure out how to make it
work. Any ideas.



"Rick Rothstein" wrote:

Try the following macro. I wasn't entirely clear on which sheets needed to
be processed (you said there were 7 sheets and then listed 8 names), so you
should check the names I used inside the Array function call on the fourth
line of active code where I assign the output from the Array function call
to the SearchSheet variable. Also, I wasn't sure where your "scheduled" and
"unscheduled" headers were on the 'WTD CHART DATA' sheet, so I assumed they
were in K141 and L141 with the data to be listed under them. There are
ranges that would have to be adjusted if that guess was wrong. Let me know
if you need help with that part. Also, the "scheduled" and "unscheduled"
headers are needed as my code needs to find their text when figuring out
where to put the "order number". Anyway, give this macro a try and let me
know how it works out...

Sub ProcessReschedules()
Dim X As Long, C As Range, FirstAddress As String, SearchSheet As Variant
Const SearchWord As String = "Rescheduled"
Const Destination As String = "WTD CHART DATA"
SearchSheet = Array("Monday", "Tuesday", "Wednesday", "Thursday", _
"Friday", "Saturday", "Sunday", "Other")
On Error Resume Next
For X = LBound(SearchSheet) To UBound(SearchSheet)
With Worksheets(SearchSheet(X))
Set C = .Range("D:D").Find(SearchWord, After:=.Cells( _
Rows.Count, "D"), LookIn:=xlValues)
If Not C Is Nothing Then
FirstAddress = C.Address
Do
If LCase(C.Offset(0, -1).Value) = "scheduled" Or LCase( _
C.Offset(0, -1).Value) = "scheduled" Then
Worksheets(Destination).Cells(Worksheets(Destinati on). _
Range("K141:K" & Rows.Count).Find(What:="*", _
SearchOrder:=xlRows, SearchDirection:=xlPrevious, _
LookIn:=xlValues).Row + 1, "K").Value = C.Offset(0, 3).Value
ElseIf LCase(C.Offset(0, -1).Value) = "unscheduled" Then
Worksheets(Destination).Cells(Worksheets(Destinati on). _
Range("L141:L" & Rows.Count).Find(What:="*", _
SearchOrder:=xlRows, SearchDirection:=xlPrevious, _
LookIn:=xlValues).Row + 1, "L").Value = C.Offset(0, 3).Value
End If
Set C = .Range("D:D").Find(SearchWord, After:=C, LookIn:=xlValues)
Loop While Not C Is Nothing And C.Address < FirstAddress
End If
End With
Next
End Sub

--
Rick (MVP - Excel)


"AwesomeSean" wrote in message
...
Thank you for the reply. Here is a little better explanation (I think)

I have a workbook with 7 pages I want to search for "Rescheduled" in
column
D (Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday, Other)
If
"Rescheduled" appears in column D, Then look at column C to see if it says
"scheduled or unscheduled" if all this is true then I want the work order
number in column G on that same line to go to 'WTD CHART DATA' page in a
box
I made where the upper left corner starts on K140 to L165 (2 column box)

There are 2 columns in the box. K141 says scheduled and L141 says
unscheduled.
I want the work order number (from row G on the other pages) to go in the
appropiate column.

Here is the other part.
If there are already work order numbers in there I don't want to keep
duplicating them so if the number is already there then do nothing. If the
work order is not there then go to the next empty cell down.

Did that help or did I make it worse?

Thank you, Thank you, Thank you

.

Did you have any ideas?

Thanks again for the help. Looking at the code this should do exactly what I
want. I just need to find out why it does not work at all. It does nothing
with no errors.

Thank you

If an error is occurring, the On Error Resume Next is hiding it. Comment out
this statement and the debugger should then stop on the line giving you your
problem.

Also I noticed a typo in my code; however this typo will not affect the
code's operation... I just perform the same test twice in an If..Then
statement. This line of code...

If LCase(C.Offset(0, -1).Value) = "scheduled" Or LCase( _
C.Offset(0, -1).Value) = "scheduled" Then

looks like it should be this instead...

If LCase(C.Offset(0, -1).Value) = "scheduled" Then

--
Rick (MVP - Excel)


"AwesomeSean" wrote in message
...
Did you have any ideas?

Thanks again for the help. Looking at the code this should do exactly what
I
want. I just need to find out why it does not work at all. It does nothing
with no errors.

Thank you

I deleted the on error line and fixed the other code and still nothing. I ran
it in the VBA side and it just does nothing at all. Any other ideas?

Sean

"Rick Rothstein" wrote:

If an error is occurring, the On Error Resume Next is hiding it. Comment out
this statement and the debugger should then stop on the line giving you your
problem.

Also I noticed a typo in my code; however this typo will not affect the
code's operation... I just perform the same test twice in an If..Then
statement. This line of code...

If LCase(C.Offset(0, -1).Value) = "scheduled" Or LCase( _
C.Offset(0, -1).Value) = "scheduled" Then

looks like it should be this instead...

If LCase(C.Offset(0, -1).Value) = "scheduled" Then

--
Rick (MVP - Excel)


"AwesomeSean" wrote in message
...
Did you have any ideas?

Thanks again for the help. Looking at the code this should do exactly what
I
want. I just need to find out why it does not work at all. It does nothing
with no errors.

Thank you

.

By the way, I am using excel 2003

if that helps




"AwesomeSean" wrote:

Hello,
I am trying to come up some code to search a workbook for numbers and
then put them in a column on the last page but if the number already exists,
do nothing. I need it to do this everytime I run the macro. Any ideas?

here is an example:

look for "day" if true then i want the number in column j on that page to go
to the last page in column k, but if the number is already there then do
nothing. if it is not there then the next cell down.

Does this make any sense? I think I confused myself.

Any help is appreciated

Thank you