Greetings,

I am passing a string of characters in the format like this: 123abc45

I would like to have it show up after pasting as: 123A <BC-45

The code I am using to paste with now is:

Select Case Target.Column
Case 3
For i = 1 To 118
With ws1_1.Range("InvData" & i)
Select Case i
Case 4 'Cust List data
.Value = _
ws2_1.Range("MCL_Name").Offset(ws1_1 _
.Range("MyRowNum").Value - 1, 60).Value
I tried to wrap the last line with this format:

Format(ws2_1.Range("MCL_Name").Offset(ws1_1 _
.Range("MyRowNum").Value - 1, 60).Value, "#### <##-##")

And get a type mismatch.

How can I get this custom format at the destination cell?

Any help is really appreciated, Thanks.

-Minitman

hi
small problem. 123abc45 is text. you cannot "format" text. you can only
format numbers. that is where you are getting your type mismatch error.
for text you have to do something like this.
I was have trouble with your ranges so assuming 123abc45 is in a1.......
Sub stringtest()
Dim s As String
s = Range("A1").Value
Range("A2").Value = _
"'" & Left(s, 4) & "<" & Mid(s, 5, 2) & "" & Right(s, 2)
End Sub

regards
FSt1

"Minitman" wrote:

Greetings,

I am passing a string of characters in the format like this: 123abc45

I would like to have it show up after pasting as: 123A <BC-45

The code I am using to paste with now is:

Select Case Target.Column
Case 3
For i = 1 To 118
With ws1_1.Range("InvData" & i)
Select Case i
Case 4 'Cust List data
.Value = _
ws2_1.Range("MCL_Name").Offset(ws1_1 _
.Range("MyRowNum").Value - 1, 60).Value
I tried to wrap the last line with this format:

Format(ws2_1.Range("MCL_Name").Offset(ws1_1 _
.Range("MyRowNum").Value - 1, 60).Value, "#### <##-##")

And get a type mismatch.

How can I get this custom format at the destination cell?

Any help is really appreciated, Thanks.

-Minitman

Thanks for the reply FSt1,

Looking at the problem a bit deeper, it seems that this piece of data
is coming from a different workbook. Out of 118 cells, about 45 are
getting their figures from this external workbook. these are working
fine!!! I will have to look into why this one can't seem to see it's
data in this workbook.

I have to give up for tonight (I have an early morning appointment).

Since this a logic screw-up, trying to explain what is going in will
probably reveal the problem, if not I'll be back with better
information.

-Minitman

btw: I realized that I had asked this question in a slightly
different way back on July 8, 2007 and received these replies:

Target.Value = "Map " & UCase(Left(Target.Value, 4)) & " <" & _
UCase(Mid(Target.Value, 5, 2)) & "-" & Right(Target.Value, 2) & ""



This is going to seem like magic<g...

Your above line of code can be replaced with this...

Target.Value = Format(Target.Value, "!Map @@@@ \<@@-@@\")

Rick Rothstein \(MVP - VB\)

For your information :^)



On Mon, 6 Apr 2009 20:47:01 -0700, FSt1
wrote:

hi
small problem. 123abc45 is text. you cannot "format" text. you can only
format numbers. that is where you are getting your type mismatch error.
for text you have to do something like this.
I was have trouble with your ranges so assuming 123abc45 is in a1.......
Sub stringtest()
Dim s As String
s = Range("A1").Value
Range("A2").Value = _
"'" & Left(s, 4) & "<" & Mid(s, 5, 2) & "" & Right(s, 2)
End Sub

regards
FSt1

"Minitman" wrote:

Greetings,

I am passing a string of characters in the format like this: 123abc45

I would like to have it show up after pasting as: 123A <BC-45

The code I am using to paste with now is:

Select Case Target.Column
Case 3
For i = 1 To 118
With ws1_1.Range("InvData" & i)
Select Case i
Case 4 'Cust List data
.Value = _
ws2_1.Range("MCL_Name").Offset(ws1_1 _
.Range("MyRowNum").Value - 1, 60).Value
I tried to wrap the last line with this format:

Format(ws2_1.Range("MCL_Name").Offset(ws1_1 _
.Range("MyRowNum").Value - 1, 60).Value, "#### <##-##")

And get a type mismatch.

How can I get this custom format at the destination cell?

Any help is really appreciated, Thanks.

-Minitman