Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Put the same number in A1 for 160 rows and change..
Put the same number in A1 for 160 rows and change that number to the next highest (ex. from 29 to 30) based on a value in cell G. .......A.............................G. 1.....29.........................."ABC" 2.....29.........................."ABC" 3.................................."ABC" 4.................................."ABC" 160..29.........................."ABC" 161..30.........................."DEF" 162..30.........................."DEF" ...................................."DEF" ...................................."DEF" Need help..just a beginner in Excel VBA -- KrimerJ ------------------------------------------------------------------------ KrimerJ's Profile: http://www.thecodecage.com/forumz/member.php?userid=227 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=84541 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Put the same number in A1 for 160 rows and change..
Is the 160 a fixed number of repeats or can the number of ABC's vary? If
they can vary, are the ABC's (and DEF's and whatever follows them) always group together, one after the other as your limited example shows; or can they be scattered all about)? Also, is Column G always fully filled in, but Column A only has a single value in Row 1? -- Rick (MVP - Excel) "KrimerJ" wrote in message ... Put the same number in A1 for 160 rows and change that number to the next highest (ex. from 29 to 30) based on a value in cell G. ......A.............................G. 1.....29.........................."ABC" 2.....29.........................."ABC" 3.................................."ABC" 4.................................."ABC" 160..29.........................."ABC" 161..30.........................."DEF" 162..30.........................."DEF" ..................................."DEF" ..................................."DEF" Need help..just a beginner in Excel VBA -- KrimerJ ------------------------------------------------------------------------ KrimerJ's Profile: http://www.thecodecage.com/forumz/member.php?userid=227 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=84541 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Put the same number in A1 for 160 rows and change..
Thanks Rick for replying; 160 is fixed number of row repeats and the ABC's, DEF's, GHI's...etc. are constant for the 160 repeats, cell G values allways present, no blank cells, and Column A will have a value of 29 throughout the 160 repeats for the ABC's then 30 for the next 160 repeats for the DEF's then..etc. --- Automerged consecutive post before response --- Missed one point to answer your question(s), column G values are contiguous so 160 ABC's then 160 DEF's then 160 GHI's..etc. -- KrimerJ ------------------------------------------------------------------------ KrimerJ's Profile: http://www.thecodecage.com/forumz/member.php?userid=227 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=84541 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Put the same number in A1 for 160 rows and change..
Try this macro...
Sub NumberSeriesFiller() Dim R As Long Dim Num As Long R = 1 Num = Range("A1").Value Do While Len(Cells(R, "B").Value) Cells(R, "A").Resize(160).Value = Num Num = Num + 1 R = R + 160 Loop End Sub -- Rick (MVP - Excel) "KrimerJ" wrote in message ... Thanks Rick for replying; 160 is fixed number of row repeats and the ABC's, DEF's, GHI's...etc. are constant for the 160 repeats, cell G values allways present, no blank cells, and Column A will have a value of 29 throughout the 160 repeats for the ABC's then 30 for the next 160 repeats for the DEF's then..etc. --- Automerged consecutive post before response --- Missed one point to answer your question(s), column G values are contiguous so 160 ABC's then 160 DEF's then 160 GHI's..etc. -- KrimerJ ------------------------------------------------------------------------ KrimerJ's Profile: http://www.thecodecage.com/forumz/member.php?userid=227 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=84541 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Put the same number in A1 for 160 rows and change..
Do I substitute "A" with a value (29) for the A-column and "B" for the H-column that has a value of (ABC) ? -- KrimerJ ------------------------------------------------------------------------ KrimerJ's Profile: http://www.thecodecage.com/forumz/member.php?userid=227 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=84541 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Put the same number in A1 for 160 rows and change..
Sorry, I missed the Column G reference in your original posting... change
the "B" in my macro to "G" (not sure why you are mentioning Column H in this posting) and leave the "A" alone. Also, I wasn't entirely clear how the first 29 was supposed to get into cell A1. My code assumes you will type 29 in cell A1 first and then run the macro to fill in the rest of Column A. If you have a different method you want to follow, then you need to tell us what it is. -- Rick (MVP - Excel) "KrimerJ" wrote in message ... Do I substitute "A" with a value (29) for the A-column and "B" for the H-column that has a value of (ABC) ? -- KrimerJ ------------------------------------------------------------------------ KrimerJ's Profile: http://www.thecodecage.com/forumz/member.php?userid=227 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=84541 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Put the same number in A1 for 160 rows and change..
Sorry, I meant G column. I only substitued the "B" with a "G" and entered the first "29" in A1, executed the macro, but nothing changed. -- KrimerJ ------------------------------------------------------------------------ KrimerJ's Profile: http://www.thecodecage.com/forumz/member.php?userid=227 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=84541 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Put the same number in A1 for 160 rows and change..
Okay, let's add a reference to the worksheet and see if that helps any. Use
the following macro instead of what I posted earlier and change my Sheet999 reference in the With statement to the actual name of your worksheet (make sure you keep the quote marks)... Sub NumberSeriesFiller() Dim R As Long Dim Num As Long R = 1 With Worksheets("Sheet999") Num = .Range("A1").Value Do While Len(.Cells(R, "G").Value) .Cells(R, "A").Resize(160).Value = Num Num = Num + 1 R = R + 160 Loop End With End Sub -- Rick (MVP - Excel) "KrimerJ" wrote in message ... Sorry, I meant G column. I only substitued the "B" with a "G" and entered the first "29" in A1, executed the macro, but nothing changed. -- KrimerJ ------------------------------------------------------------------------ KrimerJ's Profile: http://www.thecodecage.com/forumz/member.php?userid=227 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=84541 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Put the same number in A1 for 160 rows and change..
It worked, but the macro keeps incrementing by 1. The first 29 + 1, the second +2, the third +3....etc. -- KrimerJ ------------------------------------------------------------------------ KrimerJ's Profile: http://www.thecodecage.com/forumz/member.php?userid=227 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=84541 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Put the same number in A1 for 160 rows and change..
That is not what the tests of my code show... I get 29 placed in the first
160 rows of Column A, then 30 placed in the next 160 rows of Column A, then 31 placed in the next 160 rows of Column A after that, and so on down to the end of the data in Column G. -- Rick (MVP - Excel) "KrimerJ" wrote in message ... It worked, but the macro keeps incrementing by 1. The first 29 + 1, the second +2, the third +3....etc. -- KrimerJ ------------------------------------------------------------------------ KrimerJ's Profile: http://www.thecodecage.com/forumz/member.php?userid=227 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=84541 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Put the same number in A1 for 160 rows and change..
Rick, sorry I was out or the office. My appologies, the marco worked perfectly. Thank you very much. Your macro will help me also to begin learning VBA within Excel. My next task is to automate importing worksheets into MS Access 2003, from those worksheets add selected data to three related tables. -- KrimerJ ------------------------------------------------------------------------ KrimerJ's Profile: http://www.thecodecage.com/forumz/member.php?userid=227 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=84541 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to change the number of rows in a worksheet. | Excel Worksheet Functions | |||
How do I change the rows number? | Excel Worksheet Functions | |||
change the number of rows to a variable | Excel Programming | |||
change the number of rows to a variable | Excel Programming | |||
how do i change the column width over a number of rows in the sam. | Excel Worksheet Functions |