I have tried to deploy the 2 MID concepts-Statement vs. Function t
effect replacements in a range-----and have gotten stuck in a spin.

Consider, lines of string like:

*** CODE1 Begins here ****
<some other lines
*** CODE2 Begins here ****
<some other lines
etc. etc. etc.

occupying Range("A1:A2150").

I need to convert to comments all lines beginning with asterick (*) an
have them read as follows:

Rem CODE1 Begins here ****
<some other lines
Rem CODE2 Begins here ****

The code I stitched up (largely to test out the nuances of the MI
FUNCTION and MID STATEMENT) is:


Sub CommentOutWithRem()

Dim c as variant ' as Range or Object produces MisMatch errors
For each c in Range("a1:a2150")
If Mid(c,1,1)="*" Then Mid(c,1,3)="REM"
*'the sticky point follows in making an assignment:
'note, no action takes place if the code ends here.
*
c.value =c.value 'error: object required
c.Value = Mid(c, 1, 3) = "REM" 'error: object required
End if

next

End sub


Can someone kindly unknot the problem here? [I have very easily applie
other routine constructs to do what I want the code to do but there ar
obviously useful lessons to be learnt about theinteraction of the MI
function and its kin, the statement.

Many thanks in advance

--
david
-----------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...fo&userid=2064
View this thread: http://www.excelforum.com/showthread.php?threadid=47112

Sub CommentOutWithRem()

Dim c as Range
Dim sStr as String
For each c in Range("a1:a2150")
If Mid(c,1,1)="*" Then
sStr = c.Value
Mid(sStr,1,3) = "REM"
c.value =sStr
End if
next

--
Regards,
Tom Ogilvy


"davidm" wrote in
message ...

I have tried to deploy the 2 MID concepts-Statement vs. Function to
effect replacements in a range-----and have gotten stuck in a spin.

Consider, lines of string like:

*** CODE1 Begins here ****
<some other lines
*** CODE2 Begins here ****
<some other lines
etc. etc. etc.

occupying Range("A1:A2150").

I need to convert to comments all lines beginning with asterick (*) and
have them read as follows:

Rem CODE1 Begins here ****
<some other lines
Rem CODE2 Begins here ****

The code I stitched up (largely to test out the nuances of the MID
FUNCTION and MID STATEMENT) is:


Sub CommentOutWithRem()

Dim c as variant ' as Range or Object produces MisMatch errors
For each c in Range("a1:a2150")
If Mid(c,1,1)="*" Then Mid(c,1,3)="REM"
*'the sticky point follows in making an assignment:
'note, no action takes place if the code ends here.
*
c.value =c.value 'error: object required
c.Value = Mid(c, 1, 3) = "REM" 'error: object required
End if

next

End sub


Can someone kindly unknot the problem here? [I have very easily applied
other routine constructs to do what I want the code to do but there are
obviously useful lessons to be learnt about theinteraction of the MID
function and its kin, the statement.

Many thanks in advance.


--
davidm
------------------------------------------------------------------------
davidm's Profile:
http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=471123

Thanks Tom. Greatly appreciated. That was a very percipient way o
weaseling between the string and range attributes of the variable
-so much a feature of FOR...EACH constructs . The intermediate sSt
string declaration held the key. I have seen ebough of your brillian
logic not to ever allow myself to be entangled by these two MI
counsins. Once again, gracias!

davi

--
david
-----------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...fo&userid=2064
View this thread: http://www.excelforum.com/showthread.php?threadid=47112

Such profundity in the pronouncement of your profuse praise can not go
unpondered. I do appreciate your praise; alas I am but a progeny of the
prolific posters of the past who have chosen to posit their profound
postulates in this most productive port of Excel knowledge.


--
Regards,
Tom Ogilvy



"davidm" wrote in
message ...

Thanks Tom. Greatly appreciated. That was a very percipient way of
weaseling between the string and range attributes of the variable c
-so much a feature of FOR...EACH constructs . The intermediate sStr
string declaration held the key. I have seen ebough of your brilliant
logic not to ever allow myself to be entangled by these two MID
counsins. Once again, gracias!

david


--
davidm
------------------------------------------------------------------------
davidm's Profile:
http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=471123

Tom,
Any need to check really ?

c.Value = Replace(c.Value, "*** ", "REM ")

Nick

"Tom Ogilvy" wrote in message
...
Sub CommentOutWithRem()

Dim c as Range
Dim sStr as String
For each c in Range("a1:a2150")
If Mid(c,1,1)="*" Then
sStr = c.Value
Mid(sStr,1,3) = "REM"
c.value =sStr
End if
next

--
Regards,
Tom Ogilvy


"davidm" wrote in
message ...

I have tried to deploy the 2 MID concepts-Statement vs. Function to
effect replacements in a range-----and have gotten stuck in a spin.

Consider, lines of string like:

*** CODE1 Begins here ****
<some other lines
*** CODE2 Begins here ****
<some other lines
etc. etc. etc.

occupying Range("A1:A2150").

I need to convert to comments all lines beginning with asterick (*) and
have them read as follows:

Rem CODE1 Begins here ****
<some other lines
Rem CODE2 Begins here ****

The code I stitched up (largely to test out the nuances of the MID
FUNCTION and MID STATEMENT) is:


Sub CommentOutWithRem()

Dim c as variant ' as Range or Object produces MisMatch errors
For each c in Range("a1:a2150")
If Mid(c,1,1)="*" Then Mid(c,1,3)="REM"
*'the sticky point follows in making an assignment:
'note, no action takes place if the code ends here.
*
c.value =c.value 'error: object required
c.Value = Mid(c, 1, 3) = "REM" 'error: object required
End if

next

End sub


Can someone kindly unknot the problem here? [I have very easily applied
other routine constructs to do what I want the code to do but there are
obviously useful lessons to be learnt about theinteraction of the MID
function and its kin, the statement.

Many thanks in advance.


--
davidm
------------------------------------------------------------------------
davidm's Profile:
http://www.excelforum.com/member.php...o&userid=20645
View this thread:
http://www.excelforum.com/showthread...hreadid=471123

True, but why lumber along with that. (which doesn't work in xl97 and
earlier by the way)

Select the column and do

Edit=Replace
What ***<spc
With REM<spc

and don't select entire word.

But then he asked about using MID.

--
Regards,
Tom Ogilvy



"NickHK" wrote in message
...
Tom,
Any need to check really ?

c.Value = Replace(c.Value, "*** ", "REM ")

Nick

"Tom Ogilvy" wrote in message
...
Sub CommentOutWithRem()

Dim c as Range
Dim sStr as String
For each c in Range("a1:a2150")
If Mid(c,1,1)="*" Then
sStr = c.Value
Mid(sStr,1,3) = "REM"
c.value =sStr
End if
next

--
Regards,
Tom Ogilvy


"davidm" wrote in
message ...

I have tried to deploy the 2 MID concepts-Statement vs. Function to
effect replacements in a range-----and have gotten stuck in a spin.

Consider, lines of string like:

*** CODE1 Begins here ****
<some other lines
*** CODE2 Begins here ****
<some other lines
etc. etc. etc.

occupying Range("A1:A2150").

I need to convert to comments all lines beginning with asterick (*)
and
have them read as follows:

Rem CODE1 Begins here ****
<some other lines
Rem CODE2 Begins here ****

The code I stitched up (largely to test out the nuances of the MID
FUNCTION and MID STATEMENT) is:


Sub CommentOutWithRem()

Dim c as variant ' as Range or Object produces MisMatch errors
For each c in Range("a1:a2150")
If Mid(c,1,1)="*" Then Mid(c,1,3)="REM"
*'the sticky point follows in making an assignment:
'note, no action takes place if the code ends here.
*
c.value =c.value 'error: object required
c.Value = Mid(c, 1, 3) = "REM" 'error: object required
End if

next

End sub


Can someone kindly unknot the problem here? [I have very easily
applied
other routine constructs to do what I want the code to do but there
are
obviously useful lessons to be learnt about theinteraction of the MID
function and its kin, the statement.

Many thanks in advance.


--
davidm

------------------------------------------------------------------------
davidm's Profile:
http://www.excelforum.com/member.php...o&userid=20645
View this thread:
http://www.excelforum.com/showthread...hreadid=471123