Aircraft Scheduling Problem>> VBA code needs adjustment.

  • Thread starter Thread starter aircraft_model VBA
  • Start date Start date
A

aircraft_model VBA

I have a fairly simple challenge and while writing a macro and I got
stuck....

My challenge:

I want to detect cells with a certain value in a column, and if the
result is true, I want letter "e" to be added as a suffix to the
existing text in another column. For instance, if values in (Aircraft
Code), Column B = CRJ, or EMB, or EMR, then the letter "e", signifying
express should be added as a suffix to the text in column A in the
correspoding row. The goal is to add E - for Express Flights to Airlines
operating CRJ, or, EMB, or EMR aircraft.

For e.g.
Before macro

Column A Column B
CO CRJ

After macro

Column A Column B
COE CRJ

As you can see, I tack on an "E' following the CO, to depict Express
flights.

My solution (doen't work??):

I took a stab at writing some code and Im stuck with just some
structure, which may not make much sense, but if you could help I'd
appreciate it...

Sub ExpressLink()
For Each amount In Range("B:B") 'Substitute your range here
If amount.Value = CRJ Then
amount.Value = 0 '.........instead, how can i say, value in
another cell in Column A on the same row - or something
End If
Next amount
For Each amount In Range("B:B") 'Substitute your range here
If amount.Value = EMB Then
amount.Value = 0 '.........instead, how can i say, value in
another cell in column A on the same row - or something
End If
Next amount
End Sub

Appreciate any help on this. I have a spreadsheet with about 35K rows,
so your VBA contribution will be a Thanksgivin' present!

Thank you in advance
 
I have a fairly simple challenge and while writing a macro and I got
stuck....

My challenge:

I want to detect cells with a certain value in a column, and if the
result is true, I want letter "e" to be added as a suffix to the
existing text in another column. For instance, if values in (Aircraft
Code), Column B = CRJ, or EMB, or EMR, then the letter "e", signifying
express should be added as a suffix to the text in column A in the
correspoding row. The goal is to add E - for Express Flights to Airlines
operating CRJ, or, EMB, or EMR aircraft.

For e.g.
Before macro

Column A Column B
CO CRJ

After macro

Column A Column B
COE CRJ

As you can see, I tack on an "E' following the CO, to depict Express
flights.

My solution (doen't work??):

I took a stab at writing some code and Im stuck with just some
structure, which may not make much sense, but if you could help I'd
appreciate it...

Sub ExpressLink()
For Each amount In Range("B:B") 'Substitute your range here
If amount.Value = CRJ Then
amount.Value = 0 '.........instead, how can i say, value in
another cell in Column A on the same row - or something
End If
Next amount
For Each amount In Range("B:B") 'Substitute your range here
If amount.Value = EMB Then
amount.Value = 0 '.........instead, how can i say, value in
another cell in column A on the same row - or something
End If
Next amount
End Sub

Appreciate any help on this. I have a spreadsheet with about 35K rows,
so your VBA contribution will be a Thanksgivin' present!

Thank you in advance
Click to expand...


Something like this should get you pointed in the right direction:


=======================
For Each c In [A1:A35000]
Select Case c.Offset(0, 1).Text
Case Is = "CRJ", "EMB", "EMR"
c.Value = c.Value & IIf(Right(c.Text, 1) <> "E", "E", "")
End Select
Next c
=====================


--ron
 
I tried it out and it did not work, when I removed the "quotes" off of
the "CRJ", then all cells in the column are replaced by E. Could you
help??

Thanks.
 
I tried it out and it did not work, when I removed the "quotes" off of
the "CRJ", then all cells in the column are replaced by E. Could you
help??

Thanks.
Click to expand...


It works fine here.

Post exactly what you are using for the VBA code (the entire macro, and not
just the snippet I posted), as well as the contents of the worksheet cells in
the relevant worksheet columns.


--ron
 
The code I used:

Sub Express()
For Each c In [J1:J35000]
Select Case c.Offset(0, 1).Text
Case Is = "CRJ", "EMB", "EMR"
c.Value = c.Value & IIf(Right(c.Text, 1) <> "E", "E", "")
End Select
Next c
End Sub


The objective:

In 1 column of my worksheet, (i.e. Column J), I have a list of aircraft
types (B737, A320, CRJ, EMB, EMR, and so on). In another column, (i.e.
Column B), I have the corresponding Airline flying these aircraft (CO,
NW, DL, and so on). For each CRJ, or EMB, or EMR, in Column J, I would
like the corresponding cell in Column B to add letter "e" as a suffix.

Before the macro, lets assume, J2 has text CRJ and B2 has CO. After the
macro is run, J2 would continue to have CRJ as its contents, but B2
should be COE. The E would mean it is not just a continental airlines
flight, but a continental express flight.

Hope I clarified it somewhat at least, Thanks a million.
 
The code I used:

Sub Express()
For Each c In [J1:J35000]
Select Case c.Offset(0, 1).Text
Case Is = "CRJ", "EMB", "EMR"
c.Value = c.Value & IIf(Right(c.Text, 1) <> "E", "E", "")
End Select
Next c
End Sub


The objective:

In 1 column of my worksheet, (i.e. Column J), I have a list of aircraft
types (B737, A320, CRJ, EMB, EMR, and so on). In another column, (i.e.
Column B), I have the corresponding Airline flying these aircraft (CO,
NW, DL, and so on). For each CRJ, or EMB, or EMR, in Column J, I would
like the corresponding cell in Column B to add letter "e" as a suffix.

Before the macro, lets assume, J2 has text CRJ and B2 has CO. After the
macro is run, J2 would continue to have CRJ as its contents, but B2
should be COE. The E would mean it is not just a continental airlines
flight, but a continental express flight.

Hope I clarified it somewhat at least, Thanks a million.
Click to expand...

I have not tested it, (and I'm in post-Thanksgiving meal stupor), but I think
that conceptually you have set up your references incorrectly.

In the above, "c" should refer to the column to which you wish to add the "E".
So you should have a line:

For Each c In [B1:B35000]

c.offset(row, column) should refer to the column that you are testing, so
should refer to column J. B->J is about eight columns, so that reference
should read:

Select Case c.Offset(0, 8).Text


--ron
 
Ron - Just tried it out, and yes it works. I'll test it out and if I
have trouble, I'll post again. Thank you very much, and a Happy
thanksgiving to you and your loved ones.

Thanks.
 
Ron - Just tried it out, and yes it works. I'll test it out and if I
have trouble, I'll post again. Thank you very much, and a Happy
thanksgiving to you and your loved ones.

Thanks.
Click to expand...

And the same back out you. Thanks for the feedback. Glad to help.


--ron
 
Back
Top