SOLVED

VBA to copy a value until the value changes

Deleted
Not applicable

Hello.

First I must admit, I am not a VBA programmer, but aspire to learn.  I have a complex sales report that is saved in Excel spreadsheet format, with several sub-totals for customer and product.  The key field of customer number and product number are on subtotal headings, not on the rows where the actual values are stored.  This is a classic ERP report that needs to be stripped and unpivotted, so that I can analyze data.    Ultimately, I want to have a spreadsheet template set up so that my admin assist. can click a button and the VBA or the Get/Transform will run and strip the data into a usable data file.

 

I started with Get/Transform and it worked beautifully - it was easy to setup, BUT I got stumped thinking I could have the Worksheet with G/T queries saved as a template workbook, that my admin could paste in values from another spreadsheet and it could run the Get/Transfform queries.    Well, that didn't work.  I researched and found that there isn't a feature like this in G/T, and that you have to cut/paste the queries in the advanced Editor.    While she could do this, I don't want to take that approach because I think it will be prone to error.    I'm still open to using the G/T approach if I can find a simple way to re-use the queries.   These queries should run the same for each subsequent sales report.

 

Method 2:

Having been stumped on that approach, I decided to try writing VBA code to strip the report.   Don't let me fool you into thinking I have that skill, because I don't.  I simply used Macros to record my stripping steps and then compiled the different macro VBA code to produce the program.  That worked very well, UNTIL, I go to a point where I needed the functionality to do what the Get/Transform "FillUp or FillDown" function does.   Initially, I thought that I could use the Spreadsheet function of Fill Up or Fill Down - but i realized this is just to fill a range with the same value and doesn't work like the Get/Transform one.     

 

So, i'm looking for other ideas, or if someone can write me a small vba routine that would copy down the value of a cell, replacing null cells below it, until it encounters the next, cell value, and then copy that value down to the next cell in the column, until it hits the end of the table.

 

For example:

 

Company:

Manhatten

null

null

null

null

Jones

null

null

null

 

would become:

 

Manhatten

Manhatten

Mahnatten

Manhatten

Manhatten

Jones

Jones

Jones

Jones

 

 

At this point I would prefer the VBA code, althought the use case is more suited to the G/T method.   I wish Microsoft added an ability like they have with vba to make the queries more automatically re-usable.

 

 

 

 

 

3 Replies

Hallo,

 

below a sample function, you surely must adapt to your requirements. The code reads the specified range values into an array, replaces the empty values of its first column and writes the array back to the indicated sheet.

 

Public Function FillWithPrecedents(Sheet As String, RangeSource As String)
  
  Dim n As Long
  Dim d As Variant
  
  d = ThisWorkbook.Worksheets(Sheet).Range(RangeSource).Value
  
  For n = 1 + LBound(d, 1) To UBound(d, 1)
    
    If Len(d(n, 1)) < 1 Then
      
      d(n, 1) = d(n - 1, 1)
      
    End If
    
  Next
  
  ThisWorkbook.Worksheets(Sheet).Range(RangeSource).Value = d
  
End Function

Public Sub Test()

  FillWithPrecedents "Sample", "B1:B9"
  
End Sub

Best,

Mourad

best response confirmed by Sergei Baklan (MVP)
Solution

Hi to all!

 

With this code, you can do it easily:

 

Sub FillRange()
    With Range("A2:A10")
        .SpecialCells(4) = "=R[-1]C"
        .Value = .Value
    End With
End Sub

Note: Replace the Range("A2:A10") for your range.

 

Blessings!

@John Jairo Vergara Domínguez 

This code is brilliant. You are a genius!

Thank you sooo much.