Selecting 10 charcters on one cell and pasting to another cell

[annysjunkmail]

Hi,
For example, I am working on cell G8. I would like to set up a button
that, once clicked, copies and pastes the first 10 characters from D8
and pastes it into H8. The same process would apply if I am on G33,
i.e.copy first 10 characters from D33 and paste into H33, etc, etc.

I can't figure it out.

Can someone help?
Tony

 

[Guest]

Here you go...

Sub ButtonName_Click()
ActiveCell.Offset(1,0) = Left(ActiveCell,10)
End Sub

 

[Susan]

this is what i came up with:

Option Explicit

Private Sub CommandButton1_Click()

Dim CurrentRow As Long
Dim TargetCell As Range
Dim myString As String
Dim DestCell As Range

CurrentRow = ActiveCell.Row

Set TargetCell = Range("d" & CurrentRow)

myString = Left(TargetCell.Value, Len(TargetCell) - 6)

Set DestCell = Range("h" & CurrentRow)

DestCell = myString


End Sub

but Chris' is much simpler.
oh well.
susan

 

[Susan]

on 2nd thought, i don't think chris' does what you want it to, because
he's taking the value of the active cell instead of column d.
however, i found a bug in mine, that it only works properly if the
target cell is 16 characters.
so i think it'd be better to go from the right.

myString = right(TargetCell.Value, Len(TargetCell) 10)

susan

 

[Susan]

my mistake (didn't work) - just the "10" gives an error & should be
"+10", but that doesn't work. that line should be

myString = Left(TargetCell.Text, 10)

sorry!
(excuse me if this comes thru 2x, is giving me troubles)
susan

 

[annysjunkmail]

my mistake (didn't work) - just the "10" gives an error & should be
"+10", but that doesn't work. that line should be

myString = Left(TargetCell.Text, 10)

sorry!
(excuse me if this comes thru 2x, is giving me troubles)
susan

Hi Susan & Chris,
I have tried both methods and they both don't work.
I have tried variations on both your codes but no luck...any thoughts?

Thanks
Tony

 

[Alan]

I believe that eAlchemist had the right idea, just didn't get the
coordinates correct:

Sub ButtonName_Click()
ActiveCell.Offset(0, 1) = Left(ActiveCell.Offset(0, -3), 10)
End Sub

Regards,

Alan

 

[annysjunkmail]

I believe that eAlchemist had the right idea, just didn't get the
coordinates correct:

Sub ButtonName_Click()
ActiveCell.Offset(0, 1) = Left(ActiveCell.Offset(0, -3), 10)
End Sub

Regards,

Alan

Working now,
Thanks everyone

Tony

 

[Susan]

alan - if you're still monitoring this thread - why didn't mine
work????
it worked for me........ what was i doing wrong?
thanks
susan

 

[Alan]

Hi Susan,

Your code works perfect, and you didn't do anything wrong. It worked, once
the myString line was changed to the line in your third message. I
personally like to keep my code as short as possible. If I can do the same
with less, I will always choose less. Tony's situation was ActiveCell
specific and not variable, so I tend to go the ActiveCell route since it
doesn't require much code.

I'm curious which route Mr. Ogilvy would choose in his own coding. Maybe he
will read this and respond.

Thanks for asking,

Alan

 

[Susan]

thanks very much for replying!
so mine worked, just was horribly over-written & inefficient. ha
ha
i'll get there someday!
thank you for helping me learn.
susan