x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 315

Need macro to fill cells below NUMBER with that number....

http://www.experts-exchange.com/Microsoft/Applications/Microsoft_Office/Excel/Q_26812809.html#a34865488

I need a macro that will FILL the blanks BELOW each NUMBER with this same number.

So if the first number on row 10 is:  276065406

I need for every BLANK ROW cell below that to have this same number. Then, when a new number appears, I need the same thing...fill the blank cells below that with this number,..and so on..and so on.....

THANKS

CRXIuser2005-Datafile-TOFILL.xlsm
0
MIKE
• 6
• 6
1 Solution

Software Solutions ConsultantAuthor Commented:
No need to working about the blanks in rows 1-9....
0

Commented:
Here is the Excel File updated with same numbers in the respective blank cells.

Sid

Code used

``````Dim i As Long, j As Long
Sub Sample()
Dim lastrow As Long

lastrow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

For j = 1 To lastrow
On Error Resume Next
If OnlyNumbers(Sheets("Sheet1").Range("A" & j).Value) = False Then _
Sheets("Sheet1").Range("A" & j).ClearContents

On Error GoTo 0
Next

For j = lastrow To 1 Step -1
If IsNumeric(Sheets("Sheet1").Range("A" & j)) And _
Len(Trim(Sheets("Sheet1").Range("A" & j + 1).Value)) = 0 Then
Sheets("Sheet1").Range("A" & j + 1) = Sheets("Sheet1").Range("A" & j)
End If
Next
End Sub

Function OnlyNumbers(strInput As String) As Boolean
Dim strChar As String, strOutput As String

OnlyNumbers = True
strOutput = ""

For i = 1 To Len(strInput)
strChar = Mid(strInput, i, 1)
If Not (IsNumeric(strChar)) Then
OnlyNumbers = False
Exit Function
End If
Next i
End Function
``````
CRXIuser2005-Datafile-2.xlsm
0

Software Solutions ConsultantAuthor Commented:
You tha man,...but.... Need ALL BLANKS below the number to filled....sorry if I didn't explain enough.

You have the correct concept,..I need ALL blanks to be filled...thanks
0

Software Solutions ConsultantAuthor Commented:
This:
276065406

123456789

Becomes this down the column:
276065406
276065406
276065406
276065406
123456789
123456789
123456789
123456789
123456789
THEN NEXT..ETC..ETC..
0

Commented:
Oh i thought immediate blank...

Just to confitm what will be in

cell A1-A9
Cell A14-A53
This will help me understand the concept

Sid
0

Commented:
The example that you gave above

123456789

The number that comes after 276065406 is 8401258861

So if you refer to the file and compare the cells that I posted in ID: 34865770, what should be the values?

Sid
0

Software Solutions ConsultantAuthor Commented:

cell A1-A9 should be blank
Cell A10 - A11 should have 276065406
Cell A12-A53 should have 8401258861

and so on down the column..
THANKS
0

Software Solutions ConsultantAuthor Commented:
example: 123456789

Was just me being lazy and making-up a number,...above is the real sample
0

Commented:
Ok 5 mins

Sid
0

Commented:
Is this what you want? File updated.

Sid

Code Used

``````Dim i As Long, j As Long
Sub Sample()
Dim lastrow As Long

lastrow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

For j = 1 To lastrow
On Error Resume Next
If OnlyNumbers(Sheets("Sheet1").Range("A" & j).Value) = False Then _
Sheets("Sheet1").Range("A" & j).ClearContents

On Error GoTo 0
Next

For j = 10 To lastrow - 1
If Len(Trim(Sheets("Sheet1").Range("A" & j + 1).Value)) = 0 Then _
Sheets("Sheet1").Range("A" & j + 1).Value = Sheets("Sheet1").Range("A" & j).Value
Next
End Sub

Function OnlyNumbers(strInput As String) As Boolean
Dim strChar As String, strOutput As String

OnlyNumbers = True
strOutput = ""

For i = 1 To Len(strInput)
strChar = Mid(strInput, i, 1)
If Not (IsNumeric(strChar)) Then
OnlyNumbers = False
Exit Function
End If
Next i
End Function
``````
CRXIuser2005-Datafile-2.xlsm
0

Software Solutions ConsultantAuthor Commented:
As far as I can see....this is exactly what I needed....thanks so much for your EXPERTISE!

0

Commented:
You are welcome :)

Sid
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.