Go Premium for a chance to win a PS4. Enter to Win

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

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

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
upobDaPlaya
Asked:
upobDaPlaya
  • 5
  • 4
  • 3
  • +2
3 Solutions
 
redmondbCommented:
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
 
byundtCommented:
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
 
redmondbCommented:
Agreed, Brad. Although I suspect that you're being overly modest - with a big import, your code would be a lot faster!
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
upobDaPlayaAuthor Commented:
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
 
byundtCommented:
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
 
byundtCommented:
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
 
Saqib Husain, SyedEngineerCommented:
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
 
redmondbCommented:
Very nice, ssaqibh!
0
 
Rob HensonIT & Database AssistantCommented:
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
 
upobDaPlayaAuthor Commented:
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
 
byundtCommented:
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
 
upobDaPlayaAuthor Commented:
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
 
byundtCommented:
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
 
upobDaPlayaAuthor Commented:
Thanks for everyone's contribution :)
0

Featured Post

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!

  • 5
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now