Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2004
    Location
    Italy
    Posts
    15

    Cool Unanswered: Yet another macro...

    Hi everybody,
    I know it can sound trivial, but I cant solve this problem:
    I'm using in an excel macro:

    Code:
        Range(RangeFrom).Copy
        Range(RangeTo).PasteSpecial Paste:=xlPasteValues, _
        Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    The fact is that I would like to trim blank spaces that are present in RangeFrom.
    So if I have to copy cells A1 (value: " A ") and A2 (value " B ") in the corrisponding cells B1 and B2, I would like to have "A" in B1 and "B" in B2.
    How can I get this done?
    Thanks in advance.
    Father Xmas

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Code:
    Sub Test()
        Range("A1:A2").Copy Destination:=Range("B1:B2")
    End Sub
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Hi

    What i think your after is something like this

    Code:
    Sub Test()
        With Range("B1:B2")
            .FormulaR1C1 = "=TRIM(RC[-1])"
            .Formula = .Value
        End With
    End Sub
    all this is doing is using the trim formula from Excel and refrencing the previous column, Then replacing the formula you have with the values it produces.

    HTH
    David

  4. #4
    Join Date
    Oct 2003
    Posts
    1,091
    Yep, David, I missed the spaces on each side. I think the CLEAN function works as well.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  5. #5
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    hi Shades

    The clean function only gets rid of right spaces, while trim gets rid of spaces from both sides, I just thought you misread the question and had no idea when you'd be back so i posted for spead of response

    Hope your Well
    David

  6. #6
    Join Date
    Oct 2003
    Posts
    1,091
    Hi, David. That's fine. My posting is often erratic, depending on work load. Glad you stepped in. That's what makes this a community effort.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  7. #7
    Join Date
    Jun 2004
    Location
    Italy
    Posts
    15
    Thank you guys,
    just another question,
    I used:

    Code:
        With Range(MyCell)
            .NumberFormat = "General"
            .FormulaR1C1 = "=TRIM(" & SheetNameFrom & "!C1)"
            .Formula = .Value
            .AutoFill Destination:=Range("'" & SheetNameTo & "'!" & RangeTo), Type:=xlFillDefault
        End With
    I noted that
    .FormulaR1C1 = "=TRIM(" & SheetNameFrom & "!C1)"

    is not executed properly; in MyCell I get:
    =TRIM(SheetNameFrom!$A:$A)

    Where am I wrong?
    Last edited by ioclio; 08-05-04 at 06:54.

  8. #8
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    the problem is here
    Code:
    .FormulaR1C1 "=TRIM(" & SheetNameFrom & "!C1)"
    .FormulaR1C1 uses a different refrencing system so here C1 reffers to Column 1 hence Column A

    Looking at your code all i think you have to do is to use .Formula in place of .FormulaR1C1

    HTH

    David

Posting Permissions

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