RE: Formula in given via input box

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Patrick Molloy (PatrickMolloy_at_discussions.microsoft.com)
Date: 02/02/05 

Date: Wed, 2 Feb 2005 06:57:01 -0800

you're confused by your quotes !

Sub test()

Dim segment As String
Dim formula As String
segment = "HIO"

formula = "=" & Chr(39) & segment & " - Exp" & Chr(39) & "!R[-21]C - " _
                     & Chr(39) & segment & " - Rev" & Chr(39) & "!R[-26]C"

selection.FormulaR1C1 = formula

End Sub

"Petr" wrote:

> Hallo,
> I have following problem. I would need to use the user values
> submitted via inputbox into the formula of the following type:
>
> ActiveCell.FormulaR1C1 = "='HIO - Exp'!R[-21]C-'HIO - Rev'!R[-26]C"
>
> I.e. in this case the parameter "HIO" should be submitted via
> inputbox.
>
> I have the following code which ends with error.
>
> Sub formula_input_test
>
> IntgInput = InputBox("Submit the segment name (abbrev.)")
>
> segment = IntgInput
>
> Range("A1").Select
> ActiveCell.FormulaR1C1 = "='& segment & - Exp & !R[-21]C - '& segment
> & - Rev & !R[-26]C"
>
> End Sub
>
>
> Thanks a lot in advance for any suggestions.
> Best regards, Petr Duzbaba
>

Relevant Pages

  • Re: How to copy non-contiguous columns to a text file
    ... I left out one important line of code at the end and the two ScreenUpdating lines that hide the actions taking place on the worksheet. ... Sub CopyNonContiguousSelectedColumnsIntoTheClipboardForPastingElsewhere() ... Dim Formula As String, FileName As String, SplitAddrAs String ...
    (microsoft.public.excel)
  • Project Error
    ... Private Declare Sub Sleep Lib "Kernel32" ... Dim strDataSrc As String ...
    (microsoft.public.vb.bugs)
  • Re: FTP CD command
    ... My code connects to a ftp site and the enumerates all content on that place ... Public Property UriAs String ... End Sub ... Dim listRequest As FtpWebRequest = CType, ...
    (microsoft.public.dotnet.languages.vb)
  • Re: FTP CD command
    ... Private _Uri As String ... End Sub ... Dim listRequest As FtpWebRequest = CType, ... Public Sub UploadAsynch(ByVal fileName As String, ByVal uploadUrl As ...
    (microsoft.public.dotnet.languages.vb)
  • Re: FTP CD command
    ... i remember to have seen a socket level FTP ... Private _Uri As String ... End Sub ... Dim listRequest As FtpWebRequest = CType, ...
    (microsoft.public.dotnet.languages.vb)