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??
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 |
#6
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! |
#7
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? |
#8
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. |
#9
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! |
#10
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. |
#11
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! |
#12
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! |
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) |