CODEWITHSUNDEEP
excelvba
Bernheart
Bernheart

Reputation: 637

Add values with Macro in Excel

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.

Upvotes: 1

Views: 182

Answers (1)

Fadi
Fadi

Reputation: 3322

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

Upvotes: 1

Related Questions