text from a cell to VBA

[carfreak01]

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.

 

[cummings54]

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,

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

 

[carfreak01]

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.

 

[cummings54]

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

 

[carfreak01]

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.

 

[carfreak01]

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?

 

[IRstuff]

Did you format the column as text?

TTFN

FAQ731-376

 

[carfreak01]

Yes I did, and still nothing happend.

 

[melone]

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

 

[Panars]

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.

 

[carfreak01]

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

Jose Luis Walters