Add values with Macro in Excel

Question

I want to create a table in Excel to manage my money. I'd like to insert money import simply by clicking a button ("insert import"); than Excel has to create a new row, select the right cell and expect the value that I want to insert.

I have tried to do it on my own by registering a Macro; but I am not able to tell Excel to expect my values.

My expectation: I want to create a Macro, actived from a button that creates a new row for inserting two values, "info" and "import". Info is the derivation of money; import is the value of money. Than Excel wait for me to compile "info" cell. Once compiled, I press enter and Excel goes to "import" cell; than Excel wait again for me to compile "import" cell. Than I press enter and I finish the operation. The result is a row with my values and the date of the operation.

How can I do?

Here I insert the code of the Macro:

    Sub Macro5()
    Rows("3:3").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A3").Select
    ActiveCell.FormulaR1C1 = ""
    Range("C3").Select
    ActiveCell.FormulaR1C1 = ""
    Range("E3").Select
    ActiveCell.FormulaR1C1 = "1/6/2016 20:41"
    Range("F5").Select
End Sub

As you can see, this code is not useful, since it simply active cells without asking what I want to write in. What's more, date does not update when I run the Macro.

What have you tried? You should at least be able to get going by doing your steps with the Macro Recorder, then take a look at the code. Then, you can tweak it to get close to the final solution, then you can come here and ask for help on specific things. Please review How to Ask for more tips on asking a "good" question. — BruceWayne
– BruceWayne, Commented Jan 6, 2016 at 19:17
Hi Bruce, I have done all you have wrote before asking. Simply I can't tell Excel to wait for my values. — Bernheart
– Bernheart, Commented Jan 6, 2016 at 19:30
Welcome to SO. This is not a free code-writing service but exists to help others with code or functions they are trying to develop. As such, we expect to see clear cut questions with examples of data, attempted code, actual output, desired output, research efforts to solve the problem, etc. Please read the HELP pages for information as to How to Ask a Good Question; and also How to Provide an Example — Ron Rosenfeld
– Ron Rosenfeld, Commented Jan 6, 2016 at 19:35

Fadi · Accepted Answer · 2016-01-06 20:49:07Z

1

You can edit this code as needed:

Sub test()

 Dim mInfoCol As String
 Dim mImportCol As String
 Dim mDateCol As String
 Dim mRow As Long

 mInfoCol = "A"
 mImportCol = "C"
 mDateCol = "E"
 mRow = 3

 Rows(mRow).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
 Range(mInfoCol & mRow).Value = InputBox("info?", , "some info")
 Range(mImportCol & mRow).Value = InputBox("import?", , "200")
 Range(mDateCol & mRow).Value = Now()

End Sub

answered Jan 6, 2016 at 20:49

Fadi

3,3223 gold badges21 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bernheart Over a year ago

Thank you for your contribute

Collectives™ on Stack Overflow

Add values with Macro in Excel

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related