Modify code from one macro to another...any takers?

[coventry england]

Help. How can this code be modified....


----------------------------------------------
Sub sched()
Dim a1 As Boolean, a2 As Boolean, n As Long

For n = 2 To 35001
a1 = False
a2 = False

If 20030905 >= Cells(n, 7).Value Then
If 20030905 >= Cells(n, 8).Value Then
If Mid(Cells(n, 9).Value, 5, 1) = "Y" Then a1 = True
End If
End If

If (Cells(n, 1).Value = "DTW") Or _
(Cells(n, 1).Value = "MEM") Or _
(Cells(n, 1).Value = "MSP") Or _
(Cells(n, 3).Value = "DTW") Or _
(Cells(n, 3).Value = "MEM") Or _
(Cells(n, 3).Value = "MSP") _
Then
If Not (Cells(n, 5).Value = "CO") Then a2 = True
Else
If Not (Cells(n, 5).Value = "NW") Then a2 = True
End If

If (a1 And a2) Then
Cells(n, 11).Value = 1
Else
Cells(n, 11).Value = 0
End If
Next
End Sub

-----------------------------------------------------------

To match this...............

Function schedulecleanup(rowNum As Long) As Integer
If Cells(rowNum, 7).Value > 20030905 Or _
Cells(rowNum, 8).Value < 20030905 Or _
UCase(Mid(Cells(rowNum, 9).Value, 5, 1)) <> "Y" Then
schedulecleanup = 0
Exit Function
End If

Select Case UCase(Cells(rowNum, 5).Value)
Case "NW"
Select Case UCase(Cells(rowNum, 1))
Case "DTW", "MEM", "MSP" 'condition 1
schedulecleanup = 1
Case Else
Select Case UCase(Cells(rowNum, 3))
Case "DTW", "MEM", "MSP" 'condition 1
schedulecleanup = 1
Case Else 'condition 2
schedulecleanup = 0
End Select
End Select
Case "CO"
Select Case UCase(Cells(rowNum, 1))
Case "DTW", "MEM", "MSP" 'condition 3
schedulecleanup = 0
Case Else
Select Case UCase(Cells(rowNum, 3))
Case "DTW", "MEM", "MSP" 'condition 3
schedulecleanup = 0
Case Else 'condition 4
schedulecleanup = 1
End Select
End Select
Case Else 'condition 5
schedulecleanup = 1
End Select
End Function


Sub cleanuptime()
For x = 2 To 30001
Cells(x, 12).Value = schedulecleanup(x)
Next
End Sub

 

[J.E. McGimpsey]

Copy the one and paste over the other?

What would constitute a match in your opinion?

 

[coventry england]

I want to use the macro and not the function. The first set of code is
designed as a function and second is a macro. A macro is what I would
like. So is it possible?

Thanks.

 

[Tom Ogilvy]

Your first set of code is declared as

Sub sched()


your second as

Function schedulecleanup(rowNum As Long) As Integer


Yet below you describe just the opposite, so it is unclear what you want.

 

[coventry england]

Perhaps I was unclear. The one with the sub is what I would like to
modify to do what the function does - that was my original intent.
However, I may have confused you. Basically, I want to use a macro and
not a function, whatever it takes. Hope Im clearer this time. The
fuction is what works well with what I need. The macro (sub) code I
published is useless (it was worthwhile at one point, but no longer).
Appreciate your help.

Thanks.

 

[coventry england]

Id like to clarify again: I want to convert the function to a macro
because the result of the function is acceptable. I want a macro to do
the job that the function performs. Anything to clarify my point. Thanks
a million.

Thanks.

 

[Bill Manville]

Don't see why you need to modify the code that's already there.
If the function does what is required for a single row, just run Sub
cleanuptime which does it for 30000 rows.


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup

 

[coventry england]

Thats the point I guess. I dont want to use a formula at all. Just a
macro I can click? Please let me know.

Thanks.

 

[Bill Manville]

Thats the point I guess. I dont want to use a formula at all. Just a
macro I can click? Please let me know.

But you've got one in the code you posted, right after the Function
definition. It's
Sub cleanuptime

I don't see what problem you have in using it?


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup

 

[coventry england]

Bill - When i tried to run the macro, it gave me an error. Thats why I
am confused myself.
Could you help me?
Thanks.

 

[Bill Manville]

When i tried to run the macro, it gave me an error.

#What error did it give you?
On which line was the error?

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup