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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

On Thu, 27 Nov 2003 10:16:54 -0800, aircraft_model VBA
wrote:

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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!


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.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

On Thu, 27 Nov 2003 11:06:59 -0800, aircraft_model VBA
wrote:

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.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!


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.






*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

On Thu, 27 Nov 2003 13:36:41 -0800, aircraft_model VBA
wrote:

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.


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.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

On Thu, 27 Nov 2003 19:45:24 -0800, aircraft_model VBA
wrote:

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.

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


--ron