[SOLVED]Change Macro to enter full amount

Evening all

I know nothing about macro's and the person that created the macro on this sheet is not here. Can anyone change it for me/advise what to do?

At the minute we press ctrl+E (on cell C5 on Sunday page) and enter 2 rounded up figures in the pop up boxes (i.e 32, then 2, the 2nd amount is deducted from the first) what we want to do is enter 2 full figures (i.e 31599 and 2499) and then have the macro round the figure to the nearest thousand.

Is this possible?

Sal

Replace the code that's there with this:

Sub DualEnv()
    ' Keyboard Shortcut: CTRL + E

    Dim iRow        As Long
    Dim iCol        As Long
    Dim sInp        As String
    Dim nVal        As Long
    Dim iRetail     As Long
    Dim iNonTrd     As Long
    Dim sPfx        As String

    iRow = ActiveCell.Row
    iCol = ActiveCell.Column

    sInp = InputBox("Enter Retail-J total for Store", "Hourly Sales", 0)
    If Len(sInp) And IsNumeric(sInp) Then
        iRetail = CLng(sInp)
        nVal = nVal + 1
    End If

    sInp = InputBox("Enter Retail-J non-trade total for Store", "Hourly Sales", 0)
    If Len(sInp) And IsNumeric(sInp) Then
        iNonTrd = CLng(sInp)
        nVal = nVal + 1
    End If

    If nVal < 2 Then
        MsgBox "Please enter valid currency amounts."
    
    Else
        Select Case Cells(iRow, "A").Value
            Case "L'don":  sPfx = "London = "
            Case "T'ford": sPfx = "Trafford = "
            Case "Exch":   sPfx = "Exchange = "
            Case "B'ham":  sPfx = "Birmingham = "
            Case "Ecom":   sPfx = "Ecommerce = "
            Case Else:     sPfx = ""
        End Select

        ActiveCell.Value = sPfx & Format(iRetail - iNonTrd, "0,") & "k"
    End If
End Sub

Don't replace the whole sub. Open the original workbook, Alt+F11 to open the VB Editor.Then paste Shg's code below over the existing code after

Sub DualEnv()
'
' DualEnv Macro
' David Piggin - Selfridges Retail Ltd
'
' Keyboard Shortcut: CTRL + E
'

  Dim iRow        As Long
    Dim iCol        As Long
    Dim sInp        As String
    Dim nVal        As Long
    Dim iRetail     As Long
    Dim iNonTrd     As Long
    Dim sPfx        As String

    iRow = ActiveCell.Row
    iCol = ActiveCell.Column

    sInp = InputBox("Enter Retail-J total for Store", "Hourly Sales", 0)
    If Len(sInp) And IsNumeric(sInp) Then
        iRetail = CLng(sInp)
        nVal = nVal + 1
    End If

    sInp = InputBox("Enter Retail-J non-trade total for Store", "Hourly Sales", 0)
    If Len(sInp) And IsNumeric(sInp) Then
        iNonTrd = CLng(sInp)
        nVal = nVal + 1
    End If

    If nVal < 2 Then
        MsgBox "Please enter valid currency amounts."
    
    Else
        Select Case Cells(iRow, "A").Value
            Case "L'don":  sPfx = "London = "
            Case "T'ford": sPfx = "Trafford = "
            Case "Exch":   sPfx = "Exchange = "
            Case "B'ham":  sPfx = "Birmingham = "
            Case "Ecom":   sPfx = "Ecommerce = "
            Case Else:     sPfx = ""
        End Select

        ActiveCell.Value = sPfx & Format(iRetail - iNonTrd, "0,") & "k"
    End If
End Sub

works for me but you have to reassign ctrl+e in tools/macro/macros/options

The shortcut worked for me just pasting the body of the code