Link to home
Start Free TrialLog in
Avatar of Tammi Rush
Tammi Rush

asked on

Excel Macro causing an incorrect entry on first line

I have a macro in an excel spreadsheet where I want it to fill in two columns based on the number entered in the boxes.  The first is the evaluation days,  the second shipping days.  When the user enters in 28 in the evaluation days I get an entry in the first box D3 that says shipping days.   Looks like the ship days column fills in correctly.  I've attached the spreadsheet as well and the macro is below.  Can you tell me what I've got wrong in the macro?

2nd question on this - I set the macro to stop at the last row where data should not go past the last row where data is entered on the A column, however it expends the D and E columns about 4-6 past the last row entered for the A column.  So I think I have something wrong there too.

Days Eval      Ship Days
Shipping Days      22
28      22
28      22
28      22
28      22
28      22
28      22
28      22
28      22
28      22
28      22
28      22
28      22
28      22
28      22
28      22
28      22
28      22
28      22
28      22
28      22
28      22
28      22
28      22
28      22
28      22
28      22
28      22

Macro:
Sub Days()
lastrow = ActiveSheet.UsedRange.Rows.Count
ActiveCell.FormulaR1C1 = "Days Eval"
Range("D3").Select
ActiveCell.FormulaR1C1 = InputBox("Number of Evaluation Days")
Range("D3").Select
Selection.AutoFill Destination:=Range("D3:D" & lastrow)
Range("D3:D" & lastrow).Select

lastrow = ActiveSheet.UsedRange.Rows.Count
ActiveCell.FormulaR1C1 = "Shipping Days"
Range("E3").Select
ActiveCell.FormulaR1C1 = InputBox("Number of Shipping Days")
Range("E3").Select
Selection.AutoFill Destination:=Range("E3:E" & lastrow)
Range("E3:E" & lastrow).Select
End Sub
Molding-Production-Bonus-Worksheet.xlsm
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image

Hi

Here is your solution for both questions

Sub Days()
lastrow = Range("A" & Rows.Count).End(xlUp).Row
ActiveCell.FormulaR1C1 = "Days Eval"
Range("D3").Select
ActiveCell.FormulaR1C1 = InputBox("Number of Evaluation Days")
Range("D3").Select
Selection.AutoFill Destination:=Range("D3:D" & lastrow)
Range("D3:D" & lastrow).Select

Range("E3").Select
ActiveCell.FormulaR1C1 = "Shipping Days"
ActiveCell.FormulaR1C1 = InputBox("Number of Shipping Days")
Range("E3").Select
Selection.AutoFill Destination:=Range("E3:E" & lastrow)
Range("E3:E" & lastrow).Select
End Sub

Open in new window


Although I don't like this type of coding as it could go havoc due to relying on activecell and select, I only corrected the mistake but it is not optimal coding.

chk the workbook.
gowflow
Molding-Production-Bonus-Worksheet.xlsm
Avatar of Tammi Rush
Tammi Rush

ASKER

Thanks for the answer.  How would you recommend I code this as the number of eval days and shipping days changes each month and I don't want the supervisors to have to enter these on the spreadsheet manually.
ASKER CERTIFIED SOLUTION
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
That was a very nice solution as I can do those entries no matter where the cursor is.  I appreciate the help.
Yes for sure this is what I meant but optimized ! you can't expect a lot from a user to hv to cover for all possibilities and have it fool proof as much as possible.

Please feel free to let me know if you need help in any other issue by posting a link to the question asked by either messaging me or if now in here.

gowflow