CODEWITHSUNDEEP
vbaexceldate
Rosh
Rosh

Reputation: 27

Reference to a formula with inputbox method

I wanted to apply a Text(E2,"MM/DD/YYYY") formula via excel VBA. I use multiple sheets, Cell destination and the Cell reference is not fixed. Hence i used Input-box method to for Cell destination which is working perfect, and wanted to manually select or change cell reference in the formula via inputbox method. E.g. if i write above formula in A2 cell and my target cell is E2. Cell selection should happen via inputbox.

Initially my plan was to select both the things with inputbox, but i am just a beginner and did not managed to do that hence changed the plan and re-written the code. But the codes seems to be having some issues while editing formula range in inputbox, sometimes it doesn't consider my iputs. If i Say Text(E2,"MM/DD/YYYY") then it selects Text(D2 or something,"MM/DD/YYYY")

Option Explicit

Sub FinalTxtDte()

Dim Rng As range
Dim LastRow As Long
Dim Frmla As String
Dim DestRng As range

On Error Resume Next ' if the user presses "Cancel"

Set Rng = Application.InputBox("Select a Cell which needs to be converted in Date format.", "Range Selection", Type:=8)

Err.Clear

On Error GoTo 0

If Not Rng Is Nothing Then

    Frmla = "=TEXT(" & Rng.Address("False", "False") & ",""MM/DD/YYYY"")"

    On Error Resume Next ' if the user presses "Cancel"

    Set DestRng = Application.InputBox("Select a Cell where you would like to get a Converted Date.", "Range Selection", Type:=8)

    Err.Clear

    On Error GoTo 0

    If Not DestRng Is Nothing Then

        DestRng.Formula = Frmla

    LastRow = Rng.End(xlDown).Row
    DestRng.Select
    range(Selection, Selection.Offset(LastRow - Rng.Row, 0)).Select
    Selection.FillDown
    range(Selection, Selection.Offset(LastRow - Rng.Row, 0)).Value _
    = range(Selection, Selection.Offset(LastRow - Rng.Row, 0)).Value

    End If

    End If

End Sub

Upvotes: 1

Views: 1524

Answers (1)

Shai Rado
Shai Rado

Reputation: 33692

The code below will let you use 2 InputBoxes to select the cell and the formula target (currently according to your post requaet it's for 1 Cell).

I've modifed the 2nd InputBox to select the destination range for the formula.

You need to keep the On Error Resume Next (and later the On Error GoTo 0) in case the user selects the "Cancel" option in the InputBox.

Code

Option Explicit

Sub TextDateFormula()

Dim Rng As Range
Dim LastRow As Long
Dim Frmla As String, Txt As String
Dim DestRng As Range

On Error Resume Next ' if the user presses "Cancel"
Set Rng = Application.InputBox("Select a cell.", "Range Selection", Type:=8)
Err.Clear
On Error GoTo 0

If Not Rng Is Nothing Then    
    Frmla = "=TEXT(" & Rng.Address(True, True) & ",""MM/DD/YYYY"")"

    On Error Resume Next ' if the user presses "Cancel"
    Set DestRng = Application.InputBox("Select a range to add Decimal Hours.", "Range Selection", Type:=8)
    Err.Clear
    On Error GoTo 0

    If Not DestRng Is Nothing Then
        DestRng.Formula = Frmla
    End If        
End If

End Sub

Edit 1: In order for the formula not to take the absolute address, modify the code line below:

Frmla = "=TEXT(" & rng.Address(False, False) & ",""MM/DD/YYYY"")"

You need to modify the section inside the brackets after the Address("Row Absolute", "Column Absolute") , so modify column and row setting according to your needs.

Upvotes: 0

Related Questions