Use data from input box into a formula and show result in active cell

**Abhi1687** · 02-13-2013, 03:58 PM

Hi All

i was trying to write a macro so that user can get a input box and select any cell with date and hit OK then macro use that date in a formula and present the result in active cell. below is the code i tried, but it is giving error: #NAME?

Sub getweeknumber()
Dim originaldate As Date

originaldate = CDate(Application.InputBox("select the source", "Get Week Number"))
ActiveCell.Value = "=weeknum(originaldate,1)"

End Sub

any help will be much appreciated.

**protonLeah** · 02-13-2013, 04:22 PM

Option Explicit
Sub getweeknumber()
    Dim originaldate As Range
    
    Set originaldate = Application.InputBox("select the source", "Get Week Number", Type:=8)
    ActiveCell.Formula = "=weeknum(" & originaldate.Address(0, 0) & ",1)"
    
End Sub

**Andrew-R** · 02-13-2013, 04:29 PM

What are you trying to:

a. Put the weeknum formula into the active cell, or

b. Put the result of the weeknum formula into the active cell?

If A then:

ActiveCell.Formula = "=weeknum(" & CDbl(originaldate) & ",1)"

If B then:

ActiceCell.Value = WorksheetFunction.WeekNum(originaldate, 1)

**Andrew-R** · 02-13-2013, 04:30 PM

Hmmm, I should have read your original post better, really.

**Abhi1687** · 02-13-2013, 05:35 PM

many thanks guys. it worked

Use data from input box into a formula and show result in active cell

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Use data from input box into a formula and show result in active cell

Re: Use data from input box into a formula and show result in active cell

Re: Use data from input box into a formula and show result in active cell

Re: Use data from input box into a formula and show result in active cell

Re: Use data from input box into a formula and show result in active cell

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions