Formula to macro...anyone??

A

adidas VBA

Can this formula be converted to a vba macro? If so, how??

This formula is copied down 35000 rows in column K of my worksheet

=IF(AND(IF(20030905>=G2,IF(20030905<=H2,IF(MID(I2,5,1)="Y",1,0),0),0)=1,
IF(OR(A2="DTW",A2="MEM",A2="MSP",C2="DTW",C2="MEM",C2="MSP"),IF(E2="CO",
0,1),IF(E2="NW",0,1))=1),1,0)

Thanks.
 
A

Auric__

Can this formula be converted to a vba macro? If so, how??

This formula is copied down 35000 rows in column K of my worksheet

=IF(AND(IF(20030905>=G2,IF(20030905<=H2,IF(MID(I2,5,1)="Y",1,0),0),0)=1,
IF(OR(A2="DTW",A2="MEM",A2="MSP",C2="DTW",C2="MEM",C2="MSP"),IF(E2="CO",
0,1),IF(E2="NW",0,1))=1),1,0)

Thanks.
Click to expand...

First things first: converting this to VBA code will prevent it from
being automatically updated - you will have to run this code *every time
you want to update*. Also, by changing from a formula to VBA, it will go
from "practically instant" to "takes a few minutes". I would recommend
leaving it as a formula.

However, if it simply *must* be VBA:

Sub coffeeBreak()
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
 
A

adidas VBA

Your macro is on the right track, however the I think I can clarify
exactly what I desire from my nested if effort:

Data Table
Column A Col C Col E Col K
ORIGIN DESTIN AIR DESIRED
DFW IAH 8W 1
PHX IAH HP 1
DTW IAH NW 1
MKE IAH NW 0
PIT IAH CO 1
IAH DTW CO 0

Desired Conditions (Col K)
1. Keep (desired value=1) all NW flights with DTW, or MEM, or MSP in
origin/destin.

2. Remove (desired value=0) all other NW flights.

3. Remove (desired value=0) all CO flights with DTW, or MEM, or MSP in
origin/destin.

4. Keep (desired value=1) all other CO flights.

5. Keep (desired value=1) all other airline flights.

We succeed if these conditions are met


Thanks.
 
J

J.E. McGimpsey

Perhaps you could say what you're struggling with - did the post you
replied to help? If not, why not? If so, what still needs to be done?
 
A

adidas VBA

McGimpsey: The macro does not meet the 5 or so conditions is the
problem. THe macro meets some of the conditions and needs to be
evaluated and adjusted to meet all the expectations I outlined in my
previous posts. I need help meeting the conditions so the macro will
work properly. That is what I am struggling with. Hope this helps.

Thanks.
 
A

adidas VBA

McGimpsey -
With this macro the desired result is not met. Please reference my
previous post with a table and a desired result columm.


Sub coffeeBreak()
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



Thanks.
 
A

Auric__

Your macro is on the right track, however the I think I can clarify
exactly what I desire from my nested if effort:

Data Table
Column A Col C Col E Col K
ORIGIN DESTIN AIR DESIRED
DFW IAH 8W 1
PHX IAH HP 1
DTW IAH NW 1
MKE IAH NW 0
PIT IAH CO 1
IAH DTW CO 0

Desired Conditions (Col K)
1. Keep (desired value=1) all NW flights with DTW, or MEM, or MSP in
origin/destin.

2. Remove (desired value=0) all other NW flights.

3. Remove (desired value=0) all CO flights with DTW, or MEM, or MSP in
origin/destin.

4. Keep (desired value=1) all other CO flights.

5. Keep (desired value=1) all other airline flights.

We succeed if these conditions are met
Click to expand...

Okay, having put it that way, it's a lot clearer. This code *should*
work (it reproduced your results with the 6 examples you gave). Just a
little note, all I did was translate the conditions you listed into
actual code. You probably could have done it if you had just sat down
and thought it out. Also, in your original post, the Excel formula was
looking at 2 cells and doing a numeric comparison (apparently dates),
but this particular chunk of code does not do that.

To use this, call it within the cell like any other function, with the
parameter being the row it's on (use the ROW() function so you don't
have to keep track) like this:
=nocoffeebreak4u(ROW())

If you don't want even that, use the sub that follows the function.

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

Sub call4Coffee()
For x = 2 To 30001
Cells(x, 11).Value = noCoffeeBreak4U(x)
Next
End Sub
 
A

adidas VBA

Unfortunately I need the code as a macro and not as a formula, and yes
the date intervals being referenced should also be included. That still
remains the question. Unfortunately the formula code wont cut it. Could
someone please still take a shot at adjusting the macro itself?

Thanks.
 
A

Auric__

Unfortunately I need the code as a macro and not as a formula, and yes
the date intervals being referenced should also be included. That still
remains the question. Unfortunately the formula code wont cut it. Could
someone please still take a shot at adjusting the macro itself?
Click to expand...

Did you notice that last line before the code? Here, I'll quote myself:
If you don't want even that, use the sub that follows the function.
Click to expand...

Did you notice that after the function was a wee little sub? [sigh]
Here's the function... again... updated to consider the dates, and look
for the "Y" in column I. To do the work, run the sub. You owe me a beer
for doing this - you can email it to me. <g>

Function noCoffeeBreak4U(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
noCoffeeBreak4U = 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
noCoffeeBreak4U = 1
Case Else
Select Case UCase(Cells(rowNum, 3))
Case "DTW", "MEM", "MSP" 'condition 1
noCoffeeBreak4U = 1
Case Else 'condition 2
noCoffeeBreak4U = 0
End Select
End Select
Case "CO"
Select Case UCase(Cells(rowNum, 1))
Case "DTW", "MEM", "MSP" 'condition 3
noCoffeeBreak4U = 0
Case Else
Select Case UCase(Cells(rowNum, 3))
Case "DTW", "MEM", "MSP" 'condition 3
noCoffeeBreak4U = 0
Case Else 'condition 4
noCoffeeBreak4U = 1
End Select
End Select
Case Else 'condition 5
noCoffeeBreak4U = 1
End Select
End Function

Sub call4Coffee()
For x = 2 To 30001
Cells(x, 11).Value = noCoffeeBreak4U(x)
Next
End Sub
 
A

adidas VBA

I still cant figure out what the problem is after you sent me the code.
I entered =nocoffeebreak4u(ROW()) in cell M2 on the spreadsheet, and no
matter what I do with the values, I cant get any other result other than
0.

If cells in column G are less than or equal to 20030905, and cells in
column H are greater than or equal to 20030905, then the value should be
1. Once this condition is satisfied, then if the fifth element of the
data array in Column I should be "Y", for the value to be = 1, and once
this is met we implement the previous 5 conditions. I think that the
macro you have says "or" for the condition between column G and H,
should be "and". The previous 5 conditions have been covered correctly
from what I can tell. Could you please assist, and yes many cheers to
you. Email beers to follow. Thanks.

Thanks.
 
A

Auric__

I still cant figure out what the problem is after you sent me the code.
I entered =nocoffeebreak4u(ROW()) in cell M2 on the spreadsheet, and no
matter what I do with the values, I cant get any other result other than
0.
Click to expand...

Where did you paste the code? A module?
If cells in column G are less than or equal to 20030905, and cells in
column H are greater than or equal to 20030905, then the value should be
1.
Click to expand...

Are the dates entered as plain text, or as dates? Meaning, if you select
the cell and look at the formula, does it say "20030905" or does it say
"9/5/2003"? My code is actually set to deal with the value as a number,
which (upon reviewing) *could* be incorrect, but for any correction I
need to know how the data is handled.
Once this condition is satisfied, then if the fifth element of the
data array in Column I should be "Y", for the value to be = 1, and once
this is met we implement the previous 5 conditions. I think that the
macro you have says "or" for the condition between column G and H,
should be "and".
Click to expand...

Look again - I reversed the comparison. Your formula said "if G2 is at
least 20030905, and H2 is no more than 20030905, and the fifth character
of I2 is "Y", then 1, else 0". I reversed that - "if G2 is less than
20030905, or H2 is more than 20030905, or the fifth character of I2 is
not "Y", then 0 (false), else 1 (true)". It is the exact same thing -
"(G2 >= 20030905) = true" is logically equivalent to "(G2 < 20030905) =
false". The OR is there because, while your code is making sure that
everything is *right*, mine makes sure that nothing is *wrong* - catch
the difference?
The previous 5 conditions have been covered correctly
from what I can tell. Could you please assist, and yes many cheers to
you. Email beers to follow. Thanks.
Click to expand...

You're welcome. Before posting again, try playing around with the code
yourself. (I'm serious - I've taken on another job for the holidays and
my free time is currently worth its weight in gold.) The code is
actually pretty close to the original formula you posted.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Disjointed formula/macro code must be mended 2
Modify code from one macro to another...any takers? 10
Excel Logic needs a Doctor !! FORMULA FIX DESIRED!! 7
Question on LOGIC ?? - PLEASE HELP 17
Trying to Drag a Formula but it only Copies the First Cell 2
Volitile Formula 1
#N/A 1
Excel formula to convert decimals to time 2

Top