How do I make the formula see the message box answer?
 

I have a pop up message box as shown below

Dim Res As String
Res = InputBox("What is the cell address?")
If StrPtr(Res) = 0 Then
MsgBox "User Clicked Cancel"
ElseIf Res = vbNullString Then
MsgBox "User Clicked OK with no input"
Else
MsgBox "User Entered cell " & Res
End If
'
Now I need the answer to that reqeust to be in this formula where 0 is
the cell address that I have just entered. (This is a simple formual, but I
need to see if I can get it to work before I use it in a more complicated
formula)

ActiveCell.FormulaR1C1 = "=R[-1]C[2] + 0 "
Range("D7").Select

How do I make the formula see the message box answer?
 

ActiveCell.FormulaR1C1 _
= "=R[-1]C[2] + " & range(res).address(referencestyle:=xlr1c1)

But I'd drop the inputbox and use application.inputbox.


Dim Rng as range

'...
Set rng = nothing
on error resume next
set rng = application.inputbox(Prompt:="Select a cell",type:=8).cells(1)
on error goto 0

if rng is nothing then
'user hit cancel
'what should happen?
else
ActiveCell.FormulaR1C1 _
= "=R[-1]C[2] + " & rng.address(referencestyle:=xlr1c1)
end if


Melody4572 wrote:

I have a pop up message box as shown below

Dim Res As String
Res = InputBox("What is the cell address?")
If StrPtr(Res) = 0 Then
MsgBox "User Clicked Cancel"
ElseIf Res = vbNullString Then
MsgBox "User Clicked OK with no input"
Else
MsgBox "User Entered cell " & Res
End If
'
Now I need the answer to that reqeust to be in this formula where 0 is
the cell address that I have just entered. (This is a simple formual, but I
need to see if I can get it to work before I use it in a more complicated
formula)

ActiveCell.FormulaR1C1 = "=R[-1]C[2] + 0 "
Range("D7").Select

--

Dave Peterson

How do I make the formula see the message box answer?
 

Sub bBB()
res = InputBox("What is the cell address?")
If StrPtr(res) = 0 Then
MsgBox "User Clicked Cancel"
Exit Sub
ElseIf res = vbNullString Then
MsgBox "User Clicked OK with no input"
Exit Sub
Else
MsgBox "User Entered cell " & res
End If
ActiveCell.FormulaR1C1 = "=R[-1]C[2] +" & res
If ActiveCell.Text = "#NAME?" Then
res = Application.ConvertFormula(res, xlA1, xlR1C1)
ActiveCell.FormulaR1C1 = "=R[-1]C[2] +" & res
End If
End Sub

--
Regards,
Tom Ogilvy


"Melody4572" wrote in message
...
I have a pop up message box as shown below

Dim Res As String
Res = InputBox("What is the cell address?")
If StrPtr(Res) = 0 Then
MsgBox "User Clicked Cancel"
ElseIf Res = vbNullString Then
MsgBox "User Clicked OK with no input"
Else
MsgBox "User Entered cell " & Res
End If
'
Now I need the answer to that reqeust to be in this formula where 0 is
the cell address that I have just entered. (This is a simple formual, but
I
need to see if I can get it to work before I use it in a more complicated
formula)

ActiveCell.FormulaR1C1 = "=R[-1]C[2] + 0 "
Range("D7").Select

How do I make the formula see the message box answer?
 

Perfect! Thanks Tom!

"Tom Ogilvy" wrote:

Sub bBB()
res = InputBox("What is the cell address?")
If StrPtr(res) = 0 Then
MsgBox "User Clicked Cancel"
Exit Sub
ElseIf res = vbNullString Then
MsgBox "User Clicked OK with no input"
Exit Sub
Else
MsgBox "User Entered cell " & res
End If
ActiveCell.FormulaR1C1 = "=R[-1]C[2] +" & res
If ActiveCell.Text = "#NAME?" Then
res = Application.ConvertFormula(res, xlA1, xlR1C1)
ActiveCell.FormulaR1C1 = "=R[-1]C[2] +" & res
End If
End Sub

--
Regards,
Tom Ogilvy


"Melody4572" wrote in message
...
I have a pop up message box as shown below

Dim Res As String
Res = InputBox("What is the cell address?")
If StrPtr(Res) = 0 Then
MsgBox "User Clicked Cancel"
ElseIf Res = vbNullString Then
MsgBox "User Clicked OK with no input"
Else
MsgBox "User Entered cell " & Res
End If
'
Now I need the answer to that reqeust to be in this formula where 0 is
the cell address that I have just entered. (This is a simple formual, but
I
need to see if I can get it to work before I use it in a more complicated
formula)

ActiveCell.FormulaR1C1 = "=R[-1]C[2] + 0 "
Range("D7").Select

How do I make the formula see the message box answer?
 

Thank you to you also Dave. This one is somehting I can use in another
workbook.

"Melody4572" wrote:

Perfect! Thanks Tom!

"Tom Ogilvy" wrote:

Sub bBB()
res = InputBox("What is the cell address?")
If StrPtr(res) = 0 Then
MsgBox "User Clicked Cancel"
Exit Sub
ElseIf res = vbNullString Then
MsgBox "User Clicked OK with no input"
Exit Sub
Else
MsgBox "User Entered cell " & res
End If
ActiveCell.FormulaR1C1 = "=R[-1]C[2] +" & res
If ActiveCell.Text = "#NAME?" Then
res = Application.ConvertFormula(res, xlA1, xlR1C1)
ActiveCell.FormulaR1C1 = "=R[-1]C[2] +" & res
End If
End Sub

--
Regards,
Tom Ogilvy


"Melody4572" wrote in message
...
I have a pop up message box as shown below

Dim Res As String
Res = InputBox("What is the cell address?")
If StrPtr(Res) = 0 Then
MsgBox "User Clicked Cancel"
ElseIf Res = vbNullString Then
MsgBox "User Clicked OK with no input"
Else
MsgBox "User Entered cell " & Res
End If
'
Now I need the answer to that reqeust to be in this formula where 0 is
the cell address that I have just entered. (This is a simple formual, but
I
need to see if I can get it to work before I use it in a more complicated
formula)

ActiveCell.FormulaR1C1 = "=R[-1]C[2] + 0 "
Range("D7").Select