MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Mid statement

  Asked By: Oscar    Date: Feb 01    Category: MS Office    Views: 948

I am trying to replace 3 characters in a string using the following
statement but get an error message saying "variable not acceptable".

Mid(Range("E2"), 20, 3) = "IKL



4 Answers Found

Answer #1    Answered By: Seth Anderson     Answered On: Feb 01

The error  message I get is "Variable required - can't assign to this

I.e. a variable  is not "not acceptable", a variable is required.

What version of Excel are you using? (I'm using 2003.)

What your statement  is trying to do is to replace  3 characters  in the
result of Range("E2"). Or more specifically Range("E2").Value. This is
a local value which is created to contain the value of the cell - it's
not the cell itself.

To do what you want to do, I imagine you will need to create a local
variable, put the contents of the cell into it, change the local
variable's contents with Mid, then put them back into the cell.

Answer #2    Answered By: Jeanne Lawson     Answered On: Feb 01

Is it possible it does not like the Range within the MID? Try breaking it
into three statements

Tmpstr = Range("E3")

Mid(Tmpstr), 20, 3) = "IKL"

Range("E3" = Tmpstr

Answer #3    Answered By: Reamonn Fischer     Answered On: Feb 01

Thanks for the assist............

Answer #4    Answered By: Aaron Evans     Answered On: Feb 01

The problem is with the definition of "Range".
the range is a "object".
the range object:
"Represents a cell, a row, a column, a selection of cells containing one or more
contiguous blocks of cells" (msoffice help)
this object has Properties. the DEFAULT property happens to be Value.
It isn't REALLY a variable  that can be set to a string, it merely points to the
location and the PROPERTY is set to the value of the cell.
MID is a function that:
"Returns a Variant (String) containing a specified number of characters  from a
string" (MSOffice Help)
It wasn't really intended to be set equal to ANYTHING.
Like any other function, you pass it values (by reference),
it does it's MicroSoft Magic, and returns the result.
I think the fact that John's example:
Tmpstr = Range("E3")
Mid(Tmpstr, 20, 3) = "IKL"
Range("E3") = Tmpstr
works at all is unintentional (on MicroSoft's part).
In fact, it is not something I would've even TRIED because it clearly defies my
definition of how a function should work.
To me, it is like saying my car is out of gas, so I'll pour Diet Coke into the
tank. Clearly, the car is designed to run on fuel that BURNS, so why should I
think that something that doesn't burn will work?
Logically, what I would've done is something like:
Range("E2") = Left(Range("E2").Value, 20) & "IKL" & _
Mid(Range("E2").Value, 24, Len(Range("E2")))
Here, you're taking the leftmost 20 characters, concatenating "IKL", then using
the MID function to return the characters following the 23 character to the end
of the string  (the LEN() function is overkill, it will stop when it gets to the

Didn't find what you were looking for? Find more on Mid statement Or get search suggestion and latest updates.