if then statements, I think
Posted by James Bramble on November 30, 2000 9:13 AM
I need a macro that will look at cells in a column. At the top of the spreadsheet there may not be any data in the cells. however the first cell that does have data in it will then be copied to the next cell down, until it comes to a cell that has new different data. And then this data will need copied to the cells that are below it. and so on I am really at a loss, it has been 3 years since I last looked at code, and I am not familiar with excel's code.
Is this clear enough...If anyone can help me with this or can direct me to a resource that I can help myself I would be incredibly greatful
James
Posted by AB on November 30, 2000 10:44 AM
Just select any cell anywhere in a column with some staggered numbers, run the macro and BAM.
Sub FillerUp()
On Error Resume Next
Set MyCol = ActiveCell.EntireColumn.SpecialCells(xlConstants, xlNumbers)
For Each cell In MyCol
n = n + 1
If n > 1 Then
Set TweenRange = Range(cell.Offset(-1, 0).Address, cell.End(xlUp))
x = cell.End(xlUp).Value
TweenRange.Value = x
End If
Next cell
End Sub
Posted by AB on November 30, 2000 10:58 AM
There's a little bug in the last subroutine that doesn't like it when some of the data is not staggered. Use this modification instead.
Sub FillerUp()
On Error Resume Next
Set MyCol = ActiveCell.EntireColumn.SpecialCells(xlConstants, xlNumbers)
For Each cell In MyCol
n = n + 1
If n > 1 Then
If IsEmpty(cell.Offset(-1, 0).Value) Then
Set TweenRange = Range(cell.Offset(-1, 0).Address, cell.End(xlUp))
x = cell.End(xlUp).Value
TweenRange.Value = x
End If
End If
Next cell
End Sub
Posted by Celia on November 30, 2000 4:03 PM
There is also a simple way to do this without a macro :-
Select the range from the 1st cell with data(let's say A2) to the last cell with data.
Go to Edit>GoTo>Special>Blanks and select OK.
Let's say the first blank cell is A3. Type in the formula =A3 and press Ctrl+Enter - this will fill all the blank cells.
Celia
