Forum Discussion

Anonymous's avatar
Anonymous
May 31, 2017
Solved

VBA to copy a value until the value changes

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....
Macros & VBA
Need Help
  • John Jairo Vergara Domínguez's avatar
    John Jairo Vergara Domínguez
    May 31, 2017

    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!

Anonymous's avatar
Anonymous
May 31, 2017

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

Resources