×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

text from a cell to VBA

text from a cell to VBA

text from a cell to VBA

(OP)
Hello everybody,

I need help. I'm making a VBA code to work with an excel sheet. One of the functions of the VBA code is to do something depending on the value of a cell. The value of the cell is a text value, if the cell reads "END" then the code has to do something. The problem is that when I input the word END to the cell the code does not work, but if i input: ="END" then the code works. In my first attempt to make the code everything worked fine, but then I deleted a column that I wasn't using and the problem with the text in the cell started. I don't know if I moved something by mistake but I don't seem to make it work again.
Please help me because I ran out of ideas.

Jose Luis Walters.

RE: text from a cell to VBA

END is a VB keyword.  It is usually not a good idea to use it in code for other purposes than what it was intended.  If you must use END, then try to reference the input cell data using the text property.  For example,

CODE

dim MyInput as String
MyInput= Range("A1").Text

RE: text from a cell to VBA

(OP)
Hi cummings54, it didn't work the way you told me, here is the code:

Dim FinalRow As String, NextRow As Integer
    FinalRow = Range("A1000").End(xlUp)
    If FinalRow = "FIN" Then
        Sheets("S2").Select
        NextRow = Worksheets("S2").Range("A1000").End
                  (xlUp).Row + 1
        Worksheets("S2").Cells(NextRow, 1).Select
    End If

I'm using the word "FIN", END in spanish, and my version of office is in english.
The code has to detect the last used cell in column A and if the text in the cell reads "FIN" it has to select the next empty cell in column A in sheet "S2". But i have to input ="FIN" to the cell to make it work, but like I said in my first attempt it worked fine with just writing FIN into the cell.

RE: text from a cell to VBA

Hi CarFreak01,
Your code works fine on my machine even using END
Go figure...

RE: text from a cell to VBA

(OP)
I forgot to mention that excel runs the code everytime the active worksheet is calculated. So if you write FIN or END in the cell, when you hit enter you should be directed to next empty cell of sheet S2 in the column A.

RE: text from a cell to VBA

(OP)
I wrote a simple code to make a test and it seems like I have to write in the cells ="FIN" or =123 so that the VB code can read it if I don't use the = it seems that the VB code can't read it.
Can anyone help me with this problem?

RE: text from a cell to VBA

(OP)
Yes I did, and still nothing happend.

RE: text from a cell to VBA

Would using "section.value" or "activecell.value" help?

RE: text from a cell to VBA

I think the problem is that when you type FIN in a cell, the worksheet is not recalculating so the code doesn't run.  When you type ="FIN" the worksheet is recalculating and your code runs.

Try using the Change event instead of the Calculate event.  Or use both if you need to.

RE: text from a cell to VBA

(OP)
Hello Panars, your idea worked. Thank you all for replying and thank you all very much for your help.

Jose Luis Walters

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources