How to: request input, store in a cell, and later use in a message box 1

[robertsfd]

I have a macro that requests input from a user, stores in one column (column A), and another column (column B)calculates a formula based on that input value. I want a message box to then report both the user input value in column A and the calculated value in column B. Each time the macro runs, it moves to a new row and does the same thing.

I can get the user input value, but am unsure of how to "get" the value in that calculated cell (in column B) since it is not a static cell reference - instead it is a static column reference but the row reference would change each time the macro is run.

Thanks in advance. Doug

Here is what I have, but it doesn't work:

' get input and place in current cell
ActiveCell.FormulaR1C1 = InputBox("Input your value")
' place current cell value in variable1
variable1 = ActiveCell.FormulaR1C1
' get value in adjacent cell and put in variable2 - THIS IS WHAT DOESN'T WORK
variable2 = cel.Offset(0,1)
'display ending message
MsgBox ("Your value was " & variable1 & "which calculates to " & variable2 & "units")

 

[PHV]

What is cel ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

    ' get input and place in current cell
    With ActiveCell
        .Value = InputBox("Input your value")
    ' place current cell value in variable1
        variable1 = .Value
    ' get value in adjacent cell and put in variable2 - THIS IS WHAT DOESN'T WORK
        variable2 = .Offset(0, 1).Value
    'display ending message
    End With
    MsgBox ("Your value was " & variable1 & "which calculates to " & variable2 & "units")

Skip,
[sub]
[red]Sign above the facsimile apparatus at the music publisher:[/red]
If it ain't baroque...
Don't FAX it![/sub]

 

[robertsfd]

Thanks Skip. Not sure I didn't do something wrong the first time I tried your solution, but when I added "ActiveCell" to your variable2 statement, it worked.

variable2 = ActiveCell.Offset(0, 1).Value

Many thanks to you. Have a great day, Doug

 

[robertsfd]

PH, I'm only a beginner, but I think "cel" is shorthand for the current cell.

 

[SkipVought]

    With ActiveCell
....
        variable2 = [b][red].[/red]Offset(0, 1)[/b].Value
....
    End With

the [red]DOT[/red] Offset refers up to the With ActiveCell. If you did not include the [red]DOT[/red], then you would have had to do what you did.

Skip,
[sub]
[red]Sign above the facsimile apparatus at the music publisher:[/red]
If it ain't baroque...
Don't FAX it![/sub]

 

[PHV]

And the shorthand for current cell is ActiveCell.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[robertsfd]

Thanks Skip. Now I see what I did wrong so that it didn't work exactly as you showed.

And PH, I've seen and used the "cel" many times. I can't tell you what it is, but if you google it (along with "Excel VBA" or something, you should see many examples and might be able to tell where it is used or not used.

 

[PHV]

I'm afraid you're confusing variables and object model ...

 

[robertsfd]

OK, thanks for the info Skip. As you can see, I'm a long way from being knowledgeable. My earlier comment was just based on code from macros in spreadsheets I've used. This spurs on my quest to learn more...a godd thing in and of itself.

Thanks again to you both. Have a nice day.

 

[robertsfd]

Oops, I mean "thanks for the info PH...