| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with vba code please
I have a form created in VBA. I have a txtbox1 = begin date and txtbox2 =
end date. On this form I also have an OK button On worksheet "summary" I have rows of data by date. Date is in A4:A500. I would like assistance is writing the code in the "OK" procedure that will copy data from SUMMARY based on input date range(txtbox1 and txtbox2) and paste into worksheet "results". thank you |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
help with vba code please
Try this and let me know if you have problems:
' Code written 8/24/2006 by CChickering for Mona Sheets("summary").Range("A1").AutoFilter Field:=1, Criteria1:="=" & UserForm1.TxtBox1, _ Operator:=xlAnd, Criteria2:="<=" & UserForm1.TxtBox2 Sheets("summary").Range("A1").CurrentRegion.Specia lCells(xlCellTypeVisible).Copy _ Sheets("results").Range("A1") Sheets("summary").Range("A1").AutoFilter Charles Mona wrote: I have a form created in VBA. I have a txtbox1 = begin date and txtbox2 = end date. On this form I also have an OK button On worksheet "summary" I have rows of data by date. Date is in A4:A500. I would like assistance is writing the code in the "OK" procedure that will copy data from SUMMARY based on input date range(txtbox1 and txtbox2) and paste into worksheet "results". thank you |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
help with vba code please
charles-
thanks so much. Is there another way to accomplish this without using the Filter option. The filter option works OK but I have a lot of criteria on the form (check boxes) that will also affect the data range. I did not mention because I thought I could get started with some basic code and then add to it. thanks! "Die_Another_Day" wrote: Try this and let me know if you have problems: ' Code written 8/24/2006 by CChickering for Mona Sheets("summary").Range("A1").AutoFilter Field:=1, Criteria1:="=" & UserForm1.TxtBox1, _ Operator:=xlAnd, Criteria2:="<=" & UserForm1.TxtBox2 Sheets("summary").Range("A1").CurrentRegion.Specia lCells(xlCellTypeVisible).Copy _ Sheets("results").Range("A1") Sheets("summary").Range("A1").AutoFilter Charles Mona wrote: I have a form created in VBA. I have a txtbox1 = begin date and txtbox2 = end date. On this form I also have an OK button On worksheet "summary" I have rows of data by date. Date is in A4:A500. I would like assistance is writing the code in the "OK" procedure that will copy data from SUMMARY based on input date range(txtbox1 and txtbox2) and paste into worksheet "results". thank you |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
help with vba code please
The advantage of AutoFilter is speed. You can loop through all the used
cells fairly easily too. Dim lRow as Long 'Last Row Dim cnt as Long lRow = Sheets("Summary").Range("A" & Sheets("Summary").Rows.Count).End(xlUp) For cnt = 1 to lRow If Range("A" & cnt) = 'YourCriteria' Then 'Your Code End If Next Is that what you were after? Charles Mona wrote: charles- thanks so much. Is there another way to accomplish this without using the Filter option. The filter option works OK but I have a lot of criteria on the form (check boxes) that will also affect the data range. I did not mention because I thought I could get started with some basic code and then add to it. thanks! "Die_Another_Day" wrote: Try this and let me know if you have problems: ' Code written 8/24/2006 by CChickering for Mona Sheets("summary").Range("A1").AutoFilter Field:=1, Criteria1:="=" & UserForm1.TxtBox1, _ Operator:=xlAnd, Criteria2:="<=" & UserForm1.TxtBox2 Sheets("summary").Range("A1").CurrentRegion.Specia lCells(xlCellTypeVisible).Copy _ Sheets("results").Range("A1") Sheets("summary").Range("A1").AutoFilter Charles Mona wrote: I have a form created in VBA. I have a txtbox1 = begin date and txtbox2 = end date. On this form I also have an OK button On worksheet "summary" I have rows of data by date. Date is in A4:A500. I would like assistance is writing the code in the "OK" procedure that will copy data from SUMMARY based on input date range(txtbox1 and txtbox2) and paste into worksheet "results". thank you |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
help with vba code please
yes,but........
I am not a vb expert and I am having difficulties with the code. I will try to explain: I have a form that you enter txtbegindate and txtenddate. I also have "checkboxes" to even further limit the data requested. The checkboxes are, for example: apple total orange total fruit total So if I selected a date range between 01/01/2006 and 02/01/2006 and checked ONLY orange total and fruit total I would like the results sheet to look something like date orange total fruit total 01/01/2006 300 800 01/02/2006 100 500 ...... 02/02/2006 400 600 thanks again for you help. I have been looking around for examples and I am coming up empty? "Die_Another_Day" wrote: The advantage of AutoFilter is speed. You can loop through all the used cells fairly easily too. Dim lRow as Long 'Last Row Dim cnt as Long lRow = Sheets("Summary").Range("A" & Sheets("Summary").Rows.Count).End(xlUp) For cnt = 1 to lRow If Range("A" & cnt) = 'YourCriteria' Then 'Your Code End If Next Is that what you were after? Charles Mona wrote: charles- thanks so much. Is there another way to accomplish this without using the Filter option. The filter option works OK but I have a lot of criteria on the form (check boxes) that will also affect the data range. I did not mention because I thought I could get started with some basic code and then add to it. thanks! "Die_Another_Day" wrote: Try this and let me know if you have problems: ' Code written 8/24/2006 by CChickering for Mona Sheets("summary").Range("A1").AutoFilter Field:=1, Criteria1:="=" & UserForm1.TxtBox1, _ Operator:=xlAnd, Criteria2:="<=" & UserForm1.TxtBox2 Sheets("summary").Range("A1").CurrentRegion.Specia lCells(xlCellTypeVisible).Copy _ Sheets("results").Range("A1") Sheets("summary").Range("A1").AutoFilter Charles Mona wrote: I have a form created in VBA. I have a txtbox1 = begin date and txtbox2 = end date. On this form I also have an OK button On worksheet "summary" I have rows of data by date. Date is in A4:A500. I would like assistance is writing the code in the "OK" procedure that will copy data from SUMMARY based on input date range(txtbox1 and txtbox2) and paste into worksheet "results". thank you |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
help with vba code please
Dim lRow as Long 'Last Row
Dim nRow as Long 'Next Row to copy to Dim cnt as Long lRow = Sheets("Summary").Range("A" & Sheets("Summary").Rows.Count).End(xlUp) With Sheets("Summary") For cnt = 1 to lRow If .Range("A" & cnt) = 'YourCriteria' Then nRow = Sheets("results").Range("A" & _ Sheets("results").Rows.Count).End(xlUp).Offset(1,0 ).Row .Range("A" & cnt).Copy Sheets("results").Range("A" & nRow) If CheckBox1.Value Then .Range("B" & cnt).Copy Sheets("results").Range("B" & nRow) End If End If Next End With Add code for more Checkboxes as needed, changing the Column Letter as you go. Charles Mona wrote: yes,but........ I am not a vb expert and I am having difficulties with the code. I will try to explain: I have a form that you enter txtbegindate and txtenddate. I also have "checkboxes" to even further limit the data requested. The checkboxes are, for example: apple total orange total fruit total So if I selected a date range between 01/01/2006 and 02/01/2006 and checked ONLY orange total and fruit total I would like the results sheet to look something like date orange total fruit total 01/01/2006 300 800 01/02/2006 100 500 ..... 02/02/2006 400 600 thanks again for you help. I have been looking around for examples and I am coming up empty? "Die_Another_Day" wrote: The advantage of AutoFilter is speed. You can loop through all the used cells fairly easily too. Dim lRow as Long 'Last Row Dim cnt as Long lRow = Sheets("Summary").Range("A" & Sheets("Summary").Rows.Count).End(xlUp) For cnt = 1 to lRow If Range("A" & cnt) = 'YourCriteria' Then 'Your Code End If Next Is that what you were after? Charles Mona wrote: charles- thanks so much. Is there another way to accomplish this without using the Filter option. The filter option works OK but I have a lot of criteria on the form (check boxes) that will also affect the data range. I did not mention because I thought I could get started with some basic code and then add to it. thanks! "Die_Another_Day" wrote: Try this and let me know if you have problems: ' Code written 8/24/2006 by CChickering for Mona Sheets("summary").Range("A1").AutoFilter Field:=1, Criteria1:="=" & UserForm1.TxtBox1, _ Operator:=xlAnd, Criteria2:="<=" & UserForm1.TxtBox2 Sheets("summary").Range("A1").CurrentRegion.Specia lCells(xlCellTypeVisible).Copy _ Sheets("results").Range("A1") Sheets("summary").Range("A1").AutoFilter Charles Mona wrote: I have a form created in VBA. I have a txtbox1 = begin date and txtbox2 = end date. On this form I also have an OK button On worksheet "summary" I have rows of data by date. Date is in A4:A500. I would like assistance is writing the code in the "OK" procedure that will copy data from SUMMARY based on input date range(txtbox1 and txtbox2) and paste into worksheet "results". thank you |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
help with vba code please
charles- here is a copy of my code:
As you probably can tell , I am a bit confused! lRow has a value of 39082 which is 12/31/2006. Should lRow be the # of rows not the value of the last cell? Also, I am having problems with: If .Range("A" & cnt) = "txtbegindate" Then It needs to find "= txtbegindate and <= txtenddate" but I don't know how to code it correctly. Thanks so much! Private Sub cmdOK_Click() Dim lRow As Long 'last row Dim nRow As Long 'Next row to copy Dim cnt As Long lRow = Sheets("Summary").Range("A" & _ Sheets("Summary").Rows.count).End(xlUp) With Sheets("summary") For count = 1 To lRow If .Range("A" & cnt) = "txtbegindate" Then nRow = Sheets("Results").Range("A" & _ Sheets("Results").Rows.count).End(xlUp).Offset(1, 0).Row .Range("A" & cnt).Copy Sheets("Results").Range("A" & nRow) If chktotalLoss.Value Then .Range("J" & count).Copy Sheets("results").Range("J" & nRow) End If End If Next End With "Mona" wrote: I have a form created in VBA. I have a txtbox1 = begin date and txtbox2 = end date. On this form I also have an OK button On worksheet "summary" I have rows of data by date. Date is in A4:A500. I would like assistance is writing the code in the "OK" procedure that will copy data from SUMMARY based on input date range(txtbox1 and txtbox2) and paste into worksheet "results". thank you |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
help with vba code please
Private Sub cmdOK_Click()
Dim lRow As Long 'last row Dim nRow As Long 'Next row to copy Dim cnt As Long lRow = Sheets("Summary").Range("A" & _ Sheets("Summary").Rows.count).End(xlUp).Row With Sheets("summary") For count = 1 To lRow If .Range("A" & cnt) = txtbegindate And _ .Range("A" & cnt) <= txtenddate Then nRow = Sheets("Results").Range("A" & _ Sheets("Results").Rows.count).End(xlUp).Offset(1, 0).Row .Range("A" & cnt).Copy Sheets("Results").Range("A" & nRow) If chktotalLoss.Value Then .Range("J" & count).Copy Sheets("results").Range("J" & nRow) End If End If Next End With End Sub You were right about the lRow. I forgot the .row on the end of the statement. Let me know if you need anything else. Charles Chickering Mona wrote: charles- here is a copy of my code: As you probably can tell , I am a bit confused! lRow has a value of 39082 which is 12/31/2006. Should lRow be the # of rows not the value of the last cell? Also, I am having problems with: If .Range("A" & cnt) = "txtbegindate" Then It needs to find "= txtbegindate and <= txtenddate" but I don't know how to code it correctly. Thanks so much! Private Sub cmdOK_Click() Dim lRow As Long 'last row Dim nRow As Long 'Next row to copy Dim cnt As Long lRow = Sheets("Summary").Range("A" & _ Sheets("Summary").Rows.count).End(xlUp) With Sheets("summary") For count = 1 To lRow If .Range("A" & cnt) = "txtbegindate" Then nRow = Sheets("Results").Range("A" & _ Sheets("Results").Rows.count).End(xlUp).Offset(1, 0).Row .Range("A" & cnt).Copy Sheets("Results").Range("A" & nRow) If chktotalLoss.Value Then .Range("J" & count).Copy Sheets("results").Range("J" & nRow) End If End If Next End With "Mona" wrote: I have a form created in VBA. I have a txtbox1 = begin date and txtbox2 = end date. On this form I also have an OK button On worksheet "summary" I have rows of data by date. Date is in A4:A500. I would like assistance is writing the code in the "OK" procedure that will copy data from SUMMARY based on input date range(txtbox1 and txtbox2) and paste into worksheet "results". thank you |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
help with vba code please
Charles-
You have been a huge help! I really appreciate it. I am so close to getting what I need except....... The code I have looks like it is doing what I need but the values for "txtbegindate" and "txtenddate" are not being passed. I even tried to use frmDataPull.txtbegindate and frmDataPull.txtenddate. Do I need to declare these variables somehow so that all of my subs recognize? thanks! "Die_Another_Day" wrote: Private Sub cmdOK_Click() Dim lRow As Long 'last row Dim nRow As Long 'Next row to copy Dim cnt As Long lRow = Sheets("Summary").Range("A" & _ Sheets("Summary").Rows.count).End(xlUp).Row With Sheets("summary") For count = 1 To lRow If .Range("A" & cnt) = txtbegindate And _ .Range("A" & cnt) <= txtenddate Then nRow = Sheets("Results").Range("A" & _ Sheets("Results").Rows.count).End(xlUp).Offset(1, 0).Row .Range("A" & cnt).Copy Sheets("Results").Range("A" & nRow) If chktotalLoss.Value Then .Range("J" & count).Copy Sheets("results").Range("J" & nRow) End If End If Next End With End Sub You were right about the lRow. I forgot the .row on the end of the statement. Let me know if you need anything else. Charles Chickering Mona wrote: charles- here is a copy of my code: As you probably can tell , I am a bit confused! lRow has a value of 39082 which is 12/31/2006. Should lRow be the # of rows not the value of the last cell? Also, I am having problems with: If .Range("A" & cnt) = "txtbegindate" Then It needs to find "= txtbegindate and <= txtenddate" but I don't know how to code it correctly. Thanks so much! Private Sub cmdOK_Click() Dim lRow As Long 'last row Dim nRow As Long 'Next row to copy Dim cnt As Long lRow = Sheets("Summary").Range("A" & _ Sheets("Summary").Rows.count).End(xlUp) With Sheets("summary") For count = 1 To lRow If .Range("A" & cnt) = "txtbegindate" Then nRow = Sheets("Results").Range("A" & _ Sheets("Results").Rows.count).End(xlUp).Offset(1, 0).Row .Range("A" & cnt).Copy Sheets("Results").Range("A" & nRow) If chktotalLoss.Value Then .Range("J" & count).Copy Sheets("results").Range("J" & nRow) End If End If Next End With "Mona" wrote: I have a form created in VBA. I have a txtbox1 = begin date and txtbox2 = end date. On this form I also have an OK button On worksheet "summary" I have rows of data by date. Date is in A4:A500. I would like assistance is writing the code in the "OK" procedure that will copy data from SUMMARY based on input date range(txtbox1 and txtbox2) and paste into worksheet "results". thank you |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
help with vba code please
Is txtbegindate and txtenddate the caption or name your textbox? If all
else fails just email me the workbook @ my posting address. Charles Mona wrote: Charles- You have been a huge help! I really appreciate it. I am so close to getting what I need except....... The code I have looks like it is doing what I need but the values for "txtbegindate" and "txtenddate" are not being passed. I even tried to use frmDataPull.txtbegindate and frmDataPull.txtenddate. Do I need to declare these variables somehow so that all of my subs recognize? thanks! "Die_Another_Day" wrote: Private Sub cmdOK_Click() Dim lRow As Long 'last row Dim nRow As Long 'Next row to copy Dim cnt As Long lRow = Sheets("Summary").Range("A" & _ Sheets("Summary").Rows.count).End(xlUp).Row With Sheets("summary") For count = 1 To lRow If .Range("A" & cnt) = txtbegindate And _ .Range("A" & cnt) <= txtenddate Then nRow = Sheets("Results").Range("A" & _ Sheets("Results").Rows.count).End(xlUp).Offset(1, 0).Row .Range("A" & cnt).Copy Sheets("Results").Range("A" & nRow) If chktotalLoss.Value Then .Range("J" & count).Copy Sheets("results").Range("J" & nRow) End If End If Next End With End Sub You were right about the lRow. I forgot the .row on the end of the statement. Let me know if you need anything else. Charles Chickering Mona wrote: charles- here is a copy of my code: As you probably can tell , I am a bit confused! lRow has a value of 39082 which is 12/31/2006. Should lRow be the # of rows not the value of the last cell? Also, I am having problems with: If .Range("A" & cnt) = "txtbegindate" Then It needs to find "= txtbegindate and <= txtenddate" but I don't know how to code it correctly. Thanks so much! Private Sub cmdOK_Click() Dim lRow As Long 'last row Dim nRow As Long 'Next row to copy Dim cnt As Long lRow = Sheets("Summary").Range("A" & _ Sheets("Summary").Rows.count).End(xlUp) With Sheets("summary") For count = 1 To lRow If .Range("A" & cnt) = "txtbegindate" Then nRow = Sheets("Results").Range("A" & _ Sheets("Results").Rows.count).End(xlUp).Offset(1, 0).Row .Range("A" & cnt).Copy Sheets("Results").Range("A" & nRow) If chktotalLoss.Value Then .Range("J" & count).Copy Sheets("results").Range("J" & nRow) End If End If Next End With "Mona" wrote: I have a form created in VBA. I have a txtbox1 = begin date and txtbox2 = end date. On this form I also have an OK button On worksheet "summary" I have rows of data by date. Date is in A4:A500. I would like assistance is writing the code in the "OK" procedure that will copy data from SUMMARY based on input date range(txtbox1 and txtbox2) and paste into worksheet "results". thank you |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
help with vba code please
They are the names of my textboxes.
"Die_Another_Day" wrote: Is txtbegindate and txtenddate the caption or name your textbox? If all else fails just email me the workbook @ my posting address. Charles Mona wrote: Charles- You have been a huge help! I really appreciate it. I am so close to getting what I need except....... The code I have looks like it is doing what I need but the values for "txtbegindate" and "txtenddate" are not being passed. I even tried to use frmDataPull.txtbegindate and frmDataPull.txtenddate. Do I need to declare these variables somehow so that all of my subs recognize? thanks! "Die_Another_Day" wrote: Private Sub cmdOK_Click() Dim lRow As Long 'last row Dim nRow As Long 'Next row to copy Dim cnt As Long lRow = Sheets("Summary").Range("A" & _ Sheets("Summary").Rows.count).End(xlUp).Row With Sheets("summary") For count = 1 To lRow If .Range("A" & cnt) = txtbegindate And _ .Range("A" & cnt) <= txtenddate Then nRow = Sheets("Results").Range("A" & _ Sheets("Results").Rows.count).End(xlUp).Offset(1, 0).Row .Range("A" & cnt).Copy Sheets("Results").Range("A" & nRow) If chktotalLoss.Value Then .Range("J" & count).Copy Sheets("results").Range("J" & nRow) End If End If Next End With End Sub You were right about the lRow. I forgot the .row on the end of the statement. Let me know if you need anything else. Charles Chickering Mona wrote: charles- here is a copy of my code: As you probably can tell , I am a bit confused! lRow has a value of 39082 which is 12/31/2006. Should lRow be the # of rows not the value of the last cell? Also, I am having problems with: If .Range("A" & cnt) = "txtbegindate" Then It needs to find "= txtbegindate and <= txtenddate" but I don't know how to code it correctly. Thanks so much! Private Sub cmdOK_Click() Dim lRow As Long 'last row Dim nRow As Long 'Next row to copy Dim cnt As Long lRow = Sheets("Summary").Range("A" & _ Sheets("Summary").Rows.count).End(xlUp) With Sheets("summary") For count = 1 To lRow If .Range("A" & cnt) = "txtbegindate" Then nRow = Sheets("Results").Range("A" & _ Sheets("Results").Rows.count).End(xlUp).Offset(1, 0).Row .Range("A" & cnt).Copy Sheets("Results").Range("A" & nRow) If chktotalLoss.Value Then .Range("J" & count).Copy Sheets("results").Range("J" & nRow) End If End If Next End With "Mona" wrote: I have a form created in VBA. I have a txtbox1 = begin date and txtbox2 = end date. On this form I also have an OK button On worksheet "summary" I have rows of data by date. Date is in A4:A500. I would like assistance is writing the code in the "OK" procedure that will copy data from SUMMARY based on input date range(txtbox1 and txtbox2) and paste into worksheet "results". thank you |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
help with vba code please
Sorry , I should have added this:
Private Sub UserForm_Initialize() txtbegindate.Value = "" txtenddate.Value = "" chkOrangeLoss = False chkAppleLoss = False chkTotalLoss = False End Sub "Mona" wrote: I have a form created in VBA. I have a txtbox1 = begin date and txtbox2 = end date. On this form I also have an OK button On worksheet "summary" I have rows of data by date. Date is in A4:A500. I would like assistance is writing the code in the "OK" procedure that will copy data from SUMMARY based on input date range(txtbox1 and txtbox2) and paste into worksheet "results". thank you |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
| Drop Down/List w/Code and Definition, only code entered when selec | Excel Worksheet Functions | |||
| run code on opening workbook and apply code to certain sheets | Excel Programming | |||
| stubborn Excel crash when editing code with code, one solution | Excel Programming | |||
| option buttons run Click code when value is changed via VBA code | Excel Programming | |||
Hybrid Mode
