Put the same number in A1 for 160 rows and change..

[KrimerJ]

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

 

[Rick Rothstein]

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?

 

[KrimerJ]

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.

 

[Rick Rothstein]

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

 

[KrimerJ]

Do I substitute "A" with a value (29) for the A-column and "B" for the
H-column that has a value of (ABC) ?

 

[Rick Rothstein]

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.

 

[KrimerJ]

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.

 

[Rick Rothstein]

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

 

[KrimerJ]

It worked, but the macro keeps incrementing by 1. The first 29 + 1, the
second +2, the third +3....etc.

 

[Rick Rothstein]

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.

 

[KrimerJ]

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.