Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adjusting VBA code for some flexibility
How can I convert this code to say
if value in column E = "CO" then this code Right now it detects CRJ, ER3 and others in Column J and adds lette E to everything in Column 3. I would like some additional control on the code. Help appreciated. Dim mycounter As Double Dim c As Range Dim d As Range mycounter = 1 Do While mycounter < 35001 Set c = Range("J" & mycounter) Select Case c.Text Case Is = "CRJ", "EM2", "ER3", "ER4", "ERD", "ERJ" Set d = Range("E" & mycounter) d.Value = d.Value & IIf(Right(d.Text, 1) < "E", "E", "") End Select mycounter = mycounter + 1 Loop 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
|
|||
|
|||
Adjusting VBA code for some flexibility
Not really sure what you're trying to do but try this as an example:
Sub Macro1() Dim VisibleRange As Range Dim c As Range With Range("A1") .AutoFilter .AutoFilter Field:=5, Criteria1:="CO" Set VisibleRange = _ Intersect(.SpecialCells(xlCellTypeVisible), _ Columns("E"), _ ActiveSheet.UsedRange) End With MsgBox VisibleRange.Address For Each c In VisibleRange MsgBox c.Address Next 'c End Sub It uses AutoFilter to reduce the number of rows to be processed to just those with "CO" in column E. Regards Trevor "adidas VBA" wrote in message ... How can I convert this code to say if value in column E = "CO" then this code Right now it detects CRJ, ER3 and others in Column J and adds lette E to everything in Column 3. I would like some additional control on the code. Help appreciated. Dim mycounter As Double Dim c As Range Dim d As Range mycounter = 1 Do While mycounter < 35001 Set c = Range("J" & mycounter) Select Case c.Text Case Is = "CRJ", "EM2", "ER3", "ER4", "ERD", "ERJ" Set d = Range("E" & mycounter) d.Value = d.Value & IIf(Right(d.Text, 1) < "E", "E", "") End Select mycounter = mycounter + 1 Loop Thanks. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adjusting VBA code for some flexibility
Column E has several airline codes (CO, NW, AA, etc.) and I want only CO
to be impacted by my macro. The macro presently applies to all airlines that operate EMB, CRJ, etc. type aircraft. I want to further narrow the scope of the macro to only CO which is continental airlines. The macro will add an E to all CO flights that operate regional jet aircraft such as EMB, CRJ, others as mentioned in the macro. How can I adjust my original macro to impact only CO and none of the other airlines? 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
|
|||
|
|||
Adjusting VBA code for some flexibility
If I understand you correctly:
Dim rCell As Range For Each rCell In Range("E1:E35000") With rCell If .Value = "CO" Then Select Case .Offset(0, 5).Text Case "CRJ", "EM2", "ER3", "ER4", "ERD", "ERJ" .Value = .Value & _ IIf(Right(.Text, 1) < "E", "E", "") End Select End If End With Next rCell In article , adidas VBA wrote: How can I convert this code to say if value in column E = "CO" then this code Right now it detects CRJ, ER3 and others in Column J and adds lette E to everything in Column 3. I would like some additional control on the code. Help appreciated. Dim mycounter As Double Dim c As Range Dim d As Range mycounter = 1 Do While mycounter < 35001 Set c = Range("J" & mycounter) Select Case c.Text Case Is = "CRJ", "EM2", "ER3", "ER4", "ERD", "ERJ" Set d = Range("E" & mycounter) d.Value = d.Value & IIf(Right(d.Text, 1) < "E", "E", "") End Select mycounter = mycounter + 1 Loop 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
|
|||
|
|||
Adjusting VBA code for some flexibility
I think what you have written should work. Once, I check it out I will
post a response and let you know. Thanks a million. 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
|
|||
|
|||
Adjusting VBA code for some flexibility
Presented below is your code incorporated into my macro, but the
question I now have: Can this entire macro be made more efficient because it takes forever to run? Real slow...needs a boost!! Thanks as always. Sub Rest() Application.ScreenUpdating = False Application.Calculation = xlManual Dim RowNdx As Long For RowNdx = 35000 To 1 Step -1 If Cells(RowNdx, 11).Value = "0" Then Rows(RowNdx).Delete End If Next RowNdx Dim rCell As Range For Each rCell In Range("E1:E35000") With rCell If .Value = "CO" Then Select Case .Offset(0, 5).Text Case "CRJ", "EM2", "ER3", "ER4", "ERD", "ERJ" .Value = .Value & _ IIf(Right(.Text, 1) < "E", "E", "") End Select End If End With Next rCell Application.ScreenUpdating = True Application.Calculation = xlAutomatic End Sub 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
|
|||
|
|||
Adjusting VBA code for some flexibility
My code combined with that from JE should be quicker:
Sub Macro1() Dim VisibleRange As Range Dim rCell As Range With Range("A1") .AutoFilter .AutoFilter Field:=5, Criteria1:="CO" Set VisibleRange = _ Intersect(.SpecialCells(xlCellTypeVisible), _ Columns("E"), _ ActiveSheet.UsedRange) End With For Each rCell In VisibleRange With rCell If .Value = "CO" Then Select Case .Offset(0, 5).Text Case "CRJ", "EM2", "ER3", "ER4", "ERD", "ERJ" .Value = .Value & _ IIf(Right(.Text, 1) < "E", "E", "") End Select End If End With Next rCell End Sub You could do a similar thing to filter the 0 cells and delete the rows. Should also be quicker than looping 35000 times. Regards Trevor "adidas VBA" wrote in message ... Column E has several airline codes (CO, NW, AA, etc.) and I want only CO to be impacted by my macro. The macro presently applies to all airlines that operate EMB, CRJ, etc. type aircraft. I want to further narrow the scope of the macro to only CO which is continental airlines. The macro will add an E to all CO flights that operate regional jet aircraft such as EMB, CRJ, others as mentioned in the macro. How can I adjust my original macro to impact only CO and none of the other airlines? 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
|
|||
|
|||
Adjusting VBA code for some flexibility
Trevor, I will test it out and let you know.
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 | |||
date flexibility | Excel Worksheet Functions | |||
Can you build a formula in one cell with flexibility | Excel Discussion (Misc queries) | |||
Adjusting Macro | Excel Discussion (Misc queries) | |||
Adjusting Referances | Excel Worksheet Functions | |||
Add flexibility to Criteria of DFunctions | Excel Worksheet Functions |