Improve company productivity with a Business Account.Sign Up

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

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

Related to link:
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
Asked:
MIKE
  • 6
  • 6
1 Solution
 
MIKESoftware Solutions ConsultantAuthor Commented:
No need to working about the blanks in rows 1-9....
0
 
SiddharthRoutCommented:
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

Open in new window

CRXIuser2005-Datafile-2.xlsm
0
 
MIKESoftware 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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
MIKESoftware 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
 
SiddharthRoutCommented:
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
 
SiddharthRoutCommented:
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
 
MIKESoftware 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
 
MIKESoftware Solutions ConsultantAuthor Commented:
example: 123456789

Was just me being lazy and making-up a number,...above is the real sample
0
 
SiddharthRoutCommented:
Ok 5 mins

Sid
0
 
SiddharthRoutCommented:
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

Open in new window

CRXIuser2005-Datafile-2.xlsm
0
 
MIKESoftware Solutions ConsultantAuthor Commented:
As far as I can see....this is exactly what I needed....thanks so much for your EXPERTISE!

0
 
SiddharthRoutCommented:
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.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now