Increasing alphabectically

Windows XP and Excel 2003

The first time through the program should read:

        With Active Sheet
          .First = Sheets("Rates").Range("B2")
          .Second = Sheets("Rates").Range("B3:B186")
        End With

      The next time through the Ranges should read "C2" and "C3:C186"

      Then next time "D2" and "D3:D186" and so on until it reaches "X2"and 
"X3:X186"

      Can someone suggest a good way to do this. Please.




0
HarryGuy
11/27/2009 3:14:55 PM
excel.programming 6508 articles. 2 followers. Follow

4 Replies
954 Views

Similar Articles

[PageSpeed] 4

You could use index values rather than the A1 convention
In the sub below, first the values in B2:E2 are displayed,
then the ranges B3:B5, C3:C5, D3:D5 and E3:E5 are given a colour fill

Sub tryme()
  For J = 2 To 5
   MsgBox Worksheets("Sheet1").Cells(2, J)
   Set myrange = Worksheets("Sheet1").Range(Cells(3, J), Cells(5, J))
   myrange.Interior.ColorIndex = J + 5
  Next J
End Sub


hope this helps
-- 
Bernard Liengme
http://people.stfx.ca/bliengme
Microsoft Excel MVP

"HarryGuy" <jimsimpson@cox.net> wrote in message 
news:Q1SPm.1948$y%5.1361@newsfe03.iad...
> Windows XP and Excel 2003
>
> The first time through the program should read:
>
>        With Active Sheet
>          .First = Sheets("Rates").Range("B2")
>          .Second = Sheets("Rates").Range("B3:B186")
>        End With
>
>      The next time through the Ranges should read "C2" and "C3:C186"
>
>      Then next time "D2" and "D3:D186" and so on until it reaches "X2"and 
> "X3:X186"
>
>      Can someone suggest a good way to do this. Please.
>
>
>
> 
0
Bernard
11/27/2009 3:45:49 PM
I'll give you what you need to answer your question, however some of your 
syntax doesn't make sense.  If you  have other questions, come back.

Option Explicit

Sub test()

Dim myRange As Excel.Range
Dim r As Excel.Range

Dim myWS As Excel.Worksheet

Set myWS = Worksheets("Rates")
Set myRange = myWS.Range("B2:X2")

For Each r In myRange
   Debug.Print r.Address
   Debug.Print r.Offset(1, 0).Resize(184, 1).Address
   'fill in what you want to do here.
Next r

End Sub
-- 
HTH,

Barb Reinhardt



"HarryGuy" wrote:

> Windows XP and Excel 2003
> 
> The first time through the program should read:
> 
>         With Active Sheet
>           .First = Sheets("Rates").Range("B2")
>           .Second = Sheets("Rates").Range("B3:B186")
>         End With
> 
>       The next time through the Ranges should read "C2" and "C3:C186"
> 
>       Then next time "D2" and "D3:D186" and so on until it reaches "X2"and 
> "X3:X186"
> 
>       Can someone suggest a good way to do this. Please.
> 
> 
> 
> 
> .
> 
0
Utf
11/27/2009 4:11:01 PM
Hi,

you could get into a mess using second ,it's a reserved word and with out 
seeing your code it's hard to be much help but maybe this

For col = 2 To 25
first = Sheets("Rates").Cells(2, col)
sec = Range(Cells(3, col), Cells(186, col))
next

Mike

"HarryGuy" wrote:

> Windows XP and Excel 2003
> 
> The first time through the program should read:
> 
>         With Active Sheet
>           .First = Sheets("Rates").Range("B2")
>           .Second = Sheets("Rates").Range("B3:B186")
>         End With
> 
>       The next time through the Ranges should read "C2" and "C3:C186"
> 
>       Then next time "D2" and "D3:D186" and so on until it reaches "X2"and 
> "X3:X186"
> 
>       Can someone suggest a good way to do this. Please.
> 
> 
> 
> 
> .
> 
0
Utf
11/27/2009 4:28:01 PM
Thanks very much to all who responded - very much appreciated.

"Barb Reinhardt" <BarbReinhardt@discussions.microsoft.com> wrote in message 
news:EA44077B-0885-4531-B8EB-312693B909C2@microsoft.com...
> I'll give you what you need to answer your question, however some of your
> syntax doesn't make sense.  If you  have other questions, come back.
>
> Option Explicit
>
> Sub test()
>
> Dim myRange As Excel.Range
> Dim r As Excel.Range
>
> Dim myWS As Excel.Worksheet
>
> Set myWS = Worksheets("Rates")
> Set myRange = myWS.Range("B2:X2")
>
> For Each r In myRange
>   Debug.Print r.Address
>   Debug.Print r.Offset(1, 0).Resize(184, 1).Address
>   'fill in what you want to do here.
> Next r
>
> End Sub
> -- 
> HTH,
>
> Barb Reinhardt
>
>
>
> "HarryGuy" wrote:
>
>> Windows XP and Excel 2003
>>
>> The first time through the program should read:
>>
>>         With Active Sheet
>>           .First = Sheets("Rates").Range("B2")
>>           .Second = Sheets("Rates").Range("B3:B186")
>>         End With
>>
>>       The next time through the Ranges should read "C2" and "C3:C186"
>>
>>       Then next time "D2" and "D3:D186" and so on until it reaches 
>> "X2"and
>> "X3:X186"
>>
>>       Can someone suggest a good way to do this. Please.
>>
>>
>>
>>
>> .
>> 


0
HarryGuy
11/28/2009 2:53:21 PM
Reply:

Similar Artilces:

Increase size of pie chart within control?
Is there a way to increase the size of a pie chart within its control? I have a limited amount of space in which to display the chart, so the overall size of the control is restricted. But within it there's quite a lot of "wasted" white space and I would like to make the pie itself larger and more legible. Can do, or not? Many thanks CW In design mode, double-click on the chart within the control to select it for editing. Then use the sizing handles to resize the chart area to the size of the control; this does not have to be square. Click on the plot area to select it ...

Drivers for percentage increase
I am trying to set up a driver to include in my budget profile so that my managers can select the percentage from a driver and see on each budget line what the outcome will be i.e staff costs may be calculated at 150,000 but if I used my driver to calculate an increase of 3.5% that would change the staff costs to 155,250. Can anyone help? Regards Jo I am not sure what you question is but this may help:- In cell A 2 I have 150,000 In cell B 2 I have 3.5% In cell C 2 I have:- =(A2*B2)+A2 - and this returns 155,250 to cell C 2. If my comments have helped please h...

to increase quantity
hello everybody, i have a question: i want to use project to calculate the delivery time of a machine component. I know the bill of material and the time of every step (manufacturing, mounting, etc.). I don't have problem to create a gantt for this item: but i have problem when i want to create a gantt for a number greater than one of my item. I think that i have to use a function that increase the duration of each step of the process, but i don't know how. Please help me. thank you -- sentenza ------------------------------------------------------------------------ s...

Date Increase
I currently have a form for entering current passwords for systems. I have a field to enter the date the password was last changed, and another field for the date the password expires, which is every 30 days. Is there anyway i can set the date password expires field to update automatically? The 2nd field is just calculated from the 1st and better left as a calculation in a query rather than a field in a table. There are no triggers at the table level in Access. Dan Wood wrote: >I currently have a form for entering current passwords for systems. I have a >field to enter...

can I increase excel column numbers to more than 256?
I am trying to analyse tables including more than 256 variables. I cannot transpose them into rows because the program i am using in association with excel (xlstat) takes the columns as the variables. Is there a way to increase the number of the columns?? or is it a fixed specification of the excel worksheet? thank for any help! Yes, it is fixed - you cannot increase the number of columns. You can, of course, split your data up into chunks of 250 columns - the first block of data on row 1, for example, then the next set starting on another row etc. Pete ...

Increasing alphabectically
Windows XP and Excel 2003 The first time through the program should read: With Active Sheet .First = Sheets("Rates").Range("B2") .Second = Sheets("Rates").Range("B3:B186") End With The next time through the Ranges should read "C2" and "C3:C186" Then next time "D2" and "D3:D186" and so on until it reaches "X2"and "X3:X186" Can someone suggest a good way to do this. Please. You could use index values rather than th...

Increasing Months
Hi Here is a simple question and I cannot find the answer. On a report I have a text box [Text301] and when display I see Fev-07 I would like to add something like 35 other text box so month would increase by 1 What could be the formula for [Text302] to display Mar-07 or Mar 2007. The formula should be issued from [Text301] Then [Text303] related to [Text302], [Text304] related to [Text303], ....... So the day I change [Text301] for let say Apr-08 [Text302] would give me May-08 [Text303} would be Jun-08, etc... I dont realy care with the format; it can be Mar 08 or Mar-08 or Mar-2...

increasing arguments
I'm performing a simple addition calculation for material that is neste within other data, therefore i am unable to simpley sum the entir column........ Currently its telling me i have too many arguments......... =SUM(N146,N142,N138,N134,N130,N126,N122,N118,N114,N110,N106,N102,N98,N94,N90,N86,N82,N78,N74,N70,N66,N62,N58,N54,N50,N46,N42,N38,N34,N30,N26,N22) that is a total of 32 cells notated.......how can i increase th argument level from 30 to 32 or 33 ? Your assistance is greatl appreciated ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.c...

Increasing field size in detail inquiry
I would like to increase the size of one of the fields in the account area of detail inquiry. Right now the W in account number W-123-4567-33-us looks like a V. Any help is appreciated. Thanks. -- Dave Christman System Developer Dave, You might have done this already but maybe not. Did you go to the Account Format Setup window and for your first account segment change the Display Width from "standard" to "Expansion 3"? That should do the trick for you. patrick developer support -- This posting is provided "AS IS" with no warranties, and confers no righ...

Using CSocket with CSocketFile Increases memory usage
Hello All, I am using CSocket derived class with CSocketFile. After I run the application for 6 hours the memory increases in Task Manager by 30000K. The messages sent per second is 30.Has anybody seen such a issue. Regards Partha No, but there are so many problems with CSocket and CSocketFile that it is probably a Really Bad Idea to have used them at all, for any purpose. It sounds like you are not successfully closing some resource. But you would be a lot better off getting rid of them entirely. CSocket is known to be buggy, for example. joe On Sun, 6 Apr 2008 17:30:47 -0700 (PDT),...

Increasing font size in replies
What would cause font sizes to increase each time an email is replied to? For example A sends email to B B replys to A and so on. The first email is now a huge font, the reply a little smaller and so on until the current email reached where it is a normal size. An email printed should only take 4-5 pages now takes about 30! RD Does this happen when a message travels over a Notes connector? >-----Original Message----- >What would cause font sizes to increase each time an email is replied to? > > For example A sends email to B B replys to A and so on. The first email is >...

Increase Potential
ACTING FOR THE NON ACTOR WITH AWARD WINNING ACTOR/WRITER/DIRECTOR/TEACHER MICHAEL COLE DINELLI www.gravitywavefilms.com Employ the tools of the Actor to enhance your communication creative skills in business, relationships, and everyday life. Learn the exercises and acting techniques the professionals use for their work in movies and stage. These classes are designed for the non-pro to be informative, great fun, and bring out the best in you weather you are a salesman, attorney, teacher, or just looking for something fun and therapeutic. Conquer your fear of public speaking and ...

Percentage increase in excel
Hi, I have a column showing profit over one year and i would like to calculate the percentage increase each month. For example my columns might be as follows. Jan - �3000 Feb - �4500 Mar - �7000 Apr - �9000 etc. So i need to calculate the percentage increase from Jan to Feb and so on. Does anybody know the formula? Thanks -- fibregrid ------------------------------------------------------------------------ fibregrid's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25707 View this thread: http://www.excelforum.com/showthread.php?threadid=401911 fibregri...

Increase Max Value of Spinner
The Maximum Value of the "Spinner" form is 30,000. Is there any way to increase it? I'd like to bump the maximum value up into the millions. You can use the spinner button from the control toolbox instead -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "It is a good thing to follow the first law of holes; if you are in one stop digging." Lord Healey "Lon" <sobel@entertainmentlawreporter.com> wrote in message news:sdw0g.3416$oQ2.301@trnddc05... > The Maximum Value of the "Spinner"...

Increase Report Options in Report Group Window
Currently under the Reports - Financial - Groups window there are only 32 reports in the Report Options box. We have more than 32 reports set up, we would like to see all of the reports in order to put them into a group. We only need to add 10-15 reports into the group, but we need to see all of the reports we have set up. We would utilize the Group Reporting option more if we were able to see all of our reports. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I A...

Increase of spam
Hello All, For quite a while I have managed to keep a lid on spam where that my peak of spam would be approximately 2 or 3 per day. There would be the rarity that I would receive 5 of them. Part of this is that I ensure to all my friends never to send me those "joke" e-mails where they automatically forward them to everyone that is in their address book into their "To:" box. This position has been for a number of years. You might can say that I am rather "authoritive" about this procedure. Putting all this aside, Microsoft Outlook 2007 has done a great...

Increase in traffic
Hello A friend has exchange 2000 installed on a Windows 2000 server. All of a sudden there is a huge increase in network traffic and 50% of the users could access emails and the other 50% could not. Then after a while the roles were reversed, and the other 50% could access emails and the remaining 50% could not!!! Apparently there is 1GB of memory installed. I wondered if they had been hit with a virus. Last week they had a few problems with exchange as they no one could access emails. Temporarily the Mcafee Groupshield anitvirus had to be disabled to rectify the issues. The queues ha...

Want to Increase Prices by Percent
New to this. Am using Office-Excel 2000. Have entered three columns of prices for company (List, Dealer, Distributor). Prices will increase next month. How do I increase all rows (example C3-C50) by 10 percent? Will need to do this for all three columns. -- ShirleyF one way 1.. In an empty cell, enter the number that you want to multiply by..... 1.10 2.. Select that cell, and on the Edit menu, click Copy. 3.. Select the whole range of numbers that you want to multiply. 4.. On the Edit menu, click Paste Special. 5.. Under Operation, click Multiply. 6.. Click OK. 7.. Delete th...

Is there a way to increase the storage space for Rules?
We have run into a problem with Exchange 2003 where it keeps giving a user a message that "One or more rules could not be uploaded to Exchange Server and have been deactivated." The user states that if he deletes a rule then checks this rule it works. So it doesn't appear to be a problem with the rule and he wants to increase the storage of the rules for him. How or can we even do that? Thanks, Jeff It is a hard coded limit... "Jeff" <jdiaz@p1fs.com> wrote in message news:bjW4e.31868$Pc.23177@tornado.tampabay.rr.com... > We have run into a proble...

How can I increase the character capcity of an Excel cell?
The current maximum is about 1,100. Excel can hold 32k characters of text and around 1000 in a formula. You can't change this fact of life. -- Jim "Steve" <Steve@discussions.microsoft.com> wrote in message news:442F16AB-1C7A-45CD-9A6F-E2C8282A2CDC@microsoft.com... | The current maximum is about 1,100. You can increase the ability of the cell to *display* more characters (cell can *contain* approx. 32,000) by strategically inserting forced line breaks (<Alt> <Enter>) within the text. -- HTH, RD ============================================== Please kee...

Problems with increasing
I have problem with increasing. In one row I have textual vaule like "F10" and when I want to copy it in the next cell by draging "fill handle" it increments and the result is F11, F12... Haw could I turn off incrementing in that row but it is usefull in others? -- mpele ------------------------------------------------------------------------ mpele's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=31808 View this thread: http://www.excelforum.com/showthread.php?threadid=515304 Hold CTRL while you drag -- Kind regards, Niek Otten "...

number increasing
Is there a way in a master worksheet to put a formula in a cell which contains a number such as 15846 and have that number increase by one in the same cell when you make a copy of the original worksheet. Not as you ask it........but if you would describe your end desire in a little more detail, perhaps someone can suggest another method.... Vaya con Dios, Chuck, CABGx3 "TechnoGram" wrote: > Is there a way in a master worksheet to put a formula in a cell which > contains a number such as 15846 and have that number increase by one > in the same cell when you make a cop...

How do I increase a value if...
Hi, How can I set up a sheet when the value is greater than 1.5 the product will shown as 0.01 and every 1.5 it increases the 0.01. for example, if a1=1.5 then a1=0.01, if a1=3 then a1=0.02, if a1=4.5 then a1=0.03 and so on. Thx -- augustus ------------------------------------------------------------------------ augustus's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=36352 View this thread: http://www.excelforum.com/showthread.php?threadid=561364 You could try a lookup. Create a table in C1 to D3 =VLOOKUP(A1,$C$1:$D$3,2) or a i statement =IF(A5=1.5,...

how do I increase the font size of incoming messages in outloook
My eyes are getting bad and I want to sit back from my computer. I want the font size of messages sent to me in outlook to automatically be converted to 14 point text. Is this possible? No, but you can Zoom the view. jckc wrote: > My eyes are getting bad and I want to sit back from my computer. I want the > font size of messages sent to me in outlook to automatically be converted to > 14 point text. Is this possible? jckc wrote: > My eyes are getting bad and I want to sit back from my computer. I want the > font size of messages sent to me in outloo...

Increase Font Size Permanently ! - Preview Pane
There does not seem to be any way to permanently alter the font size display in the preview pane of Outlook 2007. At best the scroll wheel resizes it temporarily. Is there any way to do this? ...