MID STATEMENT vs MID FUNCTION

  • Thread starter Thread starter davidm
  • Start date Start date
D

davidm

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
 
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
 
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
 
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.
 
Tom,
Any need to check really ?

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

Nick

Tom Ogilvy said:
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 said:
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.
Click to expand...
Click to expand...
 
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 said:
Tom,
Any need to check really ?

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

Nick

Tom Ogilvy said:
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 said:
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.
Click to expand...
------------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=20645
View this thread: http://www.excelforum.com/showthread.php?threadid=471123
Click to expand...
Click to expand...
Click to expand...
 
Back
Top