UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

> Filling in blank cells - I just can't do it    
 
   
Berty
post Apr 1 2008, 10:51 AM
Post #1

UtterAccess Guru
Posts: 633
From: Gloucester UK
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)
Attached File(s)
Attached File  Demo of required result.zip ( 5.49K ) Number of downloads: 2
 
Go to the top of the page
 
+
 
 Start new topic
Replies (1 - 5)
KingMartin
post Apr 1 2008, 11:18 AM
Post #2

Retired Moderator
Posts: 10,959
From: Prague,CZ / Kiev,UA
Hi John,

there's a popular trick for this:

Fill values into cells from above

In your case, the formula might be =A2

Martin
Go to the top of the page
 
+
KingMartin
post Apr 1 2008, 11:20 AM
Post #3

Retired Moderator
Posts: 10,959
From: Prague,CZ / Kiev,UA
VBA approach is also quite straightforward:

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

Martin
Go to the top of the page
 
+
Berty
post Apr 1 2008, 11:56 AM
Post #4

UtterAccess Guru
Posts: 633
From: Gloucester UK
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
Go to the top of the page
 
+
KingMartin
post Apr 1 2008, 01:50 PM
Post #5

Retired Moderator
Posts: 10,959
From: Prague,CZ / Kiev,UA
John, try the following:

CODE
With Range("A1:A" & [b65536].End(3).Row)
        .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
        .Value = .Value
End With
Go to the top of the page
 
+
Berty
post Apr 1 2008, 01:59 PM
Post #6

UtterAccess Guru
Posts: 633
From: Gloucester UK
KingMartin

Many thanks - again

Cheers - I lie prostrate afore ye
Go to the top of the page
 
+
« Next Oldest · Microsoft Excel · Next Newest »
 

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 

Display Mode: Switch to: Standard · Linear+ · Switch to: Outline

Track this topic · Email this topic · Print this topic · Subscribe to this forum

RSS Go to Top  ·  Lo-Fi Version Time is now: 19th May 2013 - 08:28 PM