Copy value until next value

[ewan7279]

Hi,

I am trying to write a code that will copy the contents of a cell - a month
- into all subsequent empty cells, unless another month is encountered. I
then want this new month to be copied into subsequent empty cells, until
another month is encountered. And so on. This will occur in one column only.

Reason: I have a spreadsheet that varies in size and format apart from one
column. The column contains one reference only per month's worth of line
items that follow. The line items do not contain any reference to the month
that they relate to. I am trying to write a code that will copy the only
reference of the month into every row that contains a line item for that
month, and repeat this for however many months there may be.

This is what I have so far, but it does not work:

Sub Month_copy

Dim CURRPERIOD As String
Dim CELL As Range, RNG As Range
With ActiveSheet
Set RNG = .Range(.Range("A1:a10000"), .Range("A1:A10000").End(xlDown))
End With

For Each CELL In RNG
CURRPERIOD = ActiveCell.Value
If ActiveCell = "" Then
ActiveCell.Value = CURRPERIOD
Else
CURRPERIOD = ActiveCell.Value
End If
Next
End sub

What I am trying to say is if the cell is blank, paste in the current month
value and move onto the next. If it is not blank, it must be a month
reference, therefore copy this into the subsequent blank cells.

Thanks in advance for any help received. (Hope this makes sense)

Ewan.

[Glen]

For Each CELL In RNG
CURRPERIOD = ActiveCell.Value
If ActiveCell = "" Then
ActiveCell.Value = CURRPERIOD
Else
CURRPERIOD = ActiveCell.Value
End If
Next
This isn't working because you're setting CURRPERIOD to active cell and
then you check active cell try this:

'assuming the first active cell has a value
CURRPERIOD = ActiveCell.Value
range(next cell).select
For each CELL in RNG
If ActiveCell = CURRPER1 then
range(next cell).select
Elseif ActiveCell = "" then
Activecell = CURRPERIOD
range(next cell).select
Else
CURRPERIOD = ActiveCell.Value
range(next cell).select
End If
Next

[ewan7279]

Glen,

Sorry, but this does not compile. Is "range(next cell).select" literal?

"Glen" wrote:

> For Each CELL In RNG
> CURRPERIOD = ActiveCell.Value
> If ActiveCell = "" Then
> ActiveCell.Value = CURRPERIOD
> Else
> CURRPERIOD = ActiveCell.Value
> End If
> Next
> This isn't working because you're setting CURRPERIOD to active cell and
> then you check active cell try this:
>
> 'assuming the first active cell has a value
> CURRPERIOD = ActiveCell.Value
> range(next cell).select
> For each CELL in RNG
> If ActiveCell = CURRPER1 then
> range(next cell).select
> Elseif ActiveCell = "" then
> Activecell = CURRPERIOD
> range(next cell).select
> Else
> CURRPERIOD = ActiveCell.Value
> range(next cell).select
> End If
> Next
>
>

[Glen]

no - you have to name whatever the next cell is. I am sorry for that.
The format should be Range("A1").select if A! is the cell you want.
You can also name it dynamically like this
dim cellnum as integer
cellnum = 1
Range("A" & cellnum & "").select

HTH

[Pointless]

I don understand how you are setting RNG. But once that is done you could just as well use good old fassion Offset(-1, 0).Value

For Each CELL In RNG
If CELL.Value = "" Then CELL.Value = CELL.Offset(-1, 0).Value
Next

Or something like:

Sub Month_copy()

Dim CURRPERIOD As String
Dim CELL As Range, StartCell As Range, RNG As Range
With ActiveSheet
If IsEmpty(.Range("A1")) Then
Set StartCell = .Range("A1").End(xlDown)
Else
Set StartCell = .Range("A1")
End If
Set RNG = .Range(StartCell, .Range("A10000").End(xlUp))
End With
'MsgBox (RNG.Address)

For Each CELL In RNG
If CELL.Value = "" Then CELL.Value = CELL.Offset(-1, 0).Value
Next

End Sub

[Bernie Deitrick]

Ewan,

Don't loop:

Sub Month_copy2()

With Range("A:A")
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
.Copy
.PasteSpecial xlPasteValues
End With

End sub

HTH,
Bernie
MS Excel MVP


"ewan7279" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> I am trying to write a code that will copy the contents of a cell - a month
> - into all subsequent empty cells, unless another month is encountered. I
> then want this new month to be copied into subsequent empty cells, until
> another month is encountered. And so on. This will occur in one column only.
>
> Reason: I have a spreadsheet that varies in size and format apart from one
> column. The column contains one reference only per month's worth of line
> items that follow. The line items do not contain any reference to the month
> that they relate to. I am trying to write a code that will copy the only
> reference of the month into every row that contains a line item for that
> month, and repeat this for however many months there may be.
>
> This is what I have so far, but it does not work:
>
> Sub Month_copy
>
> Dim CURRPERIOD As String
> Dim CELL As Range, RNG As Range
> With ActiveSheet
> Set RNG = .Range(.Range("A1:a10000"), .Range("A1:A10000").End(xlDown))
> End With
>
> For Each CELL In RNG
> CURRPERIOD = ActiveCell.Value
> If ActiveCell = "" Then
> ActiveCell.Value = CURRPERIOD
> Else
> CURRPERIOD = ActiveCell.Value
> End If
> Next
> End sub
>
> What I am trying to say is if the cell is blank, paste in the current month
> value and move onto the next. If it is not blank, it must be a month
> reference, therefore copy this into the subsequent blank cells.
>
> Thanks in advance for any help received. (Hope this makes sense)
>
> Ewan.

[Glen]

Very good point "pointless" - isn't that ironic.

[ewan7279]

Sorry Glen, I don't understand.

I'm trying to make each empty cell in the range A1:A10000 the value of the
variable CURRPERIOD. I thought the For Each loop would cycle through all
cells in the range and change their value to CURRPERIOD, with CURRPERIOD only
changing if a non-blank cell is activated i.e. if the cell contains a month?

"Glen" wrote:

> no - you have to name whatever the next cell is. I am sorry for that.
> The format should be Range("A1").select if A! is the cell you want.
> You can also name it dynamically like this
> dim cellnum as integer
> cellnum = 1
> Range("A" & cellnum & "").select
>
> HTH
>
>

[ewan7279]

Thanks Bernie,

The code only copies up to the next month, but not the last month itself
i.e. April copies up to May, May up to June, but June does not copy any
further. I need the month to copy down for every subsequent line item. Can
you help please?

"Bernie Deitrick" wrote:

> Ewan,
>
> Don't loop:
>
> Sub Month_copy2()
>
> With Range("A:A")
> .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
> .Copy
> .PasteSpecial xlPasteValues
> End With
>
> End sub
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "ewan7279" <[email protected]> wrote in message
> news:[email protected]...
> > Hi,
> >
> > I am trying to write a code that will copy the contents of a cell - a month
> > - into all subsequent empty cells, unless another month is encountered. I
> > then want this new month to be copied into subsequent empty cells, until
> > another month is encountered. And so on. This will occur in one column only.
> >
> > Reason: I have a spreadsheet that varies in size and format apart from one
> > column. The column contains one reference only per month's worth of line
> > items that follow. The line items do not contain any reference to the month
> > that they relate to. I am trying to write a code that will copy the only
> > reference of the month into every row that contains a line item for that
> > month, and repeat this for however many months there may be.
> >
> > This is what I have so far, but it does not work:
> >
> > Sub Month_copy
> >
> > Dim CURRPERIOD As String
> > Dim CELL As Range, RNG As Range
> > With ActiveSheet
> > Set RNG = .Range(.Range("A1:a10000"), .Range("A1:A10000").End(xlDown))
> > End With
> >
> > For Each CELL In RNG
> > CURRPERIOD = ActiveCell.Value
> > If ActiveCell = "" Then
> > ActiveCell.Value = CURRPERIOD
> > Else
> > CURRPERIOD = ActiveCell.Value
> > End If
> > Next
> > End sub
> >
> > What I am trying to say is if the cell is blank, paste in the current month
> > value and move onto the next. If it is not blank, it must be a month
> > reference, therefore copy this into the subsequent blank cells.
> >
> > Thanks in advance for any help received. (Hope this makes sense)
> >
> > Ewan.
>
>
>

[Bernie Deitrick]

If it needs to match the data in column B, then you could use

With Range("A2:A" &Range("B65536").End(xlUp).Row)
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
.Copy
.PasteSpecial xlPasteValues
End With

HTH,
Bernie
MS Excel MVP


"ewan7279" <[email protected]> wrote in message
news:[email protected]...
> Thanks Bernie,
>
> The code only copies up to the next month, but not the last month itself
> i.e. April copies up to May, May up to June, but June does not copy any
> further. I need the month to copy down for every subsequent line item. Can
> you help please?
>
> "Bernie Deitrick" wrote:
>
>> Ewan,
>>
>> Don't loop:
>>
>> Sub Month_copy2()
>>
>> With Range("A:A")
>> .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
>> .Copy
>> .PasteSpecial xlPasteValues
>> End With
>>
>> End sub
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>>
>> "ewan7279" <[email protected]> wrote in message
>> news:[email protected]...
>> > Hi,
>> >
>> > I am trying to write a code that will copy the contents of a cell - a month
>> > - into all subsequent empty cells, unless another month is encountered. I
>> > then want this new month to be copied into subsequent empty cells, until
>> > another month is encountered. And so on. This will occur in one column only.
>> >
>> > Reason: I have a spreadsheet that varies in size and format apart from one
>> > column. The column contains one reference only per month's worth of line
>> > items that follow. The line items do not contain any reference to the month
>> > that they relate to. I am trying to write a code that will copy the only
>> > reference of the month into every row that contains a line item for that
>> > month, and repeat this for however many months there may be.
>> >
>> > This is what I have so far, but it does not work:
>> >
>> > Sub Month_copy
>> >
>> > Dim CURRPERIOD As String
>> > Dim CELL As Range, RNG As Range
>> > With ActiveSheet
>> > Set RNG = .Range(.Range("A1:a10000"), .Range("A1:A10000").End(xlDown))
>> > End With
>> >
>> > For Each CELL In RNG
>> > CURRPERIOD = ActiveCell.Value
>> > If ActiveCell = "" Then
>> > ActiveCell.Value = CURRPERIOD
>> > Else
>> > CURRPERIOD = ActiveCell.Value
>> > End If
>> > Next
>> > End sub
>> >
>> > What I am trying to say is if the cell is blank, paste in the current month
>> > value and move onto the next. If it is not blank, it must be a month
>> > reference, therefore copy this into the subsequent blank cells.
>> >
>> > Thanks in advance for any help received. (Hope this makes sense)
>> >
>> > Ewan.
>>
>>
>>

[Tom Ogilvy]

The only reason it would do what you describe is if you are testing it on a
dummy sheet you set up for testing and there is no data beyond the row that
contains June. If you put data in any other column that extends beyond the
row with June, then this will fill down to that row.

--
Regards,
Tom Ogilvy


"ewan7279" <[email protected]> wrote in message
news:[email protected]...
> Thanks Bernie,
>
> The code only copies up to the next month, but not the last month itself
> i.e. April copies up to May, May up to June, but June does not copy any
> further. I need the month to copy down for every subsequent line item.
Can
> you help please?
>
> "Bernie Deitrick" wrote:
>
> > Ewan,
> >
> > Don't loop:
> >
> > Sub Month_copy2()
> >
> > With Range("A:A")
> > .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
> > .Copy
> > .PasteSpecial xlPasteValues
> > End With
> >
> > End sub
> >
> > HTH,
> > Bernie
> > MS Excel MVP
> >
> >
> > "ewan7279" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi,
> > >
> > > I am trying to write a code that will copy the contents of a cell - a
month
> > > - into all subsequent empty cells, unless another month is
encountered. I
> > > then want this new month to be copied into subsequent empty cells,
until
> > > another month is encountered. And so on. This will occur in one
column only.
> > >
> > > Reason: I have a spreadsheet that varies in size and format apart from
one
> > > column. The column contains one reference only per month's worth of
line
> > > items that follow. The line items do not contain any reference to the
month
> > > that they relate to. I am trying to write a code that will copy the
only
> > > reference of the month into every row that contains a line item for
that
> > > month, and repeat this for however many months there may be.
> > >
> > > This is what I have so far, but it does not work:
> > >
> > > Sub Month_copy
> > >
> > > Dim CURRPERIOD As String
> > > Dim CELL As Range, RNG As Range
> > > With ActiveSheet
> > > Set RNG = .Range(.Range("A1:a10000"),
..Range("A1:A10000").End(xlDown))
> > > End With
> > >
> > > For Each CELL In RNG
> > > CURRPERIOD = ActiveCell.Value
> > > If ActiveCell = "" Then
> > > ActiveCell.Value = CURRPERIOD
> > > Else
> > > CURRPERIOD = ActiveCell.Value
> > > End If
> > > Next
> > > End sub
> > >
> > > What I am trying to say is if the cell is blank, paste in the current
month
> > > value and move onto the next. If it is not blank, it must be a month
> > > reference, therefore copy this into the subsequent blank cells.
> > >
> > > Thanks in advance for any help received. (Hope this makes sense)
> > >
> > > Ewan.
> >
> >
> >

[ewan7279]

Hi Tom - you were right about me using a test sheet.

I'm not really sure what the macro that Bernie described does i.e. how it
works, but I've just tried it on 'live' data and it doesn't work at all, even
to the same level as on the test sheet. I'm really stuck...

"Tom Ogilvy" wrote:

> The only reason it would do what you describe is if you are testing it on a
> dummy sheet you set up for testing and there is no data beyond the row that
> contains June. If you put data in any other column that extends beyond the
> row with June, then this will fill down to that row.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "ewan7279" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks Bernie,
> >
> > The code only copies up to the next month, but not the last month itself
> > i.e. April copies up to May, May up to June, but June does not copy any
> > further. I need the month to copy down for every subsequent line item.
> Can
> > you help please?
> >
> > "Bernie Deitrick" wrote:
> >
> > > Ewan,
> > >
> > > Don't loop:
> > >
> > > Sub Month_copy2()
> > >
> > > With Range("A:A")
> > > .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
> > > .Copy
> > > .PasteSpecial xlPasteValues
> > > End With
> > >
> > > End sub
> > >
> > > HTH,
> > > Bernie
> > > MS Excel MVP
> > >
> > >
> > > "ewan7279" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Hi,
> > > >
> > > > I am trying to write a code that will copy the contents of a cell - a
> month
> > > > - into all subsequent empty cells, unless another month is
> encountered. I
> > > > then want this new month to be copied into subsequent empty cells,
> until
> > > > another month is encountered. And so on. This will occur in one
> column only.
> > > >
> > > > Reason: I have a spreadsheet that varies in size and format apart from
> one
> > > > column. The column contains one reference only per month's worth of
> line
> > > > items that follow. The line items do not contain any reference to the
> month
> > > > that they relate to. I am trying to write a code that will copy the
> only
> > > > reference of the month into every row that contains a line item for
> that
> > > > month, and repeat this for however many months there may be.
> > > >
> > > > This is what I have so far, but it does not work:
> > > >
> > > > Sub Month_copy
> > > >
> > > > Dim CURRPERIOD As String
> > > > Dim CELL As Range, RNG As Range
> > > > With ActiveSheet
> > > > Set RNG = .Range(.Range("A1:a10000"),
> ..Range("A1:A10000").End(xlDown))
> > > > End With
> > > >
> > > > For Each CELL In RNG
> > > > CURRPERIOD = ActiveCell.Value
> > > > If ActiveCell = "" Then
> > > > ActiveCell.Value = CURRPERIOD
> > > > Else
> > > > CURRPERIOD = ActiveCell.Value
> > > > End If
> > > > Next
> > > > End sub
> > > >
> > > > What I am trying to say is if the cell is blank, paste in the current
> month
> > > > value and move onto the next. If it is not blank, it must be a month
> > > > reference, therefore copy this into the subsequent blank cells.
> > > >
> > > > Thanks in advance for any help received. (Hope this makes sense)
> > > >
> > > > Ewan.
> > >
> > >
> > >
>
>
>

[ewan7279]

I have just cleared the contents of some of the apparently blank cells in the
live data and the macro now works. Any ideas why this would be please? It
would appear that I now need to figure out how to clear the contents of every
apparently blank cell in the column before copying the months into them, so I
am faced with the same original problem...

"ewan7279" wrote:

> Hi Tom - you were right about me using a test sheet.
>
> I'm not really sure what the macro that Bernie described does i.e. how it
> works, but I've just tried it on 'live' data and it doesn't work at all, even
> to the same level as on the test sheet. I'm really stuck...
>
> "Tom Ogilvy" wrote:
>
> > The only reason it would do what you describe is if you are testing it on a
> > dummy sheet you set up for testing and there is no data beyond the row that
> > contains June. If you put data in any other column that extends beyond the
> > row with June, then this will fill down to that row.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "ewan7279" <[email protected]> wrote in message
> > news:[email protected]...
> > > Thanks Bernie,
> > >
> > > The code only copies up to the next month, but not the last month itself
> > > i.e. April copies up to May, May up to June, but June does not copy any
> > > further. I need the month to copy down for every subsequent line item.
> > Can
> > > you help please?
> > >
> > > "Bernie Deitrick" wrote:
> > >
> > > > Ewan,
> > > >
> > > > Don't loop:
> > > >
> > > > Sub Month_copy2()
> > > >
> > > > With Range("A:A")
> > > > .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
> > > > .Copy
> > > > .PasteSpecial xlPasteValues
> > > > End With
> > > >
> > > > End sub
> > > >
> > > > HTH,
> > > > Bernie
> > > > MS Excel MVP
> > > >
> > > >
> > > > "ewan7279" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > Hi,
> > > > >
> > > > > I am trying to write a code that will copy the contents of a cell - a
> > month
> > > > > - into all subsequent empty cells, unless another month is
> > encountered. I
> > > > > then want this new month to be copied into subsequent empty cells,
> > until
> > > > > another month is encountered. And so on. This will occur in one
> > column only.
> > > > >
> > > > > Reason: I have a spreadsheet that varies in size and format apart from
> > one
> > > > > column. The column contains one reference only per month's worth of
> > line
> > > > > items that follow. The line items do not contain any reference to the
> > month
> > > > > that they relate to. I am trying to write a code that will copy the
> > only
> > > > > reference of the month into every row that contains a line item for
> > that
> > > > > month, and repeat this for however many months there may be.
> > > > >
> > > > > This is what I have so far, but it does not work:
> > > > >
> > > > > Sub Month_copy
> > > > >
> > > > > Dim CURRPERIOD As String
> > > > > Dim CELL As Range, RNG As Range
> > > > > With ActiveSheet
> > > > > Set RNG = .Range(.Range("A1:a10000"),
> > ..Range("A1:A10000").End(xlDown))
> > > > > End With
> > > > >
> > > > > For Each CELL In RNG
> > > > > CURRPERIOD = ActiveCell.Value
> > > > > If ActiveCell = "" Then
> > > > > ActiveCell.Value = CURRPERIOD
> > > > > Else
> > > > > CURRPERIOD = ActiveCell.Value
> > > > > End If
> > > > > Next
> > > > > End sub
> > > > >
> > > > > What I am trying to say is if the cell is blank, paste in the current
> > month
> > > > > value and move onto the next. If it is not blank, it must be a month
> > > > > reference, therefore copy this into the subsequent blank cells.
> > > > >
> > > > > Thanks in advance for any help received. (Hope this makes sense)
> > > > >
> > > > > Ewan.
> > > >
> > > >
> > > >
> >
> >
> >

[Pointless]

That srot of mess up the basics for Bernies "speciel cells" sulution. Back to the painfully slow word of loops I guess...

Could you make some kind of assumption about the "real" month values?
This example assumes that any value that is less than 2 characters is actually blank or junk.

Sub Month_copy()
Dim CELL As Range, RNG As Range

Set RNG = ActiveSheet.Range("A2", "A10000")

For Each CELL In RNG
If Len(CELL.Value) < 2 Then CELL.Value = CELL.Offset(-1, 0).Value
Next

End Sub

Set the "RNG" in some clever way (dont start from row 1). Example:
lastRow = ActiveSheet.Range("b10000").End(xlUp).Row
Set RNG = ActiveSheet.Range("A2", ActiveSheet.Cells(lastRow, 1))

/clueless

[ewan7279]

Cheers pointless - works like a treat. I knew it wasn't as hard as I was
making it...!!

Thanks to Glen, Tom and Bernie for the input too.

"Pointless" wrote:

>
> That srot of mess up the basics for Bernies "speciel cells" sulution.
> Back to the painfully slow word of loops I guess...
>
> Could you make some kind of assumption about the "real" month values?
> This example assumes that any value that is less than 2 characters is
> actually blank or junk.
>
> Sub Month_copy()
> Dim CELL As Range, RNG As Range
>
> Set RNG = ActiveSheet.Range("A2", "A10000")
>
> For Each CELL In RNG
> If Len(CELL.Value) < 2 Then CELL.Value = CELL.Offset(-1, 0).Value
> Next
>
> End Sub
>
> Set the "RNG" in some clever way (dont start from row 1). Example:
> lastRow = ActiveSheet.Range("b10000").End(xlUp).Row
> Set RNG = ActiveSheet.Range("A2", ActiveSheet.Cells(lastRow, 1))
>
> /clueless
>
>
> --
> Pointless
> ------------------------------------------------------------------------
> Pointless's Profile: http://www.excelforum.com/member.php...o&userid=30862
> View this thread: http://www.excelforum.com/showthread...hreadid=506799
>
>