Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Concatenate in VBA (Excel xp)

    I recorded this macro

    ActiveCell.FormulaR1C1 = "=RawData!R957C1 & ""-"" & RawData!R962C1"

    and i need to make the 957 and the 962 a variable. f1=957 and f2=962 so in my code i do this...

    ActiveCell.FormulaR1C1 = "=RawData!R" & f1 & ""-"" & "RawData!R" & f2 & "C1"

    and i keep getting a type mismatch error?? i don't know how to fix it. Please help... Thank you

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Concatenate in VBA (Excel xp)

    Hi jha,

    Try:
    ActiveCell.Formula = "=RawData!A" & f1 & ""-"" & "RawData!A" & f2
    This uses the normal column/row (eg A1) cell addressing format most users would more readily recognise than row/column R1C1.

    Cheers
    PS: You omitted C1 from the 1st half of your expression.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Concatenate in VBA (Excel xp)

    Thank you. I tried this...

    ActiveCell.Formula = "=RawData!A" & f1 & ""-"" & "RawData!A" & f2

    in my code. It automatically added a space around the hyphen as soon as i pasted it in

    ActiveCell.Formula = "=RawData!A" & f1 & "" - "" & "RawData!A" & f2

    and i get the same run-time 13, type mismatch error

  4. #4
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenate in VBA (Excel xp)

    f1 and f2 are not variables. They are cell addresses.
    Try something like:

    dim x as integer, y as integer
    x = range("f1")
    y = range("f2)
    ActiveCell.Formula = "=RawData!A" & x & "" - "" & "RawData!A" & y

    HTH

  5. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Concatenate in VBA (Excel xp)

    Sorry,

    that should have been:
    ActiveCell.Formula = "=RawData!A" & f1 & "-" & "RawData!A" & f2

    If f1 & f2 are actually cell references, you could use:
    ActiveCell.Formula = "=RawData!A" & Range("f1") & "-" & "RawData!A" & Range("f2")

    Otherwise, if f1 & f2 are variables, you might be better off using something that can't get confused with cell references. Even so,
    Sub Test()
    Dim f1 As Integer, f2 As Integer
    f1 = 957
    f2 = 962
    ActiveCell.Formula = "=RawData!A" & f1 & "-" & "RawData!A" & f2
    End Sub
    seems to work OK, as does
    Sub Test()
    Dim f1 As Integer, f2 As Integer
    f1 = Range("F1").Value
    f2 = Range("F2").Value
    ActiveCell.Formula = "=RawData!A" & f1 & "-" & "RawData!A" & f2
    End Sub

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Concatenate in VBA (Excel xp)

    what happen is i get ...
    =RawData!A957-RawData!A961

    like it is subtracting A961 from A957. I actually want the "hyphen" to be in there as text like the word "to". I've tried double quotes but that won't work either

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Concatenate in VBA (Excel xp)

    Try<pre>ActiveCell.Formula = "=RawData!A" & f1 & "&""-""&" & "RawData!A" & f2</pre>

    Note the use of double double quotes to insert quotes into the formula.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •