Tricky Problem with Sequential Fill Macro

[mattisokayatexcel]

Hi, Smart People.

I've encountered a tricky problem that I thought you might be able to
help with.

I'm creating a macro that needs to sequentially fill down one column,
but only as far down as the column immediately to the right of it has
text.

Here are more details:

-Text will be inserted into Column F, starting at F7 (the number of
cells filled in column F will vary).
-In cell E7, a code that is a combination of two letters and six
numbers will be inserted (e.g., BR000800). The letter prefix will
remain the same each time, but the numbers will be different.

So, if four lines of data are inserted into column B, and the code in
E7 is GS001442, then the macro needs to fill E8 through E10 with
GS001443, GS001444, and GS001445.

Any ideas how I might accomplish this? Thanks so much.

Matt

 

[Joel]

I like doing these type problems with worksheet_change functions. Add code
below as follows:
Go to excel worksheet and right click tab on bottom of sheet (normnally
sheet1).SELECT VIEW CODE. COPY AND PAST SUBROUTINE BELOW

Sub worksheet_change(ByVal Target As Range)

Application.EnableEvents = False
For Each cell In Target

If (cell.Column = 6) And _
(cell.Row >= 7) Then

LastRowE = Cells(cell.Row, "E").End(xlUp).Row
LastVal = Cells(LastRowE, "E")
Prefix = Left(LastVal, 2)
Numsuffix = Val(Mid(LastVal, 3))
For RowCount = (LastRowE + 1) To cell.Row

Numsuffix = Numsuffix + 1
Cells(RowCount, "E") = Prefix + _
Format(Numsuffix, "000000")
Next RowCount
End If

Next cell
Application.EnableEvents = True
End Sub

 

[mattisokayatexcel]

Hi, Joel.

Thanks so much for your help. I love the idea of using a
worksheet_change function.

I tried entering the code you suggested into cell E7. Unfortunately,
when I do, nothing runs. Any idea what might not be happening?

 

[mattisokayatexcel]

Sorry, I should clarify. I added the code you wrote to the worksheet
tab. I entered sample data into cell E7.

 

[Joel]

Shouldn't you be adding data into column F? Add a 6 digit code into cell E7,
then type into column F8 - F12 anything. I types the numbers 1,2,3,4,....
It will also work if yoy type the number 1 into F7, then copy F7 into F8 -
F12.


If it appears not to work, then go to the VBA code page. click the line
"Application.EnableEvents = False" with left mouse button. Then press F9.
the line will turn red which will set a break point. Go back to the
worksheet and type data. The VBA window should come up with the line
highlighted now yellow. You can step through the code by Pressing F8.

 

[mattisokayatexcel]

Perfect. Thanks so much for your help, Joel!