Solved

How do you populate a blank cell with the value of the cell found above until another non-blank cell is found.

Posted on 2012-04-10
14
652 Views
Last Modified: 2012-04-15
I receive a daily spreadsheet (see attached - Received tab) where some of the cells have a combination of a column header and the headers value all in once cell.  There are cells that contain the header called "Rep" along with the Sales Rep name all in once cell.  I need to when this situation is found is to fill in the value of the Sales Rep forward until a non-blank cell is found within that column.  

Also I would like to fill in 2 columns with the report date and report time in columns that would be next to "REP", "Invoice#", and "Amt".  Currently the Report Date and Report time is always found at the upper corner of the report.  

See the tab called "Post-Macro" which is what my hopeful output would be...

I know how to loop thru excel, I just don't know how to within a loop take a value from a prior cell and fill down to a non-blank cell.  If you at minimum could reference me to a similar post that would be great as I assume this type of situation must happen all the time.
ee-non-normalized.xlsx
0
Comment
Question by:upobDaPlaya
  • 5
  • 4
  • 3
  • +2
14 Comments
 
LVL 26

Assisted Solution

by:redmondb
redmondb earned 100 total points
ID: 37830794
Hi, upobDaPlaya,

Please see attached. The macro is...
Option Explicit

Sub Daily_Output()
Dim xInput As Worksheet
Dim xOutput As Worksheet
Dim xLast_Row As Long
Dim xTime As Variant, xDate As Variant
Dim xCell As Range
Dim xRep As String
Dim i As Long

Set xInput = Sheets("Received")
xInput.Activate
xLast_Row = ActiveSheet.Range("A1").SpecialCells(xlLastCell).Row

xDate = Mid(Cells(1, 1), 6, 10)
xTime = Mid(Cells(1, 1), 25, 8)

Set xOutput = Sheets.Add
Range("A1:E1") = Array("Date", "Time", "Rep", "Invoice#", "Amt")
i = 1

For Each xCell In xInput.Range("A3:A" & xLast_Row)
    If xCell <> "" Then xRep = Mid(xCell, 5, 9999)
    With xCell
        If .Offset(0, 1) <> "" Then
            If .Offset(0, 1) <> "Invoice#" Then
                i = i + 1
                Cells(i, 1) = xDate
                Cells(i, 2) = xTime
                Cells(i, 3) = xRep
                Cells(i, 4) = .Offset(0, 1)
                Cells(i, 5) = .Offset(0, 2)
            End If
        End If
    End With
Next

End Sub

Open in new window

Regards,
Brian.ee-non-normalized-V2.xlsm
Edit: Date and Time processing corrected.
0
 
LVL 80

Accepted Solution

by:
byundt earned 350 total points
ID: 37830850
If you have a lot of data, it may go a little faster if you work on entire columns at a time, rather than looping through the rows.

Sub Normalizer()
Dim rg As Range
Dim fl As Filter
Application.ScreenUpdating = False
Columns("A:B").Insert Shift:=xlToRight
Set rg = Range("D1").End(xlDown)
Set rg = Range(rg, Cells(Rows.Count, rg.Column).End(xlUp))
Set rg = rg.Offset(0, -3).Resize(, 5)
rg.Columns(1).Value = DateValue(Mid(Range("C1").Value, 6, 10))
rg.Columns(2).Value = TimeValue(Right(Range("C1").Value, 8))
Columns(2).ColumnWidth = 12
rg.Columns(3).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=IF(RC[1]=""Invoice#"",MID(R[-1]C,5,99),R[-1]C)"
rg.Columns(3).Formula = rg.Columns(3).Value
rg.AutoFilter Field:=4, Criteria1:="=Invoice#", Operator:=xlOr, Criteria2:="="
rg.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
rg.AutoFilter
rg.Cells(1, 1).Resize(1, 3).Value = Array("Date", "Time", "Rep")
Range(Range("A1"), rg.Cells(1, 1).Offset(-1)).EntireRow.Delete
End Sub

Open in new window

0
 
LVL 26

Expert Comment

by:redmondb
ID: 37830895
Agreed, Brad. Although I suspect that you're being overly modest - with a big import, your code would be a lot faster!
0
 

Author Comment

by:upobDaPlaya
ID: 37830918
Hi Brad,

Can you explain the below line of code as I would like to make sure I understand exactly what it does so it can benefit my learning...I know it is setting the range, but I am trying to understand the requirements of what I believe is the Range method

Set rg = Range(rg, Cells(Rows.Count, rg.Column).End(xlUp))
0
 
LVL 80

Expert Comment

by:byundt
ID: 37830937
You can define a range by listing its top left and bottom right corners. Here, I am doing it by taking the Range of a previously defined range and one returned by the Cells property of the worksheet.

To get the first range reference, the previous statement defines the top left corner of the Invoice# column with:
Set rg = Range("D1").End(xlDown)             'Look down from D1 to the first cell with data

It then becomes necessary to find the bottom right corner of that same column. I do this with Cells, which by default applies to the worksheet object. It needs a row number and column number input.
Cells(Rows.Count, rg.Column).End(xlUp)
The Rows.Count bit returns the number of the bottom-most row in the worksheet--65,536 for Excel 2003 or 1,048,576 in Excel 2007 & 2010.  By doing it that way, I don't need to ask which version of Excel you use, although you were nice enough to specify it.

To get the column number, I like to refer to rg.Column so I only have to update one statement if you move your data around. The .End(xlUp) then looks up the Invoice# column from the bottom, stopping at the first bit of data it sees--which just happens to be the last row of data.
0
 
LVL 80

Expert Comment

by:byundt
ID: 37830957
Statement 3, which declares variable fl as Filter was put in the code during testing. I ended up taking a different approach, but forgot to remove the declaration.

The bit with statement 8 looks three columns to the left of Invoice#, then sets the range equal to the same number of rows but with five columns.
0
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 50 total points
ID: 37831091
Without VBA, using a new sheet

A2=INT(VALUE(SUBSTITUTE(SUBSTITUTE(Received!$A$1,"Date:",""),"RunTime:","")))
B2=VALUE(SUBSTITUTE(SUBSTITUTE(Received!$A$1,"Date:",""),"RunTime:",""))-INT(VALUE(SUBSTITUTE(SUBSTITUTE(Received!$A$1,"Date:",""),"RunTime:","")))
C2=SUBSTITUTE(INDEX(Received!A:A,MAX(IF(OFFSET(Received!$A$1,0,0,MAX(SMALL(IF(ISNUMBER(Received!$B$1:$B$10000),ROW(Received!$B$1:$B$10000)),ROW()-1)))<>"",ROW(OFFSET(Received!$A$1,0,0,MAX(SMALL(IF(ISNUMBER(Received!$B$1:$B$10000),ROW(Received!$B$1:$B$10000)),ROW()-1))))))),"Rep:","",1)
D2=INDEX(Received!B:B,SMALL(IF(ISNUMBER(Received!$B$1:$B$10000),ROW(Received!$B$1:$B$10000)),ROW()-1))
E2=INDEX(Received!C:C,SMALL(IF(ISNUMBER(Received!$B$1:$B$10000),ROW(Received!$B$1:$B$10000)),ROW()-1))

Open in new window

0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 26

Expert Comment

by:redmondb
ID: 37831648
Very nice, ssaqibh!
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 37831807
A non VBA method for "populating blank cells with the value from above" as per question.

Apply an auto filter to the data and in the column that contains the blanks apply the filter to show Blanks.

In the first cell type a formula to reference the cell above, eg if the active cell is A3 type =A2.

Then highlight this cell and the remaining blank cells in the column; you don't need to do them individually, you can do it as a block. Then Press Ctrl + D to do the fill down. Only the visible cells will be populated, leaving those which were hidden with their original values. if you now remove the filter, the blank cells will be populated with the same as the relevant cell above. Select the column and copy and paste special values to overwrite the formulae.

Thanks
Rob H
0
 

Author Comment

by:upobDaPlaya
ID: 37847743
Hi byundt,

Thanks for the explanation on range.  Sorry on the delay.  It is only now that I have been able to spend quality time reviewing your responses and analyzing each line of code.  A few other questions re: your code...

1-Why do I need to specify the column when populating the date value as we did an offset of 3 columns so why couldn't I do rg.Columns.Value = DateValue(Mid(Range("C1").Value, 6, 10))..Aren't we already in Col 1 due to the offset line of code ?
2-What does the Resize(,5) do...



Your code lines that I am referencing:
Set rg = rg.Offset(0, -3).Resize(, 5)
rg.Columns(1).Value = DateValue(Mid(Range("C1").Value, 6, 10))
rg.Columns(2).Value = TimeValue(Right(Range("C1").Value, 8))
0
 
LVL 80

Expert Comment

by:byundt
ID: 37848324
upobDaPlaya,

Set rg = rg.Offset(0, -3)
Before statement 8, the variable rg is pointing to column D. I want it to point to column A, so I redefine it to a location 3 columns to the left.

Set rg = rg.Offset(0, -3).Resize(, 5)
I really want rg to refer to a range that has the same number of rows, but five columns of width. That's what the .Resize(, 5) does

rg.Columns(1).Value = DateValue(Mid(Range("C1").Value, 6, 10))
rg.Columns(2).Value = TimeValue(Right(Range("C1").Value, 8))
At this point, I have a range variable rg that points to the entire table, including the newly added columns on the left. I want the entire first column to contain the date, and the entire second column to contain the time. I need to specify the column because range variable rg now has five columns. Had I not done the .Resize(,5) then I wouldn't need the .Columns(1) for the date

It is good practice to create a variable pointing to your entire table. Conversely, continually referencing everything to a column in the middle of the table would be considered poor practice. I may have made my life a little more difficult (as you have pointed out) earlier in the code--but having got this variable makes subsequent statements 12 through 18 somewhat clearer. That's the payoff for following the good practice.

Brad
0
 

Author Comment

by:upobDaPlaya
ID: 37849125
Brad,

Final question on this topic and thanks for your patience.  Why is it a good idea "to create a variable pointing to your entire table" ?  I assume it is to have a known point of reference ?  Can you provide an example of the drawbacks if I did not reference the entire table ?

Thanks for your 2 explanations above.  I suppose it would be easy for me to just casually peruse my EE responses and go on with my work, but a real goal of mone when I signed up for EE was to be able to help others out on EE at some point...

I am taking an online VBA Excel class and bought a book so hopefully all of this combined with EE will be a good combo.  I know your input and others has been phenomenal :)
0
 
LVL 80

Expert Comment

by:byundt
ID: 37849176
upobDaPlaya,
Having assumed responsibility for somebody else's spaghetti code as my first professional assignment, I have a lot of respect for making future maintenance/modifications as easy as possible. You do this by using self-explanatory variable names, adding frequent code comments, being consistent in your approaches to similar problems, and breaking code into small tasks with clearly defined inputs & outputs.

These lessons were reinforced when I started posting on Experts Exchange because I know that many people don't fully understand the code or formulas being suggested--they just know whether it gives the right answer. Some people post the exact problem (complete with sample workbook). Others try to abstract the problem and move the starting point to Sheet1 cell A1. This means that whatever I post has to be rewritten before testing it in the real workbook. The easier I make it for them to perform the required alterations, the less likely I am to hear "Doesn't work" in their report back to me.

That's why I use three statements to define rg when it could have been done with one:
Set rg = Range(Range("D1").End(xlDown), Cells(Rows.Count, 4).End(xlUp)).Offset(0,-3).Resize(,5)
Would every Asker catch that a reference to column D appears in two places in that statement: Range("D1") and Cells(Rows.Count, 4)?

Is there anything inherently wrong with defining a table relative to column D? VBA doesn't care. But the Asker might be confused, if not today, then perhaps sometime in the future when the code has to be changed.

That's why I try to define a range variable pointing to the entire table, or possibly to its top left cell. Simply put, it makes the code much easier for most people to modify to suit their real situation.

Brad
0
 

Author Closing Comment

by:upobDaPlaya
ID: 37849430
Thanks for everyone's contribution :)
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
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 will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now