excelvbainputbox

How do I put input from InputBox into a cell?


I have an InputBox where the user will type in the current month in the format of first three characters + Sale (e.g. JunSale).

I want to return this into a specific cell. This cell already has stuff in it (e.g. MaySale). Once a user types in the input, the cell will now read JunSale not MaySale.

My code gives me an error message:

Compile Error: Method or data member not found

with highlight on the last line of the code.

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sales Calc")
Dim ActSales As Range
Dim pastmthsale As String
Dim currentmthsale As String

With Worksheets("Sales Calc").Cells
    Set ActSales = .Find("Forecast Sales -->>", After:=Range("DH173"), LookIn:=xlValues)
    If Not ActSales Is Nothing Then
        ActSales.Select
    End If
End With
ActiveCell.Offset(rowOffset:=0, ColumnOffset:=-1).Select

pastmthsale = Selection.Value
currentmthsale= Application.InputBox("Enter the Latest Month with Actual Sales Data")
ws.pastmthsale = currentmthsale

This is the code I have now. The error message I get is

Application-defined or object-defined error.

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sales Calc")
Dim ActSales As Range

With Worksheets("Sales Calc").Cells
    Set ActSales = .Find("Forecast Sales -->>", After:=Range("DH173"), LookIn:=xlValues)
    If Not ActSales Is Nothing Then
        ActSales.Select
    End If
End With

ActiveCell.Offset(rowOffset:=0, ColumnOffset:=-1).Select

Range(ActiveCell) = Application.InputBox("Enter the Latest Month with Actual Sales Data")

Solution

  • Just use this method - no Range() needed when referencing activecell.

    ActiveCell = Application.InputBox("Enter the Latest Month with Actual Sales Data")