CODEWITHSUNDEEP
excelcopy-pastevba
Nick
Nick

Reputation: 1

Copy and paste cell value into until new cell value then repeat the process

I am trying to create a macro that will copy/paste or repeat the value of a previous cell into the next blank cell, until a new cell value, then it repeats until the end of a set range of 365 cells. The example below in the imgur link attempts to visualize what I mean, the second row is the desired result.

https://i.sstatic.net/jff8Y.jpg

Upvotes: 0

Views: 2032

Answers (2)

peege
peege

Reputation: 2477

This is the solution in VBA. 

Sub ReferAndRepeat()

Dim tempVal As String
Dim sheet As String

sheet = "Sheet1"   'Name your sheet here

    For lCol = 1 To 365
        If Sheets(sheet).Cells(1, lCol) <> "" Then
            tempVal = Sheets(sheet).Cells(1, lCol).Text
        End If

        Sheets(sheet).Cells(2, lCol) = tempVal
    Next lCol
End Sub

Screeshot

Upvotes: 0

Rusan Kax
Rusan Kax

Reputation: 1894

Unless I misunderstand you, this is easy in excel using formulas. VBA would be overkill.

The formula in cell A2 is =A1. The formula in cell B2 is simply =IF(B1="",A2,B1), which simply reads if cell B1 is empty, them use the value in cell A2, else if B1 is not empty, then use the value in B1. Then, as long as your reference values are as in row 1, in the image, you then copy this formula across, in row 2.

enter image description here

Upvotes: 0

Related Questions