Excel 2000 Auto Replace blank cells with Previous 2

[Bumpthis]

Hi

I have a table of data which could increase in size or decrease each time i need it but the ID column ($A:$A) may be missing a value. The problem is that i need to be able to look down this range up to where ever the grand total row cell reference sits and find blank cells and insert the same value as above running a script (preferably) to the cell address of Grand Total. Unfortunately i cannot assume that one week it will be 20 rows and therefore 20 the week after as it could be 30 or 15 etc.
Example
A - - B --- C
Fred Y N

Joe Y Y
Y Y
Kim N N
Kim Y N

Grand Total

Many Thanks in advance

 

[SQLBI]

Hi, this should sort this for you.

Dim x As Integer
Dim y As Integer

x = 1
y = 1

Do Until Cells(x, y).Value = "Grand Total"

If IsEmpty(Cells(x, y)) = True Then

    Cells(x, y).Value = Cells(x - 1, y).Value
    Cells(x, y + 1).Value = Cells(x - 1, y - 1).Value
    Cells(x, y + 2).Value = Cells(x - 1, y - 2).Value

End If

x = x + 1

Loop

Hope this helps.


Leigh Moore
Solutions 4 MS Office Ltd

http://www.solutions4msoffice.co.uk

 

[SQLBI]

Sorry, late on a Friday, brain has gone home already.

Cells(x, y + 1).Value = Cells(x - 1, y - 1).Value
Cells(x, y + 2).Value = Cells(x - 1, y - 2).Value

Should be;

Cells(x, y + 1).Value = Cells(x - 1, y + 1).Value
Cells(x, y + 2).Value = Cells(x - 1, y + 2).Value

Cheers,




Leigh Moore
Solutions 4 MS Office Ltd

http://www.solutions4msoffice.co.uk

 

[klmorgan]

This VB is out of Excel 97 VB book

and does what I think you want: Converts blanks to the contents of the cell above

Range("A1&quot.Select
Selection.CurrentRegion.Select
Selection.SpecialCells(x1CellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"
Selection.CurrentRegion.Select
Selection.Copy
Selection.PasteSpecial Paste:=x1Values, Opertion:=x1None,_
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1&quot.select

Regards

Keith

 

[xlbo]

Or there's KenWright's lovely method (which I'm sure you could record)

Select the column
Edit>Goto>Special>Blanks
Enter =
Hit up arrow
Press CTRL+Return

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[Bumpthis]

Leighmoore, Many thanks for the tips unfortunately i am getting a Compile error: invalid outside procedure when trying to run this script (apologies in advance as i am a beginner when it comes to scripts). Its highlighting x = 1 ???

 

[xlbo]

You need to wrap what Leigh posted in a :

Sub GiveItaName()

'Leigh's code

End sub

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244