Full Version: Filling in blank cells - I just can't do it
UtterAccess Discussion Forums > Microsoft® Office > Microsoft Excel
Berty
Hi Guys

I have a problem. I have a file which has company names in Col A, and then data in cols B to D.

E.G. A2 Company name is Test Company, Col B to D has relevant data in it.
A3 is for the same company - but is blank - cols B to D have data in it.

The next Col A entry will be for a new company.

What I need is some code that enters the correct company name into the currently empty A cell.

There can sometimes be just one row for a company (in which case I don't have a problem; or there can be several - this changes each month.

I have attached a small excel file with 2 sheets - the first showing some data (as described above) the second sheet - showing what I need to happen.

Your advice / VBA would be fantastic (for info the rows will not exceed 5000) just incase this is useful.


Many thanks


John (scratching furiously at his head)
KingMartin
Hi John,

there's a popular trick for this:

Fill values into cells from above

In your case, the formula might be =A2

Martin
KingMartin
VBA approach is also quite straightforward:

http://www.utteraccess.com/forums/showflat...p;Number=531488

Martin
Berty
Hi KingMartin

Thanks - but I think I'm more confused than you realise.

I've just tried the VBA example:
CODE
Sub AutoCompleteNames()


With Range("A1:A" & [b3000].End(3).Row).SpecialCells(xlCellTypeBlanks)
        .FormulaR1C1 = "=R[-1]C"
        .Value = .Value
End With


End Sub

And it enters the content of A2 into all the blank cells (except a few where it enters #N/A

Any chance of having a look at the small file I attached and see what I need to do


Many thanks for your help

Cheers
KingMartin
John, try the following:

CODE
With Range("A1:A" & [b65536].End(3).Row)
        .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
        .Value = .Value
End With
Berty
KingMartin

Many thanks - again

Cheers - I lie prostrate afore ye
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.