Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula to macro...anyone??
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. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula to macro...anyone??
On Fri, 28 Nov 2003 19:35:16 -0800, adidas VBA wrote:
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. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! 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 -- auric "underscore" "underscore" "at" hotmail "dot" com ***** My Go amn keyboar oesn't have any 's! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula to macro...anyone??
I will check it out and let you know how it works
Thanks. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula to macro...anyone??
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. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula to macro...anyone??
I am still struggling with this code. Could you please help.
Thanks. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula to macro...anyone??
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? In article , adidas VBA wrote: I am still struggling with this code. Could you please help. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula to macro...anyone??
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. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula to macro...anyone??
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. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula to macro...anyone??
On Sat, 29 Nov 2003 10:49:56 -0800, adidas VBA wrote:
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 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 -- auric "underscore" "underscore" "at" hotmail "dot" com ***** Right now, I gotta watch "Love Connection". -- Jim Gaffigan |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula to macro...anyone??
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. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula to macro...anyone??
On Sun, 30 Nov 2003 07:39:29 -0800, adidas VBA wrote:
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? 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. 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 -- auric "underscore" "underscore" "at" hotmail "dot" com ***** Trust me, would I lie to you... TWICE? |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula to macro...anyone??
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. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula to macro...anyone??
On Wed, 03 Dec 2003 18:35:35 -0800, adidas VBA wrote:
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. 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. 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". 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. 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. -- auric "underscore" "underscore" "at" hotmail "dot" com ***** There is no such thing as bravery; only degrees of fear. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MACRO or FORMULA | Excel Worksheet Functions | |||
Possible Macro or formula | Excel Discussion (Misc queries) | |||
Help with a macro/formula | Excel Worksheet Functions | |||
formula to a macro help PLEASE | Excel Discussion (Misc queries) | |||
Formula / Macro Help | Excel Discussion (Misc queries) |