VBA enter data using dialog

I need to modify this code to the following:

1. Enter the data starting on row 3 - it currently is entering on the wrong row.
2. After all entries are done
    * copy the row format (entire row) from row 50 to all rows just entered.
   *  enter formula "=AK3&IF(B3<>""," (","")&B3&IF(B3<>"","-","")&C3&IF(B3<>"",") ","")&" "&E3&" "&AL3&" "&AM3" in column A
   * total columns G through L

Sub ExpressEnter()

Application.ScreenUpdating = False
Set wsSummary = Worksheets("Dom Ex")
Sheets("Dom Ex").Activate

    Dim MyInput As Variant
    Dim iRow As Integer
    
    iRow = Range("AK" & Cells.Rows.Count).End(xlUp).Offset(1, 0).Row
    Do

        MyInput = InputBox("Enter Service")
        Range("AK" & iRow).Value = MyInput
        MyInput = InputBox("Low Weight")
        Range("B" & iRow).Value = MyInput
        MyInput = InputBox("High Weight")
        Range("C" & iRow).Value = MyInput
        MyInput = InputBox("Package Type: Letter, Pak, Box")
        Range("D" & iRow).Value = MyInput
        MyInput = InputBox("Zones (Separate by comma)")
        Range("E" & iRow).Value = MyInput
        iRow = iRow + 1
    Loop While MsgBox("Add another service?", vbYesNo, "Service Entry") = vbYes
    Rows("3:3").Select
    Selection.Delete Shift:=xlUp

End Sub

Open in new window

What-if-scenario-v2.xlsm
Euro5Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
OK as you say the data is being entered on the wrong row. That's easily fixed but first please describe what you are trying to do here.

 
       Range("A" & iRow - 1).Copy
        Range("A" & iRow).PasteSpecial xlPasteAll
        Range("B" & iRow & ":D" & iRow - 1).Copy
        Range("B" & iRow & ":D" & iRow).PasteSpecial xlPasteFormats
        Range("G" & iRow - 1 & ":AJ" & iRow - 1).Copy
        Range("G" & iRow & ":AJ" & iRow).PasteSpecial xlPasteAll

Open in new window

0
Euro5Author Commented:
@Martin, that was in an old version. It would copy the format from line to line as the user entered.
This was a tabled project and I can't remember all of the methods.
Please look at the ExpressEnter () ?
I thought that an easy method would be to paste in the format from row 50 instead.
Thank you!!
0
Martin LissOlder than dirtCommented:
The code I posted was from the workbook you posted in this thread. here is the complete sub from the workbook.

Sub ExpressEnter()

Application.ScreenUpdating = False
Set wsSummary = Worksheets("Dom Ex")
Sheets("Dom Ex").Activate

    Dim MyInput As Variant
    Dim iRow As Integer
    
    iRow = Range("AK" & Cells.Rows.Count).End(xlUp).Offset(1, 0).Row
    Do

        MyInput = InputBox("Enter Service")
        Range("AK" & iRow).Value = MyInput
        MyInput = InputBox("Low Weight")
        Range("B" & iRow).Value = MyInput
        MyInput = InputBox("High Weight")
        Range("C" & iRow).Value = MyInput
        MyInput = InputBox("Package Type: Letter, Pak, Box")
        Range("D" & iRow).Value = MyInput
        MyInput = InputBox("Zones (Separate by comma)")
        Range("E" & iRow).Value = MyInput
        iRow = iRow + 1
    Loop While MsgBox("Add another service?", vbYesNo, "Service Entry") = vbYes
    Rows("3:3").Select
    Selection.Delete Shift:=xlUp

End Sub

Open in new window

Let's assume that the sub doesn't exist. Please describe in detail, step by step, what you want the sub to do.
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Euro5Author Commented:
The dialog box must capture the
Service (column AK), Low Weight (column B), High Weight (column C), Package type (column D),
Zones (separated by comma) (column E) and enter this data starting in row 2.
The user would continue to enter by clicking YES to "Add another service" OR end by clicking NO.
If the user continues, the same information would be  looped through and entered into the next row.

When the user chooses NO (all data has been entered), I need the format copied from row 50 to the rows just entered.
Finally, I need a summary line for Columns G - L.
(You can see the what it should look like complete on the Ground and Intl tabs in the workbook.)

Does that help?
0
Martin LissOlder than dirtCommented:
Sorry I didn't get back to you sooner. You say "The dialog box must capture the Service (column AK), ... and enter this data starting in row 2". There is a heading in row 2 so I'm going to assume you mean row 3.
0
Martin LissOlder than dirtCommented:
This workbook just enters the data and I need more information about "a summary line for Columns G - L". Please enter some data, manually create whatever summary line(s) you need and repost the workbook.
28692983.xlsm
0
Euro5Author Commented:
The entries are working perfectly.
I entered the 'total' line that would need to go in.
28692983-totals.xlsm
0
Martin LissOlder than dirtCommented:
A couple of more questions:

Each time the ExpressEnter sub is run, do you want to first clear all the data that's already there?

For the total line on the Dom Ex sheet, It would make the coding easier if I could copy the total line from the Ground sheet. Will there always be one on the Ground sheet? Are the formulas (except for the number of rows) the same on both sheets?
0
Euro5Author Commented:
Good questions....
I don't want to clear the data the first time it is run. They must always start with a blank.

The Ground sheet will not be there, but now that I think of it, I can simply copy in the total formulas in a later step. Can we just copy the format from Row 51 to the last row (totals row)?
What-if-scenario-v2.xlsm
0
Martin LissOlder than dirtCommented:
Sure, we can copy the format, but first we need to clear up what you mean by
I don't want to clear the data the first time it is run. They must always start with a blank.
which seems contradictory to me, so what should happen in the following scenarios?

1) They open the workbook and there's data in rows 3:5 and row 6 has a total row on the Dom Ex sheet. They run the ExpressEnter macro. Should it clear rows 3:6 before it does anything else?

2) They run ExpressEnter a second time. Should ExpressEnter clear the data before doing anything else?
0
Euro5Author Commented:
Understood. I agree, we should clear all data before ExpressEnter runs.
0
Martin LissOlder than dirtCommented:
Try the attached. It adds a "Total" line with formulas. Make sure they are correct.

While working on this workbook I've noticed several things that IMO can be improved.
1.    Validations for the data entered in ExpressEnter are needed so that for example "blah" could not be an acceptable value for "Low Weight"
2.    Some of the existing Dom Ex formulas seem to be incorrect. For example the cell H3 formula is  G3*$D$3 but column D is text
3.    Rows 50 and 51 could be stored in a different, possibly hidden, sheet. That way the rows wouldn’t need to be hidden and you probably wouldn't need the Do NOT ENTER PAST THIS LINE warning
4.    Pressing cancel during input of the values isn't handled.
28692983a.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
The future if you like:)
...
0
Euro5Author Commented:
The totals are correct. This same process has to happen on each sheet.
There will not always be data on all sheets.

1, Yes you are right, it needs to be a number only.
2. Yes, that should be D1, which will adjust the numbers to annualized calculation if needed.
    I did not check all the formulas.
3. Yes, a hidden sheet makes more sense.
4. I don't know what to do about the cancel.
5. Can you do the above!??? That is exactly what I am looking for!!!!
0
Martin LissOlder than dirtCommented:
This same process has to happen on each sheet.
I don't think you ever mentioned that more than one sheet was involved and so if what I've done here for the Dom Ex sheet is correct then I think you should close this question and start a new one.

Can you do the above!???
Yes and I already have a solution:), but it needs to be modified to use the hidden sheet and to also work on the "Ground" and "Intl" sheets which I assume are the other sheets you where you say it needs to happen.
0
Euro5Author Commented:
OK, very good. I do need it for each sheet and it should be on new question.
0
Euro5Author Commented:
So helpful this far. Will continue to work on 1. updated form for entry 2. running for additional sheets. Thanks!! :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.