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,
here'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
any 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.