Yet another macro...

ioclio · #1 (**permalink**) 08-03-04, 12:28

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

shades · #2 (**permalink**) 08-03-04, 12:49

Code:

Sub Test()
    Range("A1:A2").Copy Destination:=Range("B1:B2")
End Sub

DavidCoutts · #3 (**permalink**) 08-04-04, 03:57

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

shades · #4 (**permalink**) 08-04-04, 10:08

Yep, David, I missed the spaces on each side. I think the CLEAN function works as well.

DavidCoutts · #5 (**permalink**) 08-04-04, 11:03

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

shades · #6 (**permalink**) 08-04-04, 12:01

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.

ioclio · #7 (**permalink**) 08-05-04, 05:52

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?

DavidCoutts · #8 (**permalink**) 08-05-04, 06:08

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

Internet Services

Web Development

Digital Marketing

Consumer Tech