|
Copy value until next value
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. |
Copy value until next value
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 |
Copy value until next value
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 |
Copy value until next value
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 |
Copy value until next value
I don understand how you are setting RNG. But once that is done yo 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 Su -- Pointles ----------------------------------------------------------------------- Pointless's Profile: http://www.excelforum.com/member.php...fo&userid=3086 View this thread: http://www.excelforum.com/showthread.php?threadid=50679 |
Copy value until next value
Very good point "pointless" - isn't that ironic.
|
Copy value until next value
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" wrote in message ... 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. |
Copy value until next value
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 |
Copy value until next value
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" wrote in message ... 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. |
Copy value until next value
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" wrote in message ... 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" wrote in message ... 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. |
Copy value until next value
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" wrote in message ... 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" wrote in message ... 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. |
Copy value until next value
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" wrote in message ... 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" wrote in message ... 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. |
Copy value until next value
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" wrote in message ... 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" wrote in message ... 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. |
Copy value until next value
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 i 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)) /clueles -- Pointles ----------------------------------------------------------------------- Pointless's Profile: http://www.excelforum.com/member.php...fo&userid=3086 View this thread: http://www.excelforum.com/showthread.php?threadid=50679 |
Copy value until next value
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 |
| All times are GMT +1. The time now is 01:54 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com