fill in empty cells beneath filled ones

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kurt - 23 Nov 2007 16:27 GMT

Hello,

I'm not a programmer, but often can make things work my own way...
except for now.
At "http://users.telenet.be/coppenskurt/help/sinter.xlsm" you can find
my problemchild (Office2007 with macro). At the opened sheet, I create
some numbers as you can see by clicking the "Test"-button. Where they
come from and wath they mean isn't important, but I just added the
file so you could understand what I'm talking about.
What I'm trying to do now is:
-fill in the empty cells (I colored them yellow so you could know
which ones I mean) with the last number above (in this case always
0.65)
-make the product of the complete row in the first column (this is the
easy part :))

I have tried everything and searched Google for hours but haven't
found anything working :(
Can someone give me a hand?

Thanks in advance!

Kurt

Reply to this Message

Jim Rech - 23 Nov 2007 19:40 GMT

I couldn't come up with anything elegant so I settled for something that
works.

Sub a()
Dim SrcRg As Range, FirstRg As Range
Dim LastRg As Range, FillCell As Range
Dim Counter As Integer
Set SrcRg = Range("B1").CurrentRegion.SpecialCells(xlCellTypeConstants)
Set FirstRg = SrcRg.Areas(1)
Set LastRg = SrcRg.Areas(SrcRg.Areas.Count)
For Counter = 1 To SrcRg.Areas.Count - 1
Set FillCell = SrcRg.Areas(Counter).Cells(2)
FillCell.Resize(LastRg.Row - FillCell.Row + 2).FillDown
Next
End Sub

Signature

Jim

> Hello,
>
[quoted text clipped - 19 lines]
>
> Kurt

Reply to this Message

Kurt - 24 Nov 2007 10:23 GMT

Thanks!
It does work!
But I don't get it why it changes the values in the C-column when you
run it more than once... If you could run it more than once, without
messing up values, I could put it in the original macro so it does
everything with one click.

Reply to this Message

Kurt - 24 Nov 2007 13:49 GMT

> Thanks!
> It does work!
> But I don't get it why it changes the values in the C-column when you
> run it more than once... If you could run it more than once, without
> messing up values, I could put it in the original macro so it does
> everything with one click.

I kept trying and added the following part to my original macro:

i = 3
Do Until Range(Cells(i, 3), Cells(i, 3)) = ""
Range(Cells(i, 2), Cells(i, 2)).Value = Range(Cells(i - 1, 2),
Cells(i - 1, 2)).Value
i = i + 1
Loop
Sheets("grafiek").Select

It now fills in the empty cells when they are firts created!
Thanks for all the help!

Reply to this Message