Solved

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

Posted on 2011-02-10
12
294 Views
Last Modified: 2013-11-05
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
Comment
Question by:MIKE
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
12 Comments
 
LVL 17

Author Comment

by:MIKE
ID: 34865590
No need to working about the blanks in rows 1-9....
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34865712
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
 
LVL 17

Author Comment

by:MIKE
ID: 34865739
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 17

Author Comment

by:MIKE
ID: 34865756
This:
276065406


123456789


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

Expert Comment

by:SiddharthRout
ID: 34865770
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34865803
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
 
LVL 17

Author Comment

by:MIKE
ID: 34865828

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
 
LVL 17

Author Comment

by:MIKE
ID: 34865834
example: 123456789

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

Expert Comment

by:SiddharthRout
ID: 34865848
Ok 5 mins

Sid
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 500 total points
ID: 34865888
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
 
LVL 17

Author Comment

by:MIKE
ID: 34865911
As far as I can see....this is exactly what I needed....thanks so much for your EXPERTISE!

0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34865923
You are welcome :)

Sid
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question