Copy only certain tex within a single cell

[Zenderboy01]

Hello,

I am trying to figure out how to make a macro only copy a string of text within a cell. Luckily, it should be able to run on an easy rule. I need it to only copy everything after a ":", so I have in cell A1...

ABCD1234: Apples and Oranges

I want it to copy to only "Apples and Oranges" and paste it in cell B1.


Thanks in advance!

[mehmetcik]

why do you need a macro. A formula will quite happily do this.

[mehmetcik]

As a formula:

Please Login or Register  to view this content.

[mehmetcik]

As Macro:

Please Login or Register  to view this content.

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

Or you could just use the formula in Range("B1")

[HaHoBe]

Hi, Zenderboy01,

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Ciao,
Holger

[Zenderboy01]

Holger,

The second line of code works perfect. However, I am trying to modify it by using referential code instead of hard code, but I keep getting the "Range of object" error and I can't figure out why. I have..

Dim curRow As Integer
Dim curCol As Integer

curRow = 4
curCol = 3

Range(Cells(curRow, curCol + 2)).Value = Trim(Split(Range("A1").Value, ":")(1))

Any idea why this error occurs when I replace it with variables?

[HaHoBe]

Hi, Zenderboy01,

Range with Cells within awaits two Cells mentioned, just use Cells alone:

Please Login or Register  to view this content.

And please use Code-Tags when showing your procedure.

Ciao,
Holger

[Zenderboy01]

Ah! I knew it would be something so simple. Thanks! Sorry for not using code tags, I'm a noob to VBA and using forums.