Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reducing numbers by one in defined areas over several sheets
Hi I need some help constructing a macro. I have a workbook with 12 sheets , A - L. In each sheet , range A3 - N35 contains a series of numbers , with some cells being blank. I need a macro which will go through each sheet , reducing each number it finds by 1. Blank cells should be left blank. Where a cell becomes negative after subtracting 1 from it , this should be made blank too. Can someone help? Grateful for any assistance. Best Wishes |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reducing numbers by one in defined areas over several sheets
On 13 Apr., 14:47, Colin Hayes wrote:
Hi I need some help constructing a macro. I have a workbook with 12 sheets , A - L. In each sheet , range A3 - N35 contains a series of numbers , with some cells being blank. I need a macro which will go through each sheet , reducing each number it finds by 1. Blank cells should be left blank. Where a cell becomes negative after subtracting 1 from it , this should be made blank too. Can someone help? Grateful for any assistance. Best Wishes Hi Colin I assume that you only have the sheets mentioned in your workbook. This code will do what you want. Sub SubtractInRange() Application.ScreenUpdating = False TargetRange = "A3:N35" For sh = 1 To Sheets.Count Sheets(sh).Select For Each c In Range(TargetRange) If IsNumeric(c.Value) = True Then c.Value = c.Value - 1 If c.Value < 0 Then c.Value = "" End If Next Next Application.ScreenUpdating = True End Sub Regards, Per |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reducing numbers by one in defined areas over several sheets
Give this a try...
Sub DecreaseByOne() Dim WS As Worksheet Dim CL As Range Application.ScreenUpdating = False For Each WS In Worksheets For Each CL In WS.Range("A3:N35") If IsNumeric(CL.Value) Then CL.Value = CL.Value - 1 If CL.Value < 0 Then CL = "" Next Next Application.ScreenUpdating = True End Sub Rick "Colin Hayes" wrote in message ... Hi I need some help constructing a macro. I have a workbook with 12 sheets , A - L. In each sheet , range A3 - N35 contains a series of numbers , with some cells being blank. I need a macro which will go through each sheet , reducing each number it finds by 1. Blank cells should be left blank. Where a cell becomes negative after subtracting 1 from it , this should be made blank too. Can someone help? Grateful for any assistance. Best Wishes |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reducing numbers by one in defined areas over several sheets
Hi guys OK thanks - that's done the trick. It does give a couple of anomalous results , I find , but I can work them out. Thanks again. BTW Per , could you add a line to the end of the code you gave to select the first sheet in the wb on completion of the macro? ^_^ In article , Rick Rothstein (MVP - VB) writes Give this a try... Sub DecreaseByOne() Dim WS As Worksheet Dim CL As Range Application.ScreenUpdating = False For Each WS In Worksheets For Each CL In WS.Range("A3:N35") If IsNumeric(CL.Value) Then CL.Value = CL.Value - 1 If CL.Value < 0 Then CL = "" Next Next Application.ScreenUpdating = True End Sub Rick "Colin Hayes" wrote in message ... Hi I need some help constructing a macro. I have a workbook with 12 sheets , A - L. In each sheet , range A3 - N35 contains a series of numbers , with some cells being blank. I need a macro which will go through each sheet , reducing each number it finds by 1. Blank cells should be left blank. Where a cell becomes negative after subtracting 1 from it , this should be made blank too. Can someone help? Grateful for any assistance. Best Wishes |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reducing numbers by one in defined areas over several sheets
Just out of curiosity, what anomalous results are you getting?
If you are running my code, it will leave you on the same sheet you call the macro from. However, if you to explicitly go to sheet #1, add this.... Worksheets(1).Select at the end of the macro (probably before setting ScreenUpdating to True would make the most sense). Rick "Colin Hayes" wrote in message ... Hi guys OK thanks - that's done the trick. It does give a couple of anomalous results , I find , but I can work them out. Thanks again. BTW Per , could you add a line to the end of the code you gave to select the first sheet in the wb on completion of the macro? ^_^ In article , Rick Rothstein (MVP - VB) writes Give this a try... Sub DecreaseByOne() Dim WS As Worksheet Dim CL As Range Application.ScreenUpdating = False For Each WS In Worksheets For Each CL In WS.Range("A3:N35") If IsNumeric(CL.Value) Then CL.Value = CL.Value - 1 If CL.Value < 0 Then CL = "" Next Next Application.ScreenUpdating = True End Sub Rick "Colin Hayes" wrote in message ... Hi I need some help constructing a macro. I have a workbook with 12 sheets , A - L. In each sheet , range A3 - N35 contains a series of numbers , with some cells being blank. I need a macro which will go through each sheet , reducing each number it finds by 1. Blank cells should be left blank. Where a cell becomes negative after subtracting 1 from it , this should be made blank too. Can someone help? Grateful for any assistance. Best Wishes |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reducing numbers by one in defined areas over several sheets
In article , Rick Rothstein (MVP
- VB) writes Just out of curiosity, what anomalous results are you getting? If you are running my code, it will leave you on the same sheet you call the macro from. However, if you to explicitly go to sheet #1, add this.... Worksheets(1).Select at the end of the macro (probably before setting ScreenUpdating to True would make the most sense). Rick Hi Rick I'm applying the code to dates in each of the sheets. I had to change the IsNumeric element to IsDate , of course. It works OK but the first day of the month , which should become 0 and therefore be blanked out is actually reading 31. The last day of the month is missing altogether. This is repeated over all 12 sheets. I can live with this and amend manually , although it would be nice if it did the whole job. I know dates can get complicated. Best Wishes "Colin Hayes" wrote in message ... Hi guys OK thanks - that's done the trick. It does give a couple of anomalous results , I find , but I can work them out. Thanks again. B TW Per , could you add a line to the end of the code you gave to select the first sheet in the wb on completion of the macro? ^_^ In article , Rick Rothstein (MVP - VB) writes Give this a try... Sub DecreaseByOne() Dim WS As Worksheet Dim CL As Range Application.ScreenUpdating = False For Each WS In Worksheets For Each CL In WS.Range("A3:N35") If IsNumeric(CL.Value) Then CL.Value = CL.Value - 1 If CL.Value < 0 Then CL = "" Next Next Application.ScreenUpdating = True End Sub Rick "Colin Hayes" wrote in message ... Hi I need some help constructing a macro. I have a workbook with 12 sheets , A - L. In each sheet , range A3 - N35 contains a series of numbers , with some cells being blank. I need a macro which will go through each sheet , reducing each number it finds by 1. Blank cells should be left blank. Where a cell becomes negative after subtracting 1 from it , this should be made blank too. Can someone help? Grateful for any assistance. Best Wishes |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reducing numbers by one in defined areas over several sheets
First off, you never mentioned dates in your original question. As a matter
of fact, you specifically requested blanking out the cell if the "number" became negative... a date can never be negative. Hence, the reason I and Per gave you the code we did. Okay, now, as to your question given your latest posting... the code below will blank out the cell if the date it contains is the first of the month; however, I do not understand what you mean when you say the "last day of the month is missing altogether". If you are blanking out a cell when one is subtracted from the first of the month, then there will be no date (only an empty cell) to subtract one from in order to get the last day of the month; and if you start with the end of the month, then my code (and I'm pretty sure Per's code) will properly decrement it by one. So, can you clarify what you meant, or what you were looking to have happen, when you said that? Sub DecreaseByOne() Dim WS As Worksheet Dim CL As Range Application.ScreenUpdating = False For Each WS In Worksheets For Each CL In WS.Range("A3:N35") If IsDate(CL.Value) Then If Day(CL.Value) = 1 Then CL = "" Else CL.Value = CL.Value - 1 End If End If Next Next Application.ScreenUpdating = True End Sub Rick "Colin Hayes" wrote in message ... In article , Rick Rothstein (MVP - VB) writes Just out of curiosity, what anomalous results are you getting? If you are running my code, it will leave you on the same sheet you call the macro from. However, if you to explicitly go to sheet #1, add this.... Worksheets(1).Select at the end of the macro (probably before setting ScreenUpdating to True would make the most sense). Rick Hi Rick I'm applying the code to dates in each of the sheets. I had to change the IsNumeric element to IsDate , of course. It works OK but the first day of the month , which should become 0 and therefore be blanked out is actually reading 31. The last day of the month is missing altogether. This is repeated over all 12 sheets. I can live with this and amend manually , although it would be nice if it did the whole job. I know dates can get complicated. Best Wishes "Colin Hayes" wrote in message ... Hi guys OK thanks - that's done the trick. It does give a couple of anomalous results , I find , but I can work them out. Thanks again. B TW Per , could you add a line to the end of the code you gave to select the first sheet in the wb on completion of the macro? ^_^ In article , Rick Rothstein (MVP - VB) writes Give this a try... Sub DecreaseByOne() Dim WS As Worksheet Dim CL As Range Application.ScreenUpdating = False For Each WS In Worksheets For Each CL In WS.Range("A3:N35") If IsNumeric(CL.Value) Then CL.Value = CL.Value - 1 If CL.Value < 0 Then CL = "" Next Next Application.ScreenUpdating = True End Sub Rick "Colin Hayes" wrote in message ... Hi I need some help constructing a macro. I have a workbook with 12 sheets , A - L. In each sheet , range A3 - N35 contains a series of numbers , with some cells being blank. I need a macro which will go through each sheet , reducing each number it finds by 1. Blank cells should be left blank. Where a cell becomes negative after subtracting 1 from it , this should be made blank too. Can someone help? Grateful for any assistance. Best Wishes |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reducing numbers by one in defined areas over several sheets
Hi Rick Sorry for not mentioning that it is dates we are working on. Thanks for getting back and for the new code. It works perfectly to remove the erroneous date at the beginning of the month now. The 'missing' last day of the month continues to be an issue. Due of course to each date being reduced by one the final 31st of the month , for example , is becoming the 30th , and the 31st is not being replaced. There would have needed to have been a 32nd in the original sheet for this to happen(!). This follows for all 12 sheets - the last date in each month is missing after running the macro. Complicating factor of course is the differing lengths of the months. It's easily corrected manually , of course , and would be hard (I think) programmatically to correct. Thanks again for you expertise. Best Wishes In article , Rick Rothstein (MVP - VB) writes First off, you never mentioned dates in your original question. As a matter of fact, you specifically requested blanking out the cell if the "number" became negative... a date can never be negative. Hence, the reason I and Per gave you the code we did. Okay, now, as to your question given your latest posting... the code below will blank out the cell if the date it contains is the first of the month; however, I do not understand what you mean when you say the "last day of the month is missing altogether". If you are blanking out a cell when one is subtracted from the first of the month, then there will be no date (only an empty cell) to subtract one from in order to get the last day of the month; and if you start with the end of the month, then my code (and I'm pretty sure Per's code) will properly decrement it by one. So, can you clarify what you meant, or what you were looking to have happen, when you said that? Sub DecreaseByOne() Dim WS As Worksheet Dim CL As Range Application.ScreenUpdating = False For Each WS In Worksheets For Each CL In WS.Range("A3:N35") If IsDate(CL.Value) Then If Day(CL.Value) = 1 Then CL = "" Else CL.Value = CL.Value - 1 End If End If Next Next Application.ScreenUpdating = True End Sub Rick "Colin Hayes" wrote in message ... In article , Rick Rothstein (MVP - VB) writes Just out of curiosity, what anomalous results are you getting? If you are running my code, it will leave you on the same sheet you call the macro from. However, if you to explicitly go to sheet #1, add this.... Worksheets(1).Select at the end of the macro (probably before setting ScreenUpdating to True would make the most sense). Rick Hi Rick I'm applying the code to dates in each of the sheets. I had to change the IsNumeric element to IsDate , of course. It works OK but the first day of the month , which should become 0 and therefore be blanked out is actually reading 31. The last day of the month is missing altogether. This is repeated over all 12 sheets. I can live with this and amend manually , although it would be nice if it did the whole job. I know dates can get complicated. Best Wishes "Colin Hayes" wrote in message ... Hi guys OK thanks - that's done the trick. It does give a couple of anomalous results , I find , but I can work them out. Thanks again. B TW Per , could you add a line to the end of the code you gave to select the first sheet in the wb on completion of the macro? ^_^ In article , Rick Rothstein (MVP - VB) writes Give this a try... Sub DecreaseByOne() Dim WS As Worksheet Dim CL As Range Application.ScreenUpdating = False For Each WS In Worksheets For Each CL In WS.Range("A3:N35") If IsNumeric(CL.Value) Then CL.Value = CL.Value - 1 If CL.Value < 0 Then CL = "" Next Next Application.ScreenUpdating = True End Sub Rick "Colin Hayes" wrote in message ... Hi I need some help constructing a macro. I have a workbook with 12 sheets , A - L. In each sheet , range A3 - N35 contains a series of numbers , with some cells being blank. I need a macro which will go through each sheet , reducing each number it finds by 1. Blank cells should be left blank. Where a cell becomes negative after subtracting 1 from it , this should be made blank too. Can someone help? Grateful for any assistance. Best Wishes |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reducing numbers by one in defined areas over several sheets
I don't think it will be hard to code once we understand what is controlling
it. What would need to be in a cell in order that decrementing it by one would produce the last day of a month? What is in the cell initially? A blank? Or, what is in the column and/or row that indicates a date should be in the cell and, if that cell is blank, the date should be the last day of the month? How will you know which month that is? In other words, right now, you can look at your table and tell it needs to have the last date placed in a cell... what is it about the layout of your data that lets you know to put that last day of the month in what I am assuming is a blank cell? Rick "Colin Hayes" wrote in message ... Hi Rick Sorry for not mentioning that it is dates we are working on. Thanks for getting back and for the new code. It works perfectly to remove the erroneous date at the beginning of the month now. The 'missing' last day of the month continues to be an issue. Due of course to each date being reduced by one the final 31st of the month , for example , is becoming the 30th , and the 31st is not being replaced. There would have needed to have been a 32nd in the original sheet for this to happen(!). This follows for all 12 sheets - the last date in each month is missing after running the macro. Complicating factor of course is the differing lengths of the months. It's easily corrected manually , of course , and would be hard (I think) programmatically to correct. Thanks again for you expertise. Best Wishes In article , Rick Rothstein (MVP - VB) writes First off, you never mentioned dates in your original question. As a matter of fact, you specifically requested blanking out the cell if the "number" became negative... a date can never be negative. Hence, the reason I and Per gave you the code we did. Okay, now, as to your question given your latest posting... the code below will blank out the cell if the date it contains is the first of the month; however, I do not understand what you mean when you say the "last day of the month is missing altogether". If you are blanking out a cell when one is subtracted from the first of the month, then there will be no date (only an empty cell) to subtract one from in order to get the last day of the month; and if you start with the end of the month, then my code (and I'm pretty sure Per's code) will properly decrement it by one. So, can you clarify what you meant, or what you were looking to have happen, when you said that? Sub DecreaseByOne() Dim WS As Worksheet Dim CL As Range Application.ScreenUpdating = False For Each WS In Worksheets For Each CL In WS.Range("A3:N35") If IsDate(CL.Value) Then If Day(CL.Value) = 1 Then CL = "" Else CL.Value = CL.Value - 1 End If End If Next Next Application.ScreenUpdating = True End Sub Rick "Colin Hayes" wrote in message ... In article , Rick Rothstein (MVP - VB) writes Just out of curiosity, what anomalous results are you getting? If you are running my code, it will leave you on the same sheet you call the macro from. However, if you to explicitly go to sheet #1, add this.... Worksheets(1).Select at the end of the macro (probably before setting ScreenUpdating to True would make the most sense). Rick Hi Rick I'm applying the code to dates in each of the sheets. I had to change the IsNumeric element to IsDate , of course. It works OK but the first day of the month , which should become 0 and therefore be blanked out is actually reading 31. The last day of the month is missing altogether. This is repeated over all 12 sheets. I can live with this and amend manually , although it would be nice if it did the whole job. I know dates can get complicated. Best Wishes "Colin Hayes" wrote in message ... Hi guys OK thanks - that's done the trick. It does give a couple of anomalous results , I find , but I can work them out. Thanks again. B TW Per , could you add a line to the end of the code you gave to select the first sheet in the wb on completion of the macro? ^_^ In article , Rick Rothstein (MVP - VB) writes Give this a try... Sub DecreaseByOne() Dim WS As Worksheet Dim CL As Range Application.ScreenUpdating = False For Each WS In Worksheets For Each CL In WS.Range("A3:N35") If IsNumeric(CL.Value) Then CL.Value = CL.Value - 1 If CL.Value < 0 Then CL = "" Next Next Application.ScreenUpdating = True End Sub Rick "Colin Hayes" wrote in message .. . Hi I need some help constructing a macro. I have a workbook with 12 sheets , A - L. In each sheet , range A3 - N35 contains a series of numbers , with some cells being blank. I need a macro which will go through each sheet , reducing each number it finds by 1. Blank cells should be left blank. Where a cell becomes negative after subtracting 1 from it , this should be made blank too. Can someone help? Grateful for any assistance. Best Wishes |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reducing numbers by one in defined areas over several sheets
In article , Rick Rothstein (MVP
- VB) writes I don't think it will be hard to code once we understand what is controlling it. What would need to be in a cell in order that decrementing it by one would produce the last day of a month? What is in the cell initially? A blank? Or, what is in the column and/or row that indicates a date should be in the cell and, if that cell is blank, the date should be the last day of the month? How will you know which month that is? In other words, right now, you can look at your table and tell it needs to have the last date placed in a cell... what is it about the layout of your data that lets you know to put that last day of the month in what I am assuming is a blank cell? Rick Hi Rick It's certainly an interesting one. What's happening is the each cell containing a date (ddmmyyyy) is being deprecated by one. When the date becomes zero in the case of the first day of the month , the macro is blanking that cell. In the case of the last day of the month , the macro would need to add a date to compensate for the reduction that has taken place. For example , G27 in April was showing 30 as the last day of the month. After running the macro , it is now showing 29. For this reason , the next cell in order (I27) would need the 30th to be added to it. This applies to all the months. The complicating thing is that all the months are of differing lengths , and also that the cell that needs to have a new end-of-month added would change across the months. Presently the new last-day cell is blank. Also , as each month is contained in the same-sized grid (A3:N35) , sometimes the end-of-the-month date would need to go to the row below. M27 would become A33 I'd be happy to forward the wb itself for you to see the issue , if you wish. It''s very small , and I'm sure would be clearer if you could see it for yourself. Thanks for your help , I'm grateful. ^_^ Best Wishes. "Colin Hayes" wrote in message ... Hi Rick Sorry for not mentioning that it is dates we are working on. Thanks for getting back and for the new code. It works perfectly to remove the erroneous date at the beginning of the month now. The 'missing' last day of the month continues to be an issue. Due of course to each date being reduced by one the final 31st of the month , for example , is becoming the 30th , and the 31st is not being replaced. There would have needed to have been a 32nd in the original sheet for this to happen(!). This follows for all 12 sheets - the last date in each month is missing after running the macro. Complicating factor of course is the differing lengths of the months. It's easily corrected manually , of course , and would be hard (I think) programmatically to correct. Thanks again for you expertise. Best Wishes In article , Rick Rothstein (MVP - VB) writes First off, you never mentioned dates in your original question. As a matter of fact, you specifically requested blanking out the cell if the "number" became negative... a date can never be negative. Hence, the reason I and Per gave you the code we did. Okay, now, as to your question given your latest posting... the code below will blank out the cell if the date it contains is the first of the month; however, I do not understand what you mean when you say the "last day of the month is missing altogether". If you are blanking out a cell when one is subtracted from the first of the month, then there will be no date (only an empty cell) to subtract one from in order to get the last day of the month; and if you start with the end of the month, then my code (and I'm pretty sure Per's code) will properly decrement it by one. So, can you clarify what you meant, or what you were looking to have happen, when you said that? Sub DecreaseByOne() Dim WS As Worksheet Dim CL As Range Application.ScreenUpdating = False For Each WS In Worksheets For Each CL In WS.Range("A3:N35") If IsDate(CL.Value) Then If Day(CL.Value) = 1 Then CL = "" Else CL.Value = CL.Value - 1 End If End If Next Next Application.ScreenUpdating = True End Sub Rick "Colin Hayes" wrote in message ... In article , Rick Rothstein (MVP - VB) writes Just out of curiosity, what anomalous results are you getting? If you are running my code, it will leave you on the same sheet you call the macro from. However, if you to explicitly go to sheet #1, add this.... Worksheets(1).Select at the end of the macro (probably before setting ScreenUpdating to True would make the most sense). Rick Hi Rick I'm applying the code to dates in each of the sheets. I had to change the IsNumeric element to IsDate , of course. It works OK but the first day of the month , which should become 0 and therefore be blanked out is actually reading 31. The last day of the month is missing altogether. This is repeated over all 12 sheets. I can live with this and amend manually , although it would be nice if it did the whole job. I know dates can get complicated. Best Wishes "Colin Hayes" wrote in message ... Hi guys OK thanks - that's done the trick. It does give a couple of anomalous results , I find , but I can work them out. Thanks again. B TW Per , could you add a line to the end of the code you gave to select the first sheet in the wb on completion of the macro? ^_^ In article , Rick Rothstein (MVP - VB) writes Give this a try... Sub DecreaseByOne() Dim WS As Worksheet Dim CL As Range Application.ScreenUpdating = False For Each WS In Worksheets For Each CL In WS.Range("A3:N35") If IsNumeric(CL.Value) Then CL.Value = CL.Value - 1 If CL.Value < 0 Then CL = "" Next Next Application.ScreenUpdating = True End Sub Rick "Colin Hayes" wrote in message . .. Hi I need some help constructing a macro. I have a workbook with 12 sheets , A - L. In each sheet , range A3 - N35 contains a series of numbers , with some cells being blank. I need a macro which will go through each sheet , reducing each number it finds by 1. Blank cells should be left blank. Where a cell becomes negative after subtracting 1 from it , this should be made blank too. Can someone help? Grateful for any assistance. Best Wishes |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reducing numbers by one in defined areas over several sheets
Rick? In article , Colin Hayes writes In article , Rick Rothstein (MVP - VB) writes I don't think it will be hard to code once we understand what is controlling it. What would need to be in a cell in order that decrementing it by one would produce the last day of a month? What is in the cell initially? A blank? Or, what is in the column and/or row that indicates a date should be in the cell and, if that cell is blank, the date should be the last day of the month? How will you know which month that is? In other words, right now, you can look at your table and tell it needs to have the last date placed in a cell... what is it about the layout of your data that lets you know to put that last day of the month in what I am assuming is a blank cell? Rick Hi Rick It's certainly an interesting one. What's happening is the each cell containing a date (ddmmyyyy) is being deprecated by one. When the date becomes zero in the case of the first day of the month , the macro is blanking that cell. In the case of the last day of the month , the macro would need to add a date to compensate for the reduction that has taken place. For example , G27 in April was showing 30 as the last day of the month. After running the macro , it is now showing 29. For this reason , the next cell in order (I27) would need the 30th to be added to it. This applies to all the months. The complicating thing is that all the months are of differing lengths , and also that the cell that needs to have a new end-of-month added would change across the months. Presently the new last-day cell is blank. Also , as each month is contained in the same-sized grid (A3:N35) , sometimes the end-of-the-month date would need to go to the row below. M27 would become A33 I'd be happy to forward the wb itself for you to see the issue , if you wish. It''s very small , and I'm sure would be clearer if you could see it for yourself. Thanks for your help , I'm grateful. ^_^ Best Wishes. "Colin Hayes" wrote in message ... Hi Rick Sorry for not mentioning that it is dates we are working on. Thanks for getting back and for the new code. It works perfectly to remove the erroneous date at the beginning of the month now. The 'missing' last day of the month continues to be an issue. Due of course to each date being reduced by one the final 31st of the month , for example , is becoming the 30th , and the 31st is not being replaced. There would have needed to have been a 32nd in the original sheet for this to happen(!). This follows for all 12 sheets - the last date in each month is missing after running the macro. Complicating factor of course is the differing lengths of the months. It's easily corrected manually , of course , and would be hard (I think) programmatically to correct. Thanks again for you expertise. Best Wishes In article , Rick Rothstein (MVP - VB) writes First off, you never mentioned dates in your original question. As a matter of fact, you specifically requested blanking out the cell if the "number" became negative... a date can never be negative. Hence, the reason I and Per gave you the code we did. Okay, now, as to your question given your latest posting... the code below will blank out the cell if the date it contains is the first of the month; however, I do not understand what you mean when you say the "last day of the month is missing altogether". If you are blanking out a cell when one is subtracted from the first of the month, then there will be no date (only an empty cell) to subtract one from in order to get the last day of the month; and if you start with the end of the month, then my code (and I'm pretty sure Per's code) will properly decrement it by one. So, can you clarify what you meant, or what you were looking to have happen, when you said that? Sub DecreaseByOne() Dim WS As Worksheet Dim CL As Range Application.ScreenUpdating = False For Each WS In Worksheets For Each CL In WS.Range("A3:N35") If IsDate(CL.Value) Then If Day(CL.Value) = 1 Then CL = "" Else CL.Value = CL.Value - 1 End If End If Next Next Application.ScreenUpdating = True End Sub Rick "Colin Hayes" wrote in message ... In article , Rick Rothstein (MVP - VB) writes Just out of curiosity, what anomalous results are you getting? If you are running my code, it will leave you on the same sheet you call the macro from. However, if you to explicitly go to sheet #1, add this.... Worksheets(1).Select at the end of the macro (probably before setting ScreenUpdating to True would make the most sense). Rick Hi Rick I'm applying the code to dates in each of the sheets. I had to change the IsNumeric element to IsDate , of course. It works OK but the first day of the month , which should become 0 and therefore be blanked out is actually reading 31. The last day of the month is missing altogether. This is repeated over all 12 sheets. I can live with this and amend manually , although it would be nice if it did the whole job. I know dates can get complicated. Best Wishes "Colin Hayes" wrote in message .. . Hi guys OK thanks - that's done the trick. It does give a couple of anomalous results , I find , but I can work them out. Thanks again. B TW Per , could you add a line to the end of the code you gave to select the first sheet in the wb on completion of the macro? ^_^ In article , Rick Rothstein (MVP - VB) writes Give this a try... Sub DecreaseByOne() Dim WS As Worksheet Dim CL As Range Application.ScreenUpdating = False For Each WS In Worksheets For Each CL In WS.Range("A3:N35") If IsNumeric(CL.Value) Then CL.Value = CL.Value - 1 If CL.Value < 0 Then CL = "" Next Next Application.ScreenUpdating = True End Sub Rick "Colin Hayes" wrote in message .. . Hi I need some help constructing a macro. I have a workbook with 12 sheets , A - L. In each sheet , range A3 - N35 contains a series of numbers , with some cells being blank. I need a macro which will go through each sheet , reducing each number it finds by 1. Blank cells should be left blank. Where a cell becomes negative after subtracting 1 from it , this should be made blank too. Can someone help? Grateful for any assistance. Best Wishes |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reducing numbers by one in defined areas over several sheets
I didn't forget about you, just been busy for the last couple of days and
only been jumping in to the newsgroups sporadically. Let's see if I fully understand what is going on. First, I presume you made a typo when you said... For example, G27 in April was showing 30 as the last day of the month. After running the macro , it is now showing 29. For this reason , the next cell in order (I27) would need the 30th to be added to it. Instead of I27, you meant H27, right? If not, you will need to tell us the column order that the cells are processed in. Second, so I am completely clear, are you saying that whenever a date is currently the last date of the month and my code decrements it by one, a new entry is placed into the "next cell in order" and that new entry is the same last day of the month that was in the previous cell before I just decremented it? In other words, once a last day of the month is in your worksheet, it will always be there in some column??? Or do these dates get changed and updated outside of the macro code you are asking for? Rick "Colin Hayes" wrote in message ... Rick? In article , Colin Hayes writes In article , Rick Rothstein (MVP - VB) writes I don't think it will be hard to code once we understand what is controlling it. What would need to be in a cell in order that decrementing it by one would produce the last day of a month? What is in the cell initially? A blank? Or, what is in the column and/or row that indicates a date should be in the cell and, if that cell is blank, the date should be the last day of the month? How will you know which month that is? In other words, right now, you can look at your table and tell it needs to have the last date placed in a cell... what is it about the layout of your data that lets you know to put that last day of the month in what I am assuming is a blank cell? Rick Hi Rick It's certainly an interesting one. What's happening is the each cell containing a date (ddmmyyyy) is being deprecated by one. When the date becomes zero in the case of the first day of the month , the macro is blanking that cell. In the case of the last day of the month , the macro would need to add a date to compensate for the reduction that has taken place. For example , G27 in April was showing 30 as the last day of the month. After running the macro , it is now showing 29. For this reason , the next cell in order (I27) would need the 30th to be added to it. This applies to all the months. The complicating thing is that all the months are of differing lengths , and also that the cell that needs to have a new end-of-month added would change across the months. Presently the new last-day cell is blank. Also , as each month is contained in the same-sized grid (A3:N35) , sometimes the end-of-the-month date would need to go to the row below. M27 would become A33 I'd be happy to forward the wb itself for you to see the issue , if you wish. It''s very small , and I'm sure would be clearer if you could see it for yourself. Thanks for your help , I'm grateful. ^_^ Best Wishes. "Colin Hayes" wrote in message ... Hi Rick Sorry for not mentioning that it is dates we are working on. Thanks for getting back and for the new code. It works perfectly to remove the erroneous date at the beginning of the month now. The 'missing' last day of the month continues to be an issue. Due of course to each date being reduced by one the final 31st of the month , for example , is becoming the 30th , and the 31st is not being replaced. There would have needed to have been a 32nd in the original sheet for this to happen(!). This follows for all 12 sheets - the last date in each month is missing after running the macro. Complicating factor of course is the differing lengths of the months. It's easily corrected manually , of course , and would be hard (I think) programmatically to correct. Thanks again for you expertise. Best Wishes In article , Rick Rothstein (MVP - VB) writes First off, you never mentioned dates in your original question. As a matter of fact, you specifically requested blanking out the cell if the "number" became negative... a date can never be negative. Hence, the reason I and Per gave you the code we did. Okay, now, as to your question given your latest posting... the code below will blank out the cell if the date it contains is the first of the month; however, I do not understand what you mean when you say the "last day of the month is missing altogether". If you are blanking out a cell when one is subtracted from the first of the month, then there will be no date (only an empty cell) to subtract one from in order to get the last day of the month; and if you start with the end of the month, then my code (and I'm pretty sure Per's code) will properly decrement it by one. So, can you clarify what you meant, or what you were looking to have happen, when you said that? Sub DecreaseByOne() Dim WS As Worksheet Dim CL As Range Application.ScreenUpdating = False For Each WS In Worksheets For Each CL In WS.Range("A3:N35") If IsDate(CL.Value) Then If Day(CL.Value) = 1 Then CL = "" Else CL.Value = CL.Value - 1 End If End If Next Next Application.ScreenUpdating = True End Sub Rick "Colin Hayes" wrote in message ... In article , Rick Rothstein (MVP - VB) writes Just out of curiosity, what anomalous results are you getting? If you are running my code, it will leave you on the same sheet you call the macro from. However, if you to explicitly go to sheet #1, add this.... Worksheets(1).Select at the end of the macro (probably before setting ScreenUpdating to True would make the most sense). Rick Hi Rick I'm applying the code to dates in each of the sheets. I had to change the IsNumeric element to IsDate , of course. It works OK but the first day of the month , which should become 0 and therefore be blanked out is actually reading 31. The last day of the month is missing altogether. This is repeated over all 12 sheets. I can live with this and amend manually , although it would be nice if it did the whole job. I know dates can get complicated. Best Wishes "Colin Hayes" wrote in message . .. Hi guys OK thanks - that's done the trick. It does give a couple of anomalous results , I find , but I can work them out. Thanks again. B TW Per , could you add a line to the end of the code you gave to select the first sheet in the wb on completion of the macro? ^_^ In article , Rick Rothstein (MVP - VB) writes Give this a try... Sub DecreaseByOne() Dim WS As Worksheet Dim CL As Range Application.ScreenUpdating = False For Each WS In Worksheets For Each CL In WS.Range("A3:N35") If IsNumeric(CL.Value) Then CL.Value = CL.Value - 1 If CL.Value < 0 Then CL = "" Next Next Application.ScreenUpdating = True End Sub Rick "Colin Hayes" wrote in message . .. Hi I need some help constructing a macro. I have a workbook with 12 sheets , A - L. In each sheet , range A3 - N35 contains a series of numbers , with some cells being blank. I need a macro which will go through each sheet , reducing each number it finds by 1. Blank cells should be left blank. Where a cell becomes negative after subtracting 1 from it , this should be made blank too. Can someone help? Grateful for any assistance. Best Wishes |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reducing numbers by one in defined areas over several sheets
Hi Rick
OK thanks for getting back. The next cell containing a date in the example below is indeed I27. The dates jump a cell on each occasion. It's just the way they are spaced in my wb. For the last-day updating issue : Where , for example , the cell counting Monday 31st has now become Monday 30th after running the macro , a new cell has to be established to be Tuesday 31st. This is always 2 cells to the right , except in the case that it goes beyond column N and therefore outside the grid. In that case it goes to column A and drops 6 rows. It's a way of updating a calendar ready for the following year. Thanks again Rick. ^_^ In article , Rick Rothstein (MVP - VB) writes I didn't forget about you, just been busy for the last couple of days and only been jumping in to the newsgroups sporadically. Let's see if I fully understand what is going on. First, I presume you made a typo when you said... For example, G27 in April was showing 30 as the last day of the month. After running the macro , it is now showing 29. For this reason , the next cell in order (I27) would need the 30th to be added to it. Instead of I27, you meant H27, right? If not, you will need to tell us the column order that the cells are processed in. Second, so I am completely clear, are you saying that whenever a date is currently the last date of the month and my code decrements it by one, a new entry is placed into the "next cell in order" and that new entry is the same last day of the month that was in the previous cell before I just decremented it? In other words, once a last day of the month is in your worksheet, it will always be there in some column??? Or do these dates get changed and updated outside of the macro code you are asking for? Rick "Colin Hayes" wrote in message ... Rick? In article , Colin Hayes writes In article , Rick Rothstein (MVP - VB) writes I don't think it will be hard to code once we understand what is controlling it. What would need to be in a cell in order that decrementing it by one would produce the last day of a month? What is in the cell initially? A blank? Or, what is in the column and/or row that indicates a date should be in the cell and, if that cell is blank, the date should be the last day of the month? How will you know which month that is? In other words, right now, you can look at your table and tell it needs to have the last date placed in a cell... what is it about the layout of your data that lets you know to put that last day of the month in what I am assuming is a blank cell? Rick Hi Rick It's certainly an interesting one. What's happening is the each cell containing a date (ddmmyyyy) is being deprecated by one. When the date becomes zero in the case of the first day of the month , the macro is blanking that cell. In the case of the last day of the month , the macro would need to add a date to compensate for the reduction that has taken place. For example , G27 in April was showing 30 as the last day of the month. After running the macro , it is now showing 29. For this reason , the next cell in order (I27) would need the 30th to be added to it. This applies to all the months. The complicating thing is that all the months are of differing lengths , and also that the cell that needs to have a new end-of-month added would change across the months. Presently the new last-day cell is blank. Also , as each month is contained in the same-sized grid (A3:N35) , sometimes the end-of-the-month date would need to go to the row below. M27 would become A33 I'd be happy to forward the wb itself for you to see the issue , if you wish. It''s very small , and I'm sure would be clearer if you could see it for yourself. Thanks for your help , I'm grateful. ^_^ Best Wishes. "Colin Hayes" wrote in message ... Hi Rick Sorry for not mentioning that it is dates we are working on. Thanks for getting back and for the new code. It works perfectly to remove the erroneous date at the beginning of the month now. The 'missing' last day of the month continues to be an issue. Due of course to each date being reduced by one the final 31st of the month , for example , is becoming the 30th , and the 31st is not being replaced. There would have needed to have been a 32nd in the original sheet for this to happen(!). This follows for all 12 sheets - the last date in each month is missing after running the macro. Complicating factor of course is the differing lengths of the months. It's easily corrected manually , of course , and would be hard (I think) programmatically to correct. Thanks again for you expertise. Best Wishes In article , Rick Rothstein (MVP - VB) writes First off, you never mentioned dates in your original question. As a matter of fact, you specifically requested blanking out the cell if the "number" became negative... a date can never be negative. Hence, the reason I and Per gave you the code we did. Okay, now, as to your question given your latest posting... the code below will blank out the cell if the date it contains is the first of the month; however, I do not understand what you mean when you say the "last day of the month is missing altogether". If you are blanking out a cell when one is subtracted from the first of the month, then there will be no date (only an empty cell) to subtract one from in order to get the last day of the month; and if you start with the end of the month, then my code (and I'm pretty sure Per's code) will properly decrement it by one. So, can you clarify what you meant, or what you were looking to have happen, when you said that? Sub DecreaseByOne() Dim WS As Worksheet Dim CL As Range Application.ScreenUpdating = False For Each WS In Worksheets For Each CL In WS.Range("A3:N35") If IsDate(CL.Value) Then If Day(CL.Value) = 1 Then CL = "" Else CL.Value = CL.Value - 1 End If End If Next Next Application.ScreenUpdating = True End Sub Rick "Colin Hayes" wrote in message .. . In article , Rick Rothstein (MVP - VB) writes Just out of curiosity, what anomalous results are you getting? If you are running my code, it will leave you on the same sheet you call the macro from. However, if you to explicitly go to sheet #1, add this.... Worksheets(1).Select at the end of the macro (probably before setting ScreenUpdating to True would make the most sense). Rick Hi Rick I'm applying the code to dates in each of the sheets. I had to change the IsNumeric element to IsDate , of course. It works OK but the first day of the month , which should become 0 and therefore be blanked out is actually reading 31. The last day of the month is missing altogether. This is repeated over all 12 sheets. I can live with this and amend manually , although it would be nice if it did the whole job. I know dates can get complicated. Best Wishes "Colin Hayes" wrote in message .. . Hi guys OK thanks - that's done the trick. It does give a couple of anomalous results , I find , but I can work them out. Thanks again. B TW Per , could you add a line to the end of the code you gave to select the first sheet in the wb on completion of the macro? ^_^ In article , Rick Rothstein (MVP - VB) writes Give this a try... Sub DecreaseByOne() Dim WS As Worksheet Dim CL As Range Application.ScreenUpdating = False For Each WS In Worksheets For Each CL In WS.Range("A3:N35") If IsNumeric(CL.Value) Then CL.Value = CL.Value - 1 If CL.Value < 0 Then CL = "" Next Next Application.ScreenUpdating = True End Sub Rick "Colin Hayes" wrote in message ... Hi I need some help constructing a macro. I have a workbook with 12 sheets , A - L. In each sheet , range A3 - N35 contains a series of numbers , with some cells being blank. I need a macro which will go through each sheet , reducing each number it finds by 1. Blank cells should be left blank. Where a cell becomes negative after subtracting 1 from it , this should be made blank too. Can someone help? Grateful for any assistance. Best Wishes |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 - How to print areas in separate sheets to 1 pdf file | Excel Discussion (Misc queries) | |||
Writing macro results to user defined areas within excel | Excel Discussion (Misc queries) | |||
how to add numbers from different areas in chart and importing forms from word | New Users to Excel | |||
Unselect all selected areas in all sheets | Excel Discussion (Misc queries) | |||
How do I set print areas for multiple sheets simultaneously? | Excel Discussion (Misc queries) |