Logo 
Search:

MS Office Forum

 Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

assigning text from cell to a variable

  Asked By: Wayne    Date: Feb 17    Category: MS Office    Views: 799
  

Can someone please tell me how to take the text in a cell and assign
it to a variable? I am trying to use the following code:

Dim A1str() As String
A1str = Range("A1").Value


but get the following error message:

-----------------------

Runtime-error '13':

Type mismatch

-----------------------


An example of the text in cell A1 is below (all in on line without
quotes), but will be different with each worksheet:

"CHR EGMLBMR Multilevel Bill Of
Materials Report 04/25/06 15:32 PAGE 1"

I ultimately want to shorten this text to contain 30 characters on
each side of "Multilevel Bill Of Materials Report", by removing extra
spaces, and write it back to cell A1 (as below).

"CHR EGMLBMR Multilevel Bill Of Materials
Report 04/25/06 15:32 PAGE 1"

My idea is to use Left$, Mid$, and Right$ functions to accomplish
this but can't seem to access the string in cell A1.

Share: 

 

3 Answers Found

 
Answer #1    Answered By: Ludwik Fischer     Answered On: Feb 17

A1str() is an array definition.

Do you want an array? You'll need to dimension it and use subscripts.

Do you just want a simple variable? Remove the ().

 
Answer #2    Answered By: Rayner Fischer     Answered On: Feb 17

I had found the solution last night after I
got home. I entered the code  by memory (forgetting the parenthesis)
in Excel 2003 (using Excel 2000 at work), and it worked. I didn't
know why until this morning when I read your reply. Thanks for your
explanation. I had the parenthesis in there because I copied from an
example. Now, I'll work on the rest of my idea.

 
Answer #3    Answered By: Adalie Fischer     Answered On: Feb 17


Try This

Sub Test()
Dim a as String
a = Range("A1").Value
MsgBox a
End Sub

 
Didn't find what you were looking for? Find more on assigning text from cell to a variable Or get search suggestion and latest updates.




Tagged:        

 
 
 
 

Related Post