| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
date code error, help would be appreciated!
morning,
i having some issues with the below code. it is supposed to search for the date today minus 12 months, and then copy all rows in between to a new sheet. any suggestions would be greatly appreciated! thanks very much on this very cold and foggy morning!! mike Private Sub CommandButton2_Click() Dim Rng As range Dim rCell As range Dim copyRng As range Dim destRng As range Dim mydate As Date Dim sh As Worksheet Dim CalcMode As Long Dim arr As Variant Dim res As Variant Dim mymonth As Date mydate = Date mymonth = Month(mydate) Set sh = Sheets("Sheet 1") Set Rng = sh.range("A5:A100") Set destRng = Sheets("Sheet 2").range("A2") res = mydate - mymonth If res = "" Then Exit Sub arr = Split(res, " ") With application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With For Each rCell In Rng.Cells If Not IsError(application.Match(rCell.Value, arr, 0)) Then If copyRng Is Nothing Then Set copyRng = rCell Else Set copyRng = Union(rCell, copyRng) End If End If Next rCell If Not copyRng Is Nothing Then copyRng.EntireRow.Copy Destination:=destRng Else 'nothing found, do nothing End If With application .Calculation = CalcMode .ScreenUpdating = True End With End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
date code error, help would be appreciated!
Hi Mike,
I believe that the code you are trying to use was originally suggested by me for a very different purpose: to copy rows containing one of several words supplied by a user in response to an input box.Certainly, as used, the code is inappropriate for your current purpose. In order to assist you, you will need to explain the layout of your data and your purpose: is the data sorted sequentially; are all rows older than 1 year to be copied; is data to be appended to existing data on the target sheet, or is the existing data to be overwritten? --- Regards, Norman "mike" wrote in message ... morning, i having some issues with the below code. it is supposed to search for the date today minus 12 months, and then copy all rows in between to a new sheet. any suggestions would be greatly appreciated! thanks very much on this very cold and foggy morning!! mike Private Sub CommandButton2_Click() Dim Rng As range Dim rCell As range Dim copyRng As range Dim destRng As range Dim mydate As Date Dim sh As Worksheet Dim CalcMode As Long Dim arr As Variant Dim res As Variant Dim mymonth As Date mydate = Date mymonth = Month(mydate) Set sh = Sheets("Sheet 1") Set Rng = sh.range("A5:A100") Set destRng = Sheets("Sheet 2").range("A2") res = mydate - mymonth If res = "" Then Exit Sub arr = Split(res, " ") With application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With For Each rCell In Rng.Cells If Not IsError(application.Match(rCell.Value, arr, 0)) Then If copyRng Is Nothing Then Set copyRng = rCell Else Set copyRng = Union(rCell, copyRng) End If End If Next rCell If Not copyRng Is Nothing Then copyRng.EntireRow.Copy Destination:=destRng Else 'nothing found, do nothing End If With application .Calculation = CalcMode .ScreenUpdating = True End With End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
date code error, help would be appreciated!
Norman,
you are correct. it is the formula you provided. basically, i have a sheet. it contains a number of columns. my overall purpose of the macro is to..... search in column A for all dates that are up to and including one year ago from today's date. then in column B there is an amount value. i wish to then add the amounts in column from today back to a year ago today. if it easier to copy them to a new sheet then this would suffice. i hope you can help. thanks mike i wish to add the amount value th "Norman Jones" wrote: Hi Mike, I believe that the code you are trying to use was originally suggested by me for a very different purpose: to copy rows containing one of several words supplied by a user in response to an input box.Certainly, as used, the code is inappropriate for your current purpose. In order to assist you, you will need to explain the layout of your data and your purpose: is the data sorted sequentially; are all rows older than 1 year to be copied; is data to be appended to existing data on the target sheet, or is the existing data to be overwritten? --- Regards, Norman "mike" wrote in message ... morning, i having some issues with the below code. it is supposed to search for the date today minus 12 months, and then copy all rows in between to a new sheet. any suggestions would be greatly appreciated! thanks very much on this very cold and foggy morning!! mike Private Sub CommandButton2_Click() Dim Rng As range Dim rCell As range Dim copyRng As range Dim destRng As range Dim mydate As Date Dim sh As Worksheet Dim CalcMode As Long Dim arr As Variant Dim res As Variant Dim mymonth As Date mydate = Date mymonth = Month(mydate) Set sh = Sheets("Sheet 1") Set Rng = sh.range("A5:A100") Set destRng = Sheets("Sheet 2").range("A2") res = mydate - mymonth If res = "" Then Exit Sub arr = Split(res, " ") With application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With For Each rCell In Rng.Cells If Not IsError(application.Match(rCell.Value, arr, 0)) Then If copyRng Is Nothing Then Set copyRng = rCell Else Set copyRng = Union(rCell, copyRng) End If End If Next rCell If Not copyRng Is Nothing Then copyRng.EntireRow.Copy Destination:=destRng Else 'nothing found, do nothing End If With application .Calculation = CalcMode .ScreenUpdating = True End With End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
date code error, help would be appreciated!
Hi Mike,
Are the rows sorted in date order? Is there additional data in columns C == that should be copied? If your purpose is to extract information for a one year period, why not use the autofilter feature to extract all rows between your required dates. You could then use the SubTotal function to sum the filtered rows, e.g.: =SUBTOTAL(9,B:B) --- Regards, Norman "mike" wrote in message ... Norman, you are correct. it is the formula you provided. basically, i have a sheet. it contains a number of columns. my overall purpose of the macro is to..... search in column A for all dates that are up to and including one year ago from today's date. then in column B there is an amount value. i wish to then add the amounts in column from today back to a year ago today. if it easier to copy them to a new sheet then this would suffice. i hope you can help. thanks mike i wish to add the amount value th "Norman Jones" wrote: Hi Mike, I believe that the code you are trying to use was originally suggested by me for a very different purpose: to copy rows containing one of several words supplied by a user in response to an input box.Certainly, as used, the code is inappropriate for your current purpose. In order to assist you, you will need to explain the layout of your data and your purpose: is the data sorted sequentially; are all rows older than 1 year to be copied; is data to be appended to existing data on the target sheet, or is the existing data to be overwritten? --- Regards, Norman "mike" wrote in message ... morning, i having some issues with the below code. it is supposed to search for the date today minus 12 months, and then copy all rows in between to a new sheet. any suggestions would be greatly appreciated! thanks very much on this very cold and foggy morning!! mike Private Sub CommandButton2_Click() Dim Rng As range Dim rCell As range Dim copyRng As range Dim destRng As range Dim mydate As Date Dim sh As Worksheet Dim CalcMode As Long Dim arr As Variant Dim res As Variant Dim mymonth As Date mydate = Date mymonth = Month(mydate) Set sh = Sheets("Sheet 1") Set Rng = sh.range("A5:A100") Set destRng = Sheets("Sheet 2").range("A2") res = mydate - mymonth If res = "" Then Exit Sub arr = Split(res, " ") With application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With For Each rCell In Rng.Cells If Not IsError(application.Match(rCell.Value, arr, 0)) Then If copyRng Is Nothing Then Set copyRng = rCell Else Set copyRng = Union(rCell, copyRng) End If End If Next rCell If Not copyRng Is Nothing Then copyRng.EntireRow.Copy Destination:=destRng Else 'nothing found, do nothing End If With application .Calculation = CalcMode .ScreenUpdating = True End With End Sub |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
date code error, help would be appreciated!
Norman can Autofilter be used in macros?
There is data in other columns, but i need only 2 other columns at most. the rows are sorted by date order but are not sequential, eg the dates will run in month order, but days may be skipped or doubled up, where there were no entries for some days or more on another day. i am trying to format it in a macro, because i have wrote something similar, using your help before, which looks for a certain word in column a then copies all information relating to that word, ie all rows that contain the word, and the relevant information in those rows, to a new sheet, which is then automatically mailed to someone. what i am trying to do now is collate the number values to complile reports and email them automatically. this is just the start, my manager also asked if it would be possible to analyse the amounts on a quarter on quarter basis to see if there is a reduction in frequency of the values. i hope you can help, i think im getting a bit out of my depth!! might have to go and buy a textbook for this stuff at lunch!!! thanks again mike "Norman Jones" wrote: Hi Mike, Are the rows sorted in date order? Is there additional data in columns C == that should be copied? If your purpose is to extract information for a one year period, why not use the autofilter feature to extract all rows between your required dates. You could then use the SubTotal function to sum the filtered rows, e.g.: =SUBTOTAL(9,B:B) --- Regards, Norman "mike" wrote in message ... Norman, you are correct. it is the formula you provided. basically, i have a sheet. it contains a number of columns. my overall purpose of the macro is to..... search in column A for all dates that are up to and including one year ago from today's date. then in column B there is an amount value. i wish to then add the amounts in column from today back to a year ago today. if it easier to copy them to a new sheet then this would suffice. i hope you can help. thanks mike i wish to add the amount value th "Norman Jones" wrote: Hi Mike, I believe that the code you are trying to use was originally suggested by me for a very different purpose: to copy rows containing one of several words supplied by a user in response to an input box.Certainly, as used, the code is inappropriate for your current purpose. In order to assist you, you will need to explain the layout of your data and your purpose: is the data sorted sequentially; are all rows older than 1 year to be copied; is data to be appended to existing data on the target sheet, or is the existing data to be overwritten? --- Regards, Norman "mike" wrote in message ... morning, i having some issues with the below code. it is supposed to search for the date today minus 12 months, and then copy all rows in between to a new sheet. any suggestions would be greatly appreciated! thanks very much on this very cold and foggy morning!! mike Private Sub CommandButton2_Click() Dim Rng As range Dim rCell As range Dim copyRng As range Dim destRng As range Dim mydate As Date Dim sh As Worksheet Dim CalcMode As Long Dim arr As Variant Dim res As Variant Dim mymonth As Date mydate = Date mymonth = Month(mydate) Set sh = Sheets("Sheet 1") Set Rng = sh.range("A5:A100") Set destRng = Sheets("Sheet 2").range("A2") res = mydate - mymonth If res = "" Then Exit Sub arr = Split(res, " ") With application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With For Each rCell In Rng.Cells If Not IsError(application.Match(rCell.Value, arr, 0)) Then If copyRng Is Nothing Then Set copyRng = rCell Else Set copyRng = Union(rCell, copyRng) End If End If Next rCell If Not copyRng Is Nothing Then copyRng.EntireRow.Copy Destination:=destRng Else 'nothing found, do nothing End If With application .Calculation = CalcMode .ScreenUpdating = True End With End Sub |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
date code error, help would be appreciated!
Hi Mike,
Norman can Autofilter be used in macros? Certainly! Turn on the macro recorder, perform the required operations manually. This will provide you with base code that can be edited to improve efficiency and render it appropriate for general application. If you require any assistance with such editing, post back with specifics. From your description, I think that the autofilter will best meet your requirements. --- Regards, Norman "mike" wrote in message ... Norman can Autofilter be used in macros? There is data in other columns, but i need only 2 other columns at most. the rows are sorted by date order but are not sequential, eg the dates will run in month order, but days may be skipped or doubled up, where there were no entries for some days or more on another day. i am trying to format it in a macro, because i have wrote something similar, using your help before, which looks for a certain word in column a then copies all information relating to that word, ie all rows that contain the word, and the relevant information in those rows, to a new sheet, which is then automatically mailed to someone. what i am trying to do now is collate the number values to complile reports and email them automatically. this is just the start, my manager also asked if it would be possible to analyse the amounts on a quarter on quarter basis to see if there is a reduction in frequency of the values. i hope you can help, i think im getting a bit out of my depth!! might have to go and buy a textbook for this stuff at lunch!!! thanks again mike "Norman Jones" wrote: Hi Mike, Are the rows sorted in date order? Is there additional data in columns C == that should be copied? If your purpose is to extract information for a one year period, why not use the autofilter feature to extract all rows between your required dates. You could then use the SubTotal function to sum the filtered rows, e.g.: =SUBTOTAL(9,B:B) --- Regards, Norman "mike" wrote in message ... Norman, you are correct. it is the formula you provided. basically, i have a sheet. it contains a number of columns. my overall purpose of the macro is to..... search in column A for all dates that are up to and including one year ago from today's date. then in column B there is an amount value. i wish to then add the amounts in column from today back to a year ago today. if it easier to copy them to a new sheet then this would suffice. i hope you can help. thanks mike i wish to add the amount value th "Norman Jones" wrote: Hi Mike, I believe that the code you are trying to use was originally suggested by me for a very different purpose: to copy rows containing one of several words supplied by a user in response to an input box.Certainly, as used, the code is inappropriate for your current purpose. In order to assist you, you will need to explain the layout of your data and your purpose: is the data sorted sequentially; are all rows older than 1 year to be copied; is data to be appended to existing data on the target sheet, or is the existing data to be overwritten? --- Regards, Norman "mike" wrote in message ... morning, i having some issues with the below code. it is supposed to search for the date today minus 12 months, and then copy all rows in between to a new sheet. any suggestions would be greatly appreciated! thanks very much on this very cold and foggy morning!! mike Private Sub CommandButton2_Click() Dim Rng As range Dim rCell As range Dim copyRng As range Dim destRng As range Dim mydate As Date Dim sh As Worksheet Dim CalcMode As Long Dim arr As Variant Dim res As Variant Dim mymonth As Date mydate = Date mymonth = Month(mydate) Set sh = Sheets("Sheet 1") Set Rng = sh.range("A5:A100") Set destRng = Sheets("Sheet 2").range("A2") res = mydate - mymonth If res = "" Then Exit Sub arr = Split(res, " ") With application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With For Each rCell In Rng.Cells If Not IsError(application.Match(rCell.Value, arr, 0)) Then If copyRng Is Nothing Then Set copyRng = rCell Else Set copyRng = Union(rCell, copyRng) End If End If Next rCell If Not copyRng Is Nothing Then copyRng.EntireRow.Copy Destination:=destRng Else 'nothing found, do nothing End If With application .Calculation = CalcMode .ScreenUpdating = True End With End Sub |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
date code error, help would be appreciated!
Norman,
No luck with recording the auto-filter, do you think you could have a stab at the code? if you can give me a little push towards the right direction, i may be able to figure the rest out for myself. thanks again mike "Norman Jones" wrote: Hi Mike, Norman can Autofilter be used in macros? Certainly! Turn on the macro recorder, perform the required operations manually. This will provide you with base code that can be edited to improve efficiency and render it appropriate for general application. If you require any assistance with such editing, post back with specifics. From your description, I think that the autofilter will best meet your requirements. --- Regards, Norman "mike" wrote in message ... Norman can Autofilter be used in macros? There is data in other columns, but i need only 2 other columns at most. the rows are sorted by date order but are not sequential, eg the dates will run in month order, but days may be skipped or doubled up, where there were no entries for some days or more on another day. i am trying to format it in a macro, because i have wrote something similar, using your help before, which looks for a certain word in column a then copies all information relating to that word, ie all rows that contain the word, and the relevant information in those rows, to a new sheet, which is then automatically mailed to someone. what i am trying to do now is collate the number values to complile reports and email them automatically. this is just the start, my manager also asked if it would be possible to analyse the amounts on a quarter on quarter basis to see if there is a reduction in frequency of the values. i hope you can help, i think im getting a bit out of my depth!! might have to go and buy a textbook for this stuff at lunch!!! thanks again mike "Norman Jones" wrote: Hi Mike, Are the rows sorted in date order? Is there additional data in columns C == that should be copied? If your purpose is to extract information for a one year period, why not use the autofilter feature to extract all rows between your required dates. You could then use the SubTotal function to sum the filtered rows, e.g.: =SUBTOTAL(9,B:B) --- Regards, Norman "mike" wrote in message ... Norman, you are correct. it is the formula you provided. basically, i have a sheet. it contains a number of columns. my overall purpose of the macro is to..... search in column A for all dates that are up to and including one year ago from today's date. then in column B there is an amount value. i wish to then add the amounts in column from today back to a year ago today. if it easier to copy them to a new sheet then this would suffice. i hope you can help. thanks mike i wish to add the amount value th "Norman Jones" wrote: Hi Mike, I believe that the code you are trying to use was originally suggested by me for a very different purpose: to copy rows containing one of several words supplied by a user in response to an input box.Certainly, as used, the code is inappropriate for your current purpose. In order to assist you, you will need to explain the layout of your data and your purpose: is the data sorted sequentially; are all rows older than 1 year to be copied; is data to be appended to existing data on the target sheet, or is the existing data to be overwritten? --- Regards, Norman "mike" wrote in message ... morning, i having some issues with the below code. it is supposed to search for the date today minus 12 months, and then copy all rows in between to a new sheet. any suggestions would be greatly appreciated! thanks very much on this very cold and foggy morning!! mike Private Sub CommandButton2_Click() Dim Rng As range Dim rCell As range Dim copyRng As range Dim destRng As range Dim mydate As Date Dim sh As Worksheet Dim CalcMode As Long Dim arr As Variant Dim res As Variant Dim mymonth As Date mydate = Date mymonth = Month(mydate) Set sh = Sheets("Sheet 1") Set Rng = sh.range("A5:A100") Set destRng = Sheets("Sheet 2").range("A2") res = mydate - mymonth If res = "" Then Exit Sub arr = Split(res, " ") With application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With For Each rCell In Rng.Cells If Not IsError(application.Match(rCell.Value, arr, 0)) Then If copyRng Is Nothing Then Set copyRng = rCell Else Set copyRng = Union(rCell, copyRng) End If End If Next rCell If Not copyRng Is Nothing Then copyRng.EntireRow.Copy Destination:=destRng Else 'nothing found, do nothing End If With application .Calculation = CalcMode .ScreenUpdating = True End With End Sub |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
date code error, help would be appreciated!
Hi Mike,
No luck with recording the auto-filter, Post the code that you got when setting up the autofilter and I will try to help. --- Regards, Norman "mike" wrote in message ... Norman, No luck with recording the auto-filter, do you think you could have a stab at the code? if you can give me a little push towards the right direction, i may be able to figure the rest out for myself. thanks again mike "Norman Jones" wrote: Hi Mike, Norman can Autofilter be used in macros? Certainly! Turn on the macro recorder, perform the required operations manually. This will provide you with base code that can be edited to improve efficiency and render it appropriate for general application. If you require any assistance with such editing, post back with specifics. From your description, I think that the autofilter will best meet your requirements. --- Regards, Norman "mike" wrote in message ... Norman can Autofilter be used in macros? There is data in other columns, but i need only 2 other columns at most. the rows are sorted by date order but are not sequential, eg the dates will run in month order, but days may be skipped or doubled up, where there were no entries for some days or more on another day. i am trying to format it in a macro, because i have wrote something similar, using your help before, which looks for a certain word in column a then copies all information relating to that word, ie all rows that contain the word, and the relevant information in those rows, to a new sheet, which is then automatically mailed to someone. what i am trying to do now is collate the number values to complile reports and email them automatically. this is just the start, my manager also asked if it would be possible to analyse the amounts on a quarter on quarter basis to see if there is a reduction in frequency of the values. i hope you can help, i think im getting a bit out of my depth!! might have to go and buy a textbook for this stuff at lunch!!! thanks again mike "Norman Jones" wrote: Hi Mike, Are the rows sorted in date order? Is there additional data in columns C == that should be copied? If your purpose is to extract information for a one year period, why not use the autofilter feature to extract all rows between your required dates. You could then use the SubTotal function to sum the filtered rows, e.g.: =SUBTOTAL(9,B:B) --- Regards, Norman "mike" wrote in message ... Norman, you are correct. it is the formula you provided. basically, i have a sheet. it contains a number of columns. my overall purpose of the macro is to..... search in column A for all dates that are up to and including one year ago from today's date. then in column B there is an amount value. i wish to then add the amounts in column from today back to a year ago today. if it easier to copy them to a new sheet then this would suffice. i hope you can help. thanks mike i wish to add the amount value th "Norman Jones" wrote: Hi Mike, I believe that the code you are trying to use was originally suggested by me for a very different purpose: to copy rows containing one of several words supplied by a user in response to an input box.Certainly, as used, the code is inappropriate for your current purpose. In order to assist you, you will need to explain the layout of your data and your purpose: is the data sorted sequentially; are all rows older than 1 year to be copied; is data to be appended to existing data on the target sheet, or is the existing data to be overwritten? --- Regards, Norman "mike" wrote in message ... morning, i having some issues with the below code. it is supposed to search for the date today minus 12 months, and then copy all rows in between to a new sheet. any suggestions would be greatly appreciated! thanks very much on this very cold and foggy morning!! mike Private Sub CommandButton2_Click() Dim Rng As range Dim rCell As range Dim copyRng As range Dim destRng As range Dim mydate As Date Dim sh As Worksheet Dim CalcMode As Long Dim arr As Variant Dim res As Variant Dim mymonth As Date mydate = Date mymonth = Month(mydate) Set sh = Sheets("Sheet 1") Set Rng = sh.range("A5:A100") Set destRng = Sheets("Sheet 2").range("A2") res = mydate - mymonth If res = "" Then Exit Sub arr = Split(res, " ") With application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With For Each rCell In Rng.Cells If Not IsError(application.Match(rCell.Value, arr, 0)) Then If copyRng Is Nothing Then Set copyRng = rCell Else Set copyRng = Union(rCell, copyRng) End If End If Next rCell If Not copyRng Is Nothing Then copyRng.EntireRow.Copy Destination:=destRng Else 'nothing found, do nothing End If With application .Calculation = CalcMode .ScreenUpdating = True End With End Sub |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
date code error, help would be appreciated!
Norman,
this is the code below, i did what i normally do.... select row, data, autofilter. but the arrows didnt show. also, i am a little confused now, how i can filter between today and a year ago today using filter. Sub Macro3() ' ' Macro3 Macro ' Macro recorded 09/12/2005 by mike ' ' Rows("4:4").Select Selection.autofilter range("A4").Select End Sub thanks mike "Norman Jones" wrote: Hi Mike, No luck with recording the auto-filter, Post the code that you got when setting up the autofilter and I will try to help. --- Regards, Norman "mike" wrote in message ... Norman, No luck with recording the auto-filter, do you think you could have a stab at the code? if you can give me a little push towards the right direction, i may be able to figure the rest out for myself. thanks again mike "Norman Jones" wrote: Hi Mike, Norman can Autofilter be used in macros? Certainly! Turn on the macro recorder, perform the required operations manually. This will provide you with base code that can be edited to improve efficiency and render it appropriate for general application. If you require any assistance with such editing, post back with specifics. From your description, I think that the autofilter will best meet your requirements. --- Regards, Norman "mike" wrote in message ... Norman can Autofilter be used in macros? There is data in other columns, but i need only 2 other columns at most. the rows are sorted by date order but are not sequential, eg the dates will run in month order, but days may be skipped or doubled up, where there were no entries for some days or more on another day. i am trying to format it in a macro, because i have wrote something similar, using your help before, which looks for a certain word in column a then copies all information relating to that word, ie all rows that contain the word, and the relevant information in those rows, to a new sheet, which is then automatically mailed to someone. what i am trying to do now is collate the number values to complile reports and email them automatically. this is just the start, my manager also asked if it would be possible to analyse the amounts on a quarter on quarter basis to see if there is a reduction in frequency of the values. i hope you can help, i think im getting a bit out of my depth!! might have to go and buy a textbook for this stuff at lunch!!! thanks again mike "Norman Jones" wrote: Hi Mike, Are the rows sorted in date order? Is there additional data in columns C == that should be copied? If your purpose is to extract information for a one year period, why not use the autofilter feature to extract all rows between your required dates. You could then use the SubTotal function to sum the filtered rows, e.g.: =SUBTOTAL(9,B:B) --- Regards, Norman "mike" wrote in message ... Norman, you are correct. it is the formula you provided. basically, i have a sheet. it contains a number of columns. my overall purpose of the macro is to..... search in column A for all dates that are up to and including one year ago from today's date. then in column B there is an amount value. i wish to then add the amounts in column from today back to a year ago today. if it easier to copy them to a new sheet then this would suffice. i hope you can help. thanks mike i wish to add the amount value th "Norman Jones" wrote: Hi Mike, I believe that the code you are trying to use was originally suggested by me for a very different purpose: to copy rows containing one of several words supplied by a user in response to an input box.Certainly, as used, the code is inappropriate for your current purpose. In order to assist you, you will need to explain the layout of your data and your purpose: is the data sorted sequentially; are all rows older than 1 year to be copied; is data to be appended to existing data on the target sheet, or is the existing data to be overwritten? --- Regards, Norman "mike" wrote in message ... morning, i having some issues with the below code. it is supposed to search for the date today minus 12 months, and then copy all rows in between to a new sheet. any suggestions would be greatly appreciated! thanks very much on this very cold and foggy morning!! mike Private Sub CommandButton2_Click() Dim Rng As range Dim rCell As range Dim copyRng As range Dim destRng As range Dim mydate As Date Dim sh As Worksheet Dim CalcMode As Long Dim arr As Variant Dim res As Variant Dim mymonth As Date mydate = Date mymonth = Month(mydate) Set sh = Sheets("Sheet 1") Set Rng = sh.range("A5:A100") Set destRng = Sheets("Sheet 2").range("A2") res = mydate - mymonth If res = "" Then Exit Sub arr = Split(res, " ") With application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With For Each rCell In Rng.Cells If Not IsError(application.Match(rCell.Value, arr, 0)) Then If copyRng Is Nothing Then Set copyRng = rCell Else Set copyRng = Union(rCell, copyRng) End If End If Next rCell If Not copyRng Is Nothing Then copyRng.EntireRow.Copy Destination:=destRng Else 'nothing found, do nothing End If With application .Calculation = CalcMode .ScreenUpdating = True End With End Sub |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
date code error, help would be appreciated!
Mike,
Does this meet you need? It assumes data is sorted in ascending date sequence and finds a start date = to your start date and end date <= to TODAY. It sums Column B between these rows. HTH Sub A() Dim StartDate As Long, EndDate As Long Dim Btotal As Double EndDate = CLng(Now()) StartDate = EndDate - 365 ' Above dates will be 09/12/04 to 09/12/05 inclusive (UK date format!) srow = Application.Match(StartDate, Range("a2:a2000"), 1) ' If "Match" date less then StartDate then begin at next date ( StartDate) If CLng(Cells(srow, 1)) < StartDate Then srow = srow + 1 ' Date will be less than or equal to Today's date erow = Application.Match(EndDate, Range("a2:a2000"), 1) 'Total data in column B Btotal = Application.Sum(Range("B" & srow & ":B" & erow)) End Sub "mike" wrote: Norman, this is the code below, i did what i normally do.... select row, data, autofilter. but the arrows didnt show. also, i am a little confused now, how i can filter between today and a year ago today using filter. Sub Macro3() ' ' Macro3 Macro ' Macro recorded 09/12/2005 by mike ' ' Rows("4:4").Select Selection.autofilter range("A4").Select End Sub thanks mike "Norman Jones" wrote: Hi Mike, No luck with recording the auto-filter, Post the code that you got when setting up the autofilter and I will try to help. --- Regards, Norman "mike" wrote in message ... Norman, No luck with recording the auto-filter, do you think you could have a stab at the code? if you can give me a little push towards the right direction, i may be able to figure the rest out for myself. thanks again mike "Norman Jones" wrote: Hi Mike, Norman can Autofilter be used in macros? Certainly! Turn on the macro recorder, perform the required operations manually. This will provide you with base code that can be edited to improve efficiency and render it appropriate for general application. If you require any assistance with such editing, post back with specifics. From your description, I think that the autofilter will best meet your requirements. --- Regards, Norman "mike" wrote in message ... Norman can Autofilter be used in macros? There is data in other columns, but i need only 2 other columns at most. the rows are sorted by date order but are not sequential, eg the dates will run in month order, but days may be skipped or doubled up, where there were no entries for some days or more on another day. i am trying to format it in a macro, because i have wrote something similar, using your help before, which looks for a certain word in column a then copies all information relating to that word, ie all rows that contain the word, and the relevant information in those rows, to a new sheet, which is then automatically mailed to someone. what i am trying to do now is collate the number values to complile reports and email them automatically. this is just the start, my manager also asked if it would be possible to analyse the amounts on a quarter on quarter basis to see if there is a reduction in frequency of the values. i hope you can help, i think im getting a bit out of my depth!! might have to go and buy a textbook for this stuff at lunch!!! thanks again mike "Norman Jones" wrote: Hi Mike, Are the rows sorted in date order? Is there additional data in columns C == that should be copied? If your purpose is to extract information for a one year period, why not use the autofilter feature to extract all rows between your required dates. You could then use the SubTotal function to sum the filtered rows, e.g.: =SUBTOTAL(9,B:B) --- Regards, Norman "mike" wrote in message ... Norman, you are correct. it is the formula you provided. basically, i have a sheet. it contains a number of columns. my overall purpose of the macro is to..... search in column A for all dates that are up to and including one year ago from today's date. then in column B there is an amount value. i wish to then add the amounts in column from today back to a year ago today. if it easier to copy them to a new sheet then this would suffice. i hope you can help. thanks mike i wish to add the amount value th "Norman Jones" wrote: Hi Mike, I believe that the code you are trying to use was originally suggested by me for a very different purpose: to copy rows containing one of several words supplied by a user in response to an input box.Certainly, as used, the code is inappropriate for your current purpose. In order to assist you, you will need to explain the layout of your data and your purpose: is the data sorted sequentially; are all rows older than 1 year to be copied; is data to be appended to existing data on the target sheet, or is the existing data to be overwritten? --- Regards, Norman "mike" wrote in message ... morning, i having some issues with the below code. it is supposed to search for the date today minus 12 months, and then copy all rows in between to a new sheet. any suggestions would be greatly appreciated! thanks very much on this very cold and foggy morning!! mike Private Sub CommandButton2_Click() Dim Rng As range Dim rCell As range Dim copyRng As range Dim destRng As range Dim mydate As Date Dim sh As Worksheet Dim CalcMode As Long Dim arr As Variant Dim res As Variant Dim mymonth As Date mydate = Date mymonth = Month(mydate) Set sh = Sheets("Sheet 1") Set Rng = sh.range("A5:A100") Set destRng = Sheets("Sheet 2").range("A2") res = mydate - mymonth If res = "" Then Exit Sub arr = Split(res, " ") With application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With For Each rCell In Rng.Cells If Not IsError(application.Match(rCell.Value, arr, 0)) Then If copyRng Is Nothing Then Set copyRng = rCell Else Set copyRng = Union(rCell, copyRng) End If End If Next rCell If Not copyRng Is Nothing Then copyRng.EntireRow.Copy Destination:=destRng Else 'nothing found, do nothing End If With application .Calculation = CalcMode .ScreenUpdating = True End With End Sub |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
date code error, help would be appreciated!
Hi Mike,
Try: '============= Public Sub Tester002() With ActiveSheet <<==== CHANGE If .AutoFilterMode Then If .FilterMode Then .ShowAllData End If End If End With Range("A4").AutoFilter _ Field:=1, _ Criteria1:="=" & CLng(DateSerial(2004, 12, 9)) End Sub '<<============= --- Regards, Norman "mike" wrote in message ... Norman, this is the code below, i did what i normally do.... select row, data, autofilter. but the arrows didnt show. also, i am a little confused now, how i can filter between today and a year ago today using filter. Sub Macro3() ' ' Macro3 Macro ' Macro recorded 09/12/2005 by mike ' ' Rows("4:4").Select Selection.autofilter range("A4").Select End Sub thanks mike "Norman Jones" wrote: Hi Mike, No luck with recording the auto-filter, Post the code that you got when setting up the autofilter and I will try to help. --- Regards, Norman "mike" wrote in message ... Norman, No luck with recording the auto-filter, do you think you could have a stab at the code? if you can give me a little push towards the right direction, i may be able to figure the rest out for myself. thanks again mike "Norman Jones" wrote: Hi Mike, Norman can Autofilter be used in macros? Certainly! Turn on the macro recorder, perform the required operations manually. This will provide you with base code that can be edited to improve efficiency and render it appropriate for general application. If you require any assistance with such editing, post back with specifics. From your description, I think that the autofilter will best meet your requirements. --- Regards, Norman "mike" wrote in message ... Norman can Autofilter be used in macros? There is data in other columns, but i need only 2 other columns at most. the rows are sorted by date order but are not sequential, eg the dates will run in month order, but days may be skipped or doubled up, where there were no entries for some days or more on another day. i am trying to format it in a macro, because i have wrote something similar, using your help before, which looks for a certain word in column a then copies all information relating to that word, ie all rows that contain the word, and the relevant information in those rows, to a new sheet, which is then automatically mailed to someone. what i am trying to do now is collate the number values to complile reports and email them automatically. this is just the start, my manager also asked if it would be possible to analyse the amounts on a quarter on quarter basis to see if there is a reduction in frequency of the values. i hope you can help, i think im getting a bit out of my depth!! might have to go and buy a textbook for this stuff at lunch!!! thanks again mike "Norman Jones" wrote: Hi Mike, Are the rows sorted in date order? Is there additional data in columns C == that should be copied? If your purpose is to extract information for a one year period, why not use the autofilter feature to extract all rows between your required dates. You could then use the SubTotal function to sum the filtered rows, e.g.: =SUBTOTAL(9,B:B) --- Regards, Norman "mike" wrote in message ... Norman, you are correct. it is the formula you provided. basically, i have a sheet. it contains a number of columns. my overall purpose of the macro is to..... search in column A for all dates that are up to and including one year ago from today's date. then in column B there is an amount value. i wish to then add the amounts in column from today back to a year ago today. if it easier to copy them to a new sheet then this would suffice. i hope you can help. thanks mike i wish to add the amount value th "Norman Jones" wrote: Hi Mike, I believe that the code you are trying to use was originally suggested by me for a very different purpose: to copy rows containing one of several words supplied by a user in response to an input box.Certainly, as used, the code is inappropriate for your current purpose. In order to assist you, you will need to explain the layout of your data and your purpose: is the data sorted sequentially; are all rows older than 1 year to be copied; is data to be appended to existing data on the target sheet, or is the existing data to be overwritten? --- Regards, Norman "mike" wrote in message ... morning, i having some issues with the below code. it is supposed to search for the date today minus 12 months, and then copy all rows in between to a new sheet. any suggestions would be greatly appreciated! thanks very much on this very cold and foggy morning!! mike Private Sub CommandButton2_Click() Dim Rng As range Dim rCell As range Dim copyRng As range Dim destRng As range Dim mydate As Date Dim sh As Worksheet Dim CalcMode As Long Dim arr As Variant Dim res As Variant Dim mymonth As Date mydate = Date mymonth = Month(mydate) Set sh = Sheets("Sheet 1") Set Rng = sh.range("A5:A100") Set destRng = Sheets("Sheet 2").range("A2") res = mydate - mymonth If res = "" Then Exit Sub arr = Split(res, " ") With application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With For Each rCell In Rng.Cells If Not IsError(application.Match(rCell.Value, arr, 0)) Then If copyRng Is Nothing Then Set copyRng = rCell Else Set copyRng = Union(rCell, copyRng) End If End If Next rCell If Not copyRng Is Nothing Then copyRng.EntireRow.Copy Destination:=destRng Else 'nothing found, do nothing End If With application .Calculation = CalcMode .ScreenUpdating = True End With End Sub |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
date code error, help would be appreciated!
Hi Mike,
Better would be: '============= Public Sub Tester002() With ActiveSheet '<<==== CHANGE If .AutoFilterMode Then If .FilterMode Then .ShowAllData End If End If .Range("A4").AutoFilter _ Field:=1, _ Criteria1:="=" & CLng(DateSerial(2004, 12, 9)) End With End Sub '<<============= --- Regards, Norman "Norman Jones" wrote in message ... Hi Mike, Try: '============= Public Sub Tester002() With ActiveSheet <<==== CHANGE If .AutoFilterMode Then If .FilterMode Then .ShowAllData End If End If End With Range("A4").AutoFilter _ Field:=1, _ Criteria1:="=" & CLng(DateSerial(2004, 12, 9)) End Sub '<<============= --- Regards, Norman |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
date code error, help would be appreciated!
Toppers,
There is a type mismatch error in your code on this line... If CLng(Cells(srow, 1)) < StartDate Then srow = srow + 1 any suggestions mike "Toppers" wrote: Mike, Does this meet you need? It assumes data is sorted in ascending date sequence and finds a start date = to your start date and end date <= to TODAY. It sums Column B between these rows. HTH Sub A() Dim StartDate As Long, EndDate As Long Dim Btotal As Double EndDate = CLng(Now()) StartDate = EndDate - 365 ' Above dates will be 09/12/04 to 09/12/05 inclusive (UK date format!) srow = Application.Match(StartDate, Range("a2:a2000"), 1) ' If "Match" date less then StartDate then begin at next date ( StartDate) If CLng(Cells(srow, 1)) < StartDate Then srow = srow + 1 ' Date will be less than or equal to Today's date erow = Application.Match(EndDate, Range("a2:a2000"), 1) 'Total data in column B Btotal = Application.Sum(Range("B" & srow & ":B" & erow)) End Sub "mike" wrote: Norman, this is the code below, i did what i normally do.... select row, data, autofilter. but the arrows didnt show. also, i am a little confused now, how i can filter between today and a year ago today using filter. Sub Macro3() ' ' Macro3 Macro ' Macro recorded 09/12/2005 by mike ' ' Rows("4:4").Select Selection.autofilter range("A4").Select End Sub thanks mike "Norman Jones" wrote: Hi Mike, No luck with recording the auto-filter, Post the code that you got when setting up the autofilter and I will try to help. --- Regards, Norman "mike" wrote in message ... Norman, No luck with recording the auto-filter, do you think you could have a stab at the code? if you can give me a little push towards the right direction, i may be able to figure the rest out for myself. thanks again mike "Norman Jones" wrote: Hi Mike, Norman can Autofilter be used in macros? Certainly! Turn on the macro recorder, perform the required operations manually. This will provide you with base code that can be edited to improve efficiency and render it appropriate for general application. If you require any assistance with such editing, post back with specifics. From your description, I think that the autofilter will best meet your requirements. --- Regards, Norman "mike" wrote in message ... Norman can Autofilter be used in macros? There is data in other columns, but i need only 2 other columns at most. the rows are sorted by date order but are not sequential, eg the dates will run in month order, but days may be skipped or doubled up, where there were no entries for some days or more on another day. i am trying to format it in a macro, because i have wrote something similar, using your help before, which looks for a certain word in column a then copies all information relating to that word, ie all rows that contain the word, and the relevant information in those rows, to a new sheet, which is then automatically mailed to someone. what i am trying to do now is collate the number values to complile reports and email them automatically. this is just the start, my manager also asked if it would be possible to analyse the amounts on a quarter on quarter basis to see if there is a reduction in frequency of the values. i hope you can help, i think im getting a bit out of my depth!! might have to go and buy a textbook for this stuff at lunch!!! thanks again mike "Norman Jones" wrote: Hi Mike, Are the rows sorted in date order? Is there additional data in columns C == that should be copied? If your purpose is to extract information for a one year period, why not use the autofilter feature to extract all rows between your required dates. You could then use the SubTotal function to sum the filtered rows, e.g.: =SUBTOTAL(9,B:B) --- Regards, Norman "mike" wrote in message ... Norman, you are correct. it is the formula you provided. basically, i have a sheet. it contains a number of columns. my overall purpose of the macro is to..... search in column A for all dates that are up to and including one year ago from today's date. then in column B there is an amount value. i wish to then add the amounts in column from today back to a year ago today. if it easier to copy them to a new sheet then this would suffice. i hope you can help. thanks mike i wish to add the amount value th "Norman Jones" wrote: Hi Mike, I believe that the code you are trying to use was originally suggested by me for a very different purpose: to copy rows containing one of several words supplied by a user in response to an input box.Certainly, as used, the code is inappropriate for your current purpose. In order to assist you, you will need to explain the layout of your data and your purpose: is the data sorted sequentially; are all rows older than 1 year to be copied; is data to be appended to existing data on the target sheet, or is the existing data to be overwritten? --- Regards, Norman "mike" wrote in message ... morning, i having some issues with the below code. it is supposed to search for the date today minus 12 months, and then copy all rows in between to a new sheet. any suggestions would be greatly appreciated! thanks very much on this very cold and foggy morning!! mike Private Sub CommandButton2_Click() Dim Rng As range Dim rCell As range Dim copyRng As range Dim destRng As range Dim mydate As Date Dim sh As Worksheet Dim CalcMode As Long Dim arr As Variant Dim res As Variant Dim mymonth As Date mydate = Date mymonth = Month(mydate) Set sh = Sheets("Sheet 1") Set Rng = sh.range("A5:A100") Set destRng = Sheets("Sheet 2").range("A2") res = mydate - mymonth If res = "" Then Exit Sub arr = Split(res, " ") With application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With For Each rCell In Rng.Cells If Not IsError(application.Match(rCell.Value, arr, 0)) Then If copyRng Is Nothing Then Set copyRng = rCell Else Set copyRng = Union(rCell, copyRng) End If End If Next rCell If Not copyRng Is Nothing Then copyRng.EntireRow.Copy Destination:=destRng Else 'nothing found, do nothing End If With application .Calculation = CalcMode .ScreenUpdating = True End With End Sub |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
date code error, help would be appreciated!
Norman,
i saw toppers code and think this will be more workable. seein as you know my requirements, any suggestions as to the development of this code? thanks mike "Norman Jones" wrote: Hi Mike, Better would be: '============= Public Sub Tester002() With ActiveSheet '<<==== CHANGE If .AutoFilterMode Then If .FilterMode Then .ShowAllData End If End If .Range("A4").AutoFilter _ Field:=1, _ Criteria1:="=" & CLng(DateSerial(2004, 12, 9)) End With End Sub '<<============= --- Regards, Norman "Norman Jones" wrote in message ... Hi Mike, Try: '============= Public Sub Tester002() With ActiveSheet <<==== CHANGE If .AutoFilterMode Then If .FilterMode Then .ShowAllData End If End If End With Range("A4").AutoFilter _ Field:=1, _ Criteria1:="=" & CLng(DateSerial(2004, 12, 9)) End Sub '<<============= --- Regards, Norman |
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
date code error, help would be appreciated!
Hi Mike,
i saw toppers code and think this will be more workable. Fine. seein as you know my requirements, any suggestions as to the development of this code? I think that it would be more appropriate to refer this to Toppers. --- Regards, Norman "mike" wrote in message ... Norman, i saw toppers code and think this will be more workable. seein as you know my requirements, any suggestions as to the development of this code? thanks mike "Norman Jones" wrote: Hi Mike, Better would be: '============= Public Sub Tester002() With ActiveSheet '<<==== CHANGE If .AutoFilterMode Then If .FilterMode Then .ShowAllData End If End If .Range("A4").AutoFilter _ Field:=1, _ Criteria1:="=" & CLng(DateSerial(2004, 12, 9)) End With End Sub '<<============= --- Regards, Norman "Norman Jones" wrote in message ... Hi Mike, Try: '============= Public Sub Tester002() With ActiveSheet <<==== CHANGE If .AutoFilterMode Then If .FilterMode Then .ShowAllData End If End If End With Range("A4").AutoFilter _ Field:=1, _ Criteria1:="=" & CLng(DateSerial(2004, 12, 9)) End Sub '<<============= --- Regards, Norman |
|
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
date code error, help would be appreciated!
thanks norman
"Norman Jones" wrote: Hi Mike, i saw toppers code and think this will be more workable. Fine. seein as you know my requirements, any suggestions as to the development of this code? I think that it would be more appropriate to refer this to Toppers. --- Regards, Norman "mike" wrote in message ... Norman, i saw toppers code and think this will be more workable. seein as you know my requirements, any suggestions as to the development of this code? thanks mike "Norman Jones" wrote: Hi Mike, Better would be: '============= Public Sub Tester002() With ActiveSheet '<<==== CHANGE If .AutoFilterMode Then If .FilterMode Then .ShowAllData End If End If .Range("A4").AutoFilter _ Field:=1, _ Criteria1:="=" & CLng(DateSerial(2004, 12, 9)) End With End Sub '<<============= --- Regards, Norman "Norman Jones" wrote in message ... Hi Mike, Try: '============= Public Sub Tester002() With ActiveSheet <<==== CHANGE If .AutoFilterMode Then If .FilterMode Then .ShowAllData End If End If End With Range("A4").AutoFilter _ Field:=1, _ Criteria1:="=" & CLng(DateSerial(2004, 12, 9)) End Sub '<<============= --- Regards, Norman |
|
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
date code error, help would be appreciated!
|
|
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
date code error, help would be appreciated!
|
|
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
date code error, help would be appreciated!
Hi,
Try this which assumes data starts in column 1 HTH Sub CreateRanges() Dim Lastrow As Long Dim Lastcol As Integer Dim Col As Integer Dim ws1 As Worksheet Dim AgeSeries As Range Dim NameSeries As Range ' Add other ranges as required ..... Set ws1 = Worksheets("Sheet2") With ws1 Lastcol = .Cells(6, Columns.Count).End(xlToLeft).Column Lastrow = .Cells(Rows.Count, 1).End(xlUp).Row For Col = 1 To Lastcol Select Case .Cells(6, Col) 'if the name is age, then set the range to the stuff below Case "Age" Set AgeSeries = .Range(.Cells(7, Col), .Cells(Lastrow, Col)) Case "Name" Set NameSeries = .Range(.Cells(7, Col), .Cells(Lastrow, Col)) Case ("Date") ' ... End Select Next Col End With End Sub "mike" wrote: Toppers, Not possible, I am at work, and cannot send external email, nor access webmail. Any more code suggestions? thanks mike "Toppers" wrote: Mike, Suggests data which cannot be converted to an integer - what is in the Cells(srow,1)? If you prefer, mail your spreadsheet to me so I can check it through as it can be quicker than communicating via the news group. My e-mail is: "mike" wrote: Toppers, There is a type mismatch error in your code on this line... If CLng(Cells(srow, 1)) < StartDate Then srow = srow + 1 any suggestions mike "Toppers" wrote: Mike, Does this meet you need? It assumes data is sorted in ascending date sequence and finds a start date = to your start date and end date <= to TODAY. It sums Column B between these rows. HTH Sub A() Dim StartDate As Long, EndDate As Long Dim Btotal As Double EndDate = CLng(Now()) StartDate = EndDate - 365 ' Above dates will be 09/12/04 to 09/12/05 inclusive (UK date format!) srow = Application.Match(StartDate, Range("a2:a2000"), 1) ' If "Match" date less then StartDate then begin at next date ( StartDate) If CLng(Cells(srow, 1)) < StartDate Then srow = srow + 1 ' Date will be less than or equal to Today's date erow = Application.Match(EndDate, Range("a2:a2000"), 1) 'Total data in column B Btotal = Application.Sum(Range("B" & srow & ":B" & erow)) End Sub "mike" wrote: Norman, this is the code below, i did what i normally do.... select row, data, autofilter. but the arrows didnt show. also, i am a little confused now, how i can filter between today and a year ago today using filter. Sub Macro3() ' ' Macro3 Macro ' Macro recorded 09/12/2005 by mike ' ' Rows("4:4").Select Selection.autofilter range("A4").Select End Sub thanks mike "Norman Jones" wrote: Hi Mike, No luck with recording the auto-filter, Post the code that you got when setting up the autofilter and I will try to help. --- Regards, Norman "mike" wrote in message ... Norman, No luck with recording the auto-filter, do you think you could have a stab at the code? if you can give me a little push towards the right direction, i may be able to figure the rest out for myself. thanks again mike "Norman Jones" wrote: Hi Mike, Norman can Autofilter be used in macros? Certainly! Turn on the macro recorder, perform the required operations manually. This will provide you with base code that can be edited to improve efficiency and render it appropriate for general application. If you require any assistance with such editing, post back with specifics. From your description, I think that the autofilter will best meet your requirements. --- Regards, Norman "mike" wrote in message ... Norman can Autofilter be used in macros? There is data in other columns, but i need only 2 other columns at most. the rows are sorted by date order but are not sequential, eg the dates will run in month order, but days may be skipped or doubled up, where there were no entries for some days or more on another day. i am trying to format it in a macro, because i have wrote something similar, using your help before, which looks for a certain word in column a then copies all information relating to that word, ie all rows that contain the word, and the relevant information in those rows, to a new sheet, which is then automatically mailed to someone. what i am trying to do now is collate the number values to complile reports and email them automatically. this is just the start, my manager also asked if it would be possible to analyse the amounts on a quarter on quarter basis to see if there is a reduction in frequency of the values. i hope you can help, i think im getting a bit out of my depth!! might have to go and buy a textbook for this stuff at lunch!!! thanks again mike "Norman Jones" wrote: Hi Mike, Are the rows sorted in date order? Is there additional data in columns C == that should be copied? If your purpose is to extract information for a one year period, why not use the autofilter feature to extract all rows between your required dates. You could then use the SubTotal function to sum the filtered rows, e.g.: =SUBTOTAL(9,B:B) --- Regards, Norman "mike" wrote in message ... Norman, you are correct. it is the formula you provided. basically, i have a sheet. it contains a number of columns. my overall purpose of the macro is to..... search in column A for all dates that are up to and including one year ago from today's date. then in column B there is an amount value. i wish to then add the amounts in column from today back to a year ago today. if it easier to copy them to a new sheet then this would suffice. i hope you can help. thanks mike i wish to add the amount value th "Norman Jones" wrote: Hi Mike, I believe that the code you are trying to use was originally suggested by me for a very different purpose: to copy rows containing one of several words supplied by a user in response to an input box.Certainly, as used, the code is inappropriate for your current purpose. In order to assist you, you will need to explain the layout of your data and your purpose: is the data sorted sequentially; are all rows older than 1 year to be copied; is data to be appended to existing data on the target sheet, or is the existing data to be overwritten? --- Regards, Norman "mike" wrote in message ... morning, i having some issues with the below code. it is supposed to search for the date today minus 12 months, and then copy all rows in between to a new sheet. any suggestions would be greatly appreciated! thanks very much on this very cold and foggy morning!! mike Private Sub CommandButton2_Click() Dim Rng As range Dim rCell As range Dim copyRng As range Dim destRng As range Dim mydate As Date Dim sh As Worksheet Dim CalcMode As Long Dim arr As Variant Dim res As Variant Dim mymonth As Date mydate = Date mymonth = Month(mydate) Set sh = Sheets("Sheet 1") Set Rng = sh.range("A5:A100") Set destRng = Sheets("Sheet 2").range("A2") res = mydate - mymonth If res = "" Then Exit Sub arr = Split(res, " ") |
|
#21
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
date code error, help would be appreciated!
Ignore last post - reply to a completely different posting!!!
"Toppers" wrote: Hi, Try this which assumes data starts in column 1 HTH Sub CreateRanges() Dim Lastrow As Long Dim Lastcol As Integer Dim Col As Integer Dim ws1 As Worksheet Dim AgeSeries As Range Dim NameSeries As Range ' Add other ranges as required ..... Set ws1 = Worksheets("Sheet2") With ws1 Lastcol = .Cells(6, Columns.Count).End(xlToLeft).Column Lastrow = .Cells(Rows.Count, 1).End(xlUp).Row For Col = 1 To Lastcol Select Case .Cells(6, Col) 'if the name is age, then set the range to the stuff below Case "Age" Set AgeSeries = .Range(.Cells(7, Col), .Cells(Lastrow, Col)) Case "Name" Set NameSeries = .Range(.Cells(7, Col), .Cells(Lastrow, Col)) Case ("Date") ' ... End Select Next Col End With End Sub "mike" wrote: Toppers, Not possible, I am at work, and cannot send external email, nor access webmail. Any more code suggestions? thanks mike "Toppers" wrote: Mike, Suggests data which cannot be converted to an integer - what is in the Cells(srow,1)? If you prefer, mail your spreadsheet to me so I can check it through as it can be quicker than communicating via the news group. My e-mail is: "mike" wrote: Toppers, There is a type mismatch error in your code on this line... If CLng(Cells(srow, 1)) < StartDate Then srow = srow + 1 any suggestions mike "Toppers" wrote: Mike, Does this meet you need? It assumes data is sorted in ascending date sequence and finds a start date = to your start date and end date <= to TODAY. It sums Column B between these rows. HTH Sub A() Dim StartDate As Long, EndDate As Long Dim Btotal As Double EndDate = CLng(Now()) StartDate = EndDate - 365 ' Above dates will be 09/12/04 to 09/12/05 inclusive (UK date format!) srow = Application.Match(StartDate, Range("a2:a2000"), 1) ' If "Match" date less then StartDate then begin at next date ( StartDate) If CLng(Cells(srow, 1)) < StartDate Then srow = srow + 1 ' Date will be less than or equal to Today's date erow = Application.Match(EndDate, Range("a2:a2000"), 1) 'Total data in column B Btotal = Application.Sum(Range("B" & srow & ":B" & erow)) End Sub "mike" wrote: Norman, this is the code below, i did what i normally do.... select row, data, autofilter. but the arrows didnt show. also, i am a little confused now, how i can filter between today and a year ago today using filter. Sub Macro3() ' ' Macro3 Macro ' Macro recorded 09/12/2005 by mike ' ' Rows("4:4").Select Selection.autofilter range("A4").Select End Sub thanks mike "Norman Jones" wrote: Hi Mike, No luck with recording the auto-filter, Post the code that you got when setting up the autofilter and I will try to help. --- Regards, Norman "mike" wrote in message ... Norman, No luck with recording the auto-filter, do you think you could have a stab at the code? if you can give me a little push towards the right direction, i may be able to figure the rest out for myself. thanks again mike "Norman Jones" wrote: Hi Mike, Norman can Autofilter be used in macros? Certainly! Turn on the macro recorder, perform the required operations manually. This will provide you with base code that can be edited to improve efficiency and render it appropriate for general application. If you require any assistance with such editing, post back with specifics. From your description, I think that the autofilter will best meet your requirements. --- Regards, Norman "mike" wrote in message ... Norman can Autofilter be used in macros? There is data in other columns, but i need only 2 other columns at most. the rows are sorted by date order but are not sequential, eg the dates will run in month order, but days may be skipped or doubled up, where there were no entries for some days or more on another day. i am trying to format it in a macro, because i have wrote something similar, using your help before, which looks for a certain word in column a then copies all information relating to that word, ie all rows that contain the word, and the relevant information in those rows, to a new sheet, which is then automatically mailed to someone. what i am trying to do now is collate the number values to complile reports and email them automatically. this is just the start, my manager also asked if it would be possible to analyse the amounts on a quarter on quarter basis to see if there is a reduction in frequency of the values. i hope you can help, i think im getting a bit out of my depth!! might have to go and buy a textbook for this stuff at lunch!!! thanks again mike "Norman Jones" wrote: Hi Mike, Are the rows sorted in date order? Is there additional data in columns C == that should be copied? If your purpose is to extract information for a one year period, why not use the autofilter feature to extract all rows between your required dates. You could then use the SubTotal function to sum the filtered rows, e.g.: =SUBTOTAL(9,B:B) --- Regards, Norman "mike" wrote in message ... Norman, you are correct. it is the formula you provided. basically, i have a sheet. it contains a number of columns. my overall purpose of the macro is to..... search in column A for all dates that are up to and including one year ago from today's date. then in column B there is an amount value. i wish to then add the amounts in column from today back to a year ago today. if it easier to copy them to a new sheet then this would suffice. i hope you can help. thanks mike i wish to add the amount value th "Norman Jones" wrote: Hi Mike, I believe that the code you are trying to use was originally suggested by me for a very different purpose: to copy rows containing one of several words supplied by a user in response to an input box.Certainly, as used, the code is inappropriate for your current purpose. In order to assist you, you will need to explain the layout of your data and your purpose: is the data sorted sequentially; are all rows older than 1 year to be copied; is data to be appended to existing data on the target |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Any Help Appreciated | Excel Worksheet Functions | |||
| Help is much appreciated for- | Excel Discussion (Misc queries) | |||
| Help would be much appreciated! | Excel Worksheet Functions | |||
| Any help much appreciated | Excel Discussion (Misc queries) | |||
| Code Error - Run Time Error 5 (Disable Cut, Copy & Paste) | Excel Programming | |||
Linear Mode
