0

Developing using Excel 2007, but need to be compatible with 2003.

Problem:

Workbook has two sheets. Sheet two contains data, columns A thru M. Column C is formatted for Date values. Not all rows contain a value in column C.

Sheet One has 3 'Option Buttons (form Control), labeled Contract date, Effective Date, and End Date. When contract date is selected, Need data on sheet two, column C (Date is contained here) to be queried with a conditional filter... If date < today's date + 14 days ... If true, copy column C thru M of that row to Sheet One beginning at cell C13. Continue until all data rows have been tested.

If another 'Option Button' is selected, results from first query are replaced by results from second query.

Here is the code I have been working on, but it won't work.

Sub OptionButton1_Click() Application.ScreenUpdating = False TEMPLATE_SHEET = "Data_Input" Database_sheet = "Carrier" myzerorange = "C" & ActiveWindow.RangeSelection.Row & ":" & "M" & ActiveWindow.RangeSelection.Row mycompany = "C" & ActiveWindow.RangeSelection.Row mydate = "D" & ActiveWindow.RangeSelection.Row Database_sheet = ActiveSheet.Name DATABASE_RECORDS = Sheets(Database_sheet).Range("C2:C1000") Count_Row = 13 If Range(mycompany) <> "" Then If Range(mydate) <> "" Then 'Range(mydate) = contractdate If mydate < DateAdd("d", 14, "Today()") Then Range(myzerorange).Copy Sheets(TEMPLATE_SHEET).Select 'To identify the next blank row in the database sheet DATABASE_RECORDS = Sheets(TEMPLATE_SHEET).Range("C13:C1000") 'To identify the next blank row in the data_Input sheet For Each DBRECORD In DATABASE_RECORDS If DBRECORD <> "" Then Count_Row = Count_Row + 1 Next DBRECORD Sheets(TEMPLATE_SHEET).Range("C" & Count_Row).Select ActiveSheet.Paste 'Return to origin and check for another contract date Sheets(Database_sheet).Select Else End If Else End If End If Application.ScreenUpdating = True End Sub

This revised code still doesn't work... not sure what is hanging this up...

Sub CopyRowConditional() Application.ScreenUpdating = False Srownumber = 2 'source sheet row number "Data_Input" Trownumber = 13 'target sheet row number "Carrier" Do Srownumber = Srownumber + 1 Trownumber = Trownumber + 1 If Cells(Srownumber, 3).Value = "" Then Exit Do If Cells(Srownumber, 4).Value < DateAdd("d", 14, "Today()") Then For Column = 3 To 13 Sheets(template_sheet).Cells(Trownumber, Column).Value = >Sheets(Database_sheet).Cells(Srownumber, Column).Value Next Column End If End If Loop Application.ScreenUpdating = True End Sub
Improve this question
2
  • 2
    Questions here need to be a little more focused. You're asking for to many things at once. This isn't a write-your-code site. Show us what you've done. Commented Jun 21, 2011 at 16:21
  • Also please show us an example of what you have as input and what you want as output, e.g. a screenshot or a properly formatted table. Use code formatting if necessary to get a fix-width font. Few people care to read a description of a spreadsheet in prose. Commented Jun 21, 2011 at 18:48

2 Answers 2

Reset to default
1

This is what I have in mind for your problem. See the comments. You need to bind the button click to CopyRowConditional.

Sub CopyRowConditional() Do i = i + 1 If Cells(i, 1).Value = "" Then Exit Do ' this is to exit the loop when you reach an empty row If Cells(i, 1).Value = 10 Then ' this is where you put ' the condition that triggers the copy ' here I just put 10 as an example TargetRow = 4 ' this is where you need to determine how ' you select the row that will receive the ' data you're copying in the Target sheet ' If you need to check for an empty row ' you can add a Do ... Loop statement ' that stops when the row is good For j = 1 To 14 ' this is where you loop in to the 'column of the Source sheet Sheets("Target").Cells(TargetRow, j).Value = Sheets("Source").Cells(i, j).Value ' this is the line that actually does the copying, cell by cell ' if you need to change the column index, just write .Cells(i, j+ n).value ' where n is any offeset you need Next j End If Loop End Sub
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

ok, is it working out for you? Just looking at it, you need to think about how the target row changes at each iteration. May be you need to move the line Trownumber = Trownumber + 1 just after the line that tests the condition for the days but before the for loop.
Also, on your conditional statement, you might want to simplify that with " If Now - Cells(Srownumber,4).Value < 14 Then ...." Test it out. Also, just as a suggestion for style, it's probably easier to format your variables differently, especially if other people need to look at your code. Consider Srownumber vs. StartRowNumber vs. StartRow .
Did you hook up the event handler? For debugging purposes, go one line at a time by pressing F8 and see what happens. Put a breakpoint next to the first line to pause the code and add a watch to each variable. Also, remove the ScreenUpdating line for now.
Frenchie... Thank you for the help that you gave. I indeed got it working once I understood the logic that you pointed out. I also got the other two Option Buttons working. Still can't figure out how to post the code, as this site's editor is awful.
Ok, just keep going; programming seems almost impossible at the beginning but it's a lot easier than it looks when you get into it. As for the site's editor, it's only meant for a few lines at a time. Good luck and keep going!
0

This seems pretty easy to do so my guess is that you don't know VBA very well. Like others have said, the site is not about building your app; it's about people who build apps helping other people who build apps.

As a pointer, you should be able to post your question on a gigs site and get your project done in a matter of hours. Try craigslist if you want to meet in person or elance or if you're ok with virtual.

Hope this helps.

Improve this answer

9 Comments

I have been working on this problem and can not get it to work... the code that I wrote follows.
OK... the comment window will not allow me to post the code... it report negative chatacters left.
just send the part where you have the part where you have the filter for Contract Date. If we get one button working, you'll be able to figure out the others.
I paste the code into the comment window and it reports a negative character number. It will not Add the comment. What am I doing wrong here.
The code is longer than 600 characters... I have added the code to the original post via an edit.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.