VBA; autofill down to a variable cell, but not overwriting already written numbers.

[torchwood]

Hello

I'm really noobish to everything VBA (as in I can open the macrobox and thats it) so hoping there is someone willing to take some time to help me with a macro.

What I am looking for is a macro in which I would want to:

- Autofill data from the first row (in this case data is from B4-R4) and down to a point dictated by the value in E (E4-E15) (e.g. if the number in E only goes down to row 10 the autofill should fill down to that row)

- If there is anything in the cell it should "replace" the autofill value, so that this new value continues down (e.g. value changes in B3 and this should then continue downwards) as the values will change in some cells along the process.

- Should ignore the P column in this case.

don't know if it has any usefullness but I'm using the 07 suite.

Looking forward for any help, even if it's to guide me to a related post, that could help.

I have attached a screenshot of the sheet, if thats to anyhelp, it doesn't have any content in the cells but might help with the visual.

- Martin

[#2 · Accepted solution]

@torchwood 

I finally found a bit of usefull code, so if anyone want the code here it is.

Sub FillStuff()
On Error Resume Next

'I experience the code sometimes file an error, probably because E is in the marked area. so with this I make the code ignore possible errors and move on to the next line.

Worksheets("Fyldeforløb").Activate

'Fyldeforløb being the sheet I want it done to, with this you can make it autofill multiple sheets, just copy paste the whole piece and change the sheet name.

With Range("C5:R" & Range("E" & Rows.Count).End(xlUp).Row)

'C5 being the first cell in the area I need filled, and R being the last column. E is marking how far down the code will write. Example: I only got numbers in 6 rows of E then the code will only fill down to row E, and if I had 100 rows filled in E it would fill down to that last number.

.SpecialCells(xlBlanks).FormulaR1C1 = "=R[-1]C"

'This being the code that takes all blank cells and fill it with the value above

.Value = .Value

End With

End sub

With this I successfully managed to autofill 4 sheets without a problem.

Also for those as noob as I am; the text I have put in with ' in front will have no effect on code, as it will register as notes (green text in the makro editor. So this should be plug and play.