What is the ActiveCell.Value of a wrapped cell

[Kanine]

Excel experts,

I am new to the forum and this is my first post. I have read the rules and guidelines, and I will do my best to follow them. I apologize if break any rules.

I am working on a macro that searches for certain cell values. I am struggling with the correct syntax of the cell value if wrap text is on. I have searched high and low for this but can't seem to word my searches correctly.

My code is:

If ActiveCell.Value = "Example cell value"

Where "Example cell value" is in a wrapped cell. "Example" is on the first line and "cell value" is on the second line. I have tried several underscore combinations but none have worked.

Any help is greatly appreciated.

 

[Kanine]

RoryA, I figured it was because of the quote marks, but now I know for sure, so thank you. I have double and triple checked to make sure I didn't just have a typo in there...

Joe4, thank you for this info. I have a really bad habit of hitting the space bar after typing a word, so thanks for bringing that up. I was very thoughtful in making sure I didn't do this the several times I re-typed the cell value because I wanted it to match exactly.

 

[Rick Rothstein]

This triggers the error message if either "name", "address", or "duration" is selected, but does not trigger the error message if "Customer Identification", or "Date mm/dd/yy" is selected. The only thing I can identify that is different is that they are in a cell with wrap text on. That is why I figured it had something to do with that. I tried re-typing these cells to remove any artificial lines and just let Excel wrap the text, and it did not trigger the error message. I tried re-typing these cells with the artificial line (with wrap text still on), added "& vbLf &", and it did not trigger the message. I also tried re-typing these cells with the artificial line (with wrap text off), and it still did not trigger the error message.

I am not sure why it is not working... I would point out that your test much match exactly, character-for-character and exact casing... since it is not matching, I can only conclude what you are checking for is not an exact match to what is in the cells. However, you have an easier test available to you that does not involve the individual tests you are now doing. ActiveCell is a range object and, as such, has a Row property... all you have to do is check if that Row property equals 1... if it does, you know you are on the header row somewhere which is not a correct location to be. So, I think you can change your test to this and I believe it should work the way you want...

[table="width: 500"]
[tr]
	[td]If ActiveCell.Value = "" Or ActiveCell.Value = 0 Or ActiveCell.Row = 1 Then
    MsgBox ("Make sure you select a name first."), vbOKOnly, "Whoops..."
    ActiveCell.Offset(, 1).Select
    Exit Sub
Else
....[/td]
[/tr]
[/table]

 

[Kanine]

I really thought that would work...unfortunately it is still doing the same thing. I changed the code, it will trigger the error message on "name", "address", and "duration", but still won't trigger on "Customer Identification" or "Date mm/dd/yy".

Thank you for the great description. I think this would be the way to go even if the original way was working as expected.

 

[Norie]

I'm sorry but this doesn't really make any sense.

 If ActiveCell.Value = "" Or ActiveCell.Value = 0 Or ActiveCell.Value = "Name" Or ActiveCell.Value = "Customer Identification" Or ActiveCell.Value = "Address" Or ActiveCell.Value = "Date mm/dd/yy" Or ActiveCell.Value = "Duration" Then

What exactly are you trying to find/look for in the active cell?

 

[Kanine]

I'm only trying to find the value of the active cell if it is in column B. If the active cell is any other cell in the worksheet, I want it to trigger the error message. My first attempt at that was:

If ActiveCell.Value = "" Or ActiveCell.value = 0 Then

I then realized if the active cell was a header cell, it would run the macro wrong and wouldn't trigger the error message. So, I listed them all as "Or" statements because the value of the header cells won't change.

I like Rick Rothstein's solution of using:

ActiveCell.Row = 1

But, when I tried it, it didn't change anything.
And, I'm wondering if I can use:

ActiveCell.Column <> B

But I'm not sure if that is the correct syntax. I tried it and it didn't seem to work as it would trigger the error message even if the active cell was in column B, but still wouldn't trigger on the two active cells mentioned in the previous reply.

 

[RoryA]

Column returns a number so you need to use:

ActiveCell.Column <> 2

 

[Rick Rothstein]

I'm only trying to find the value of the active cell if it is in column B. If the active cell is any other cell in the worksheet, I want it to trigger the error message.

Oh, is that what you want. Try this...

[table="width: 500"]
[tr]
	[td]If ActiveCell.Value = "" Or ActiveCell.value = 0 Or ActiveCell.Column <> 2 Or ActiveCell.Row = 1 Then
    MsgBox ("Make sure you select a name first."), vbOKOnly, "Whoops..."
    ActiveCell.Offset(, 1).Select
    Exit Sub
Else
....[/td]
[/tr]
[/table]

 

[Kanine]

Ah, should've thought of that. Thanks RoryA.

Thank you everyone for posting your thoughts and potential solutions. I am starting to think the workbook may have some corruption or something because I tried changing the header value to one word and it still won't trigger the error. I also went back to the original method and it still won't trigger. Maybe I formatted something that is preventing it and can't remember what it was.

I think I am going to try to rebuild the workbook from scratch and see if it will work then. I will update this thread if it does. Thanks again, everyone.

 

[Rick Rothstein]

I think I am going to try to rebuild the workbook from scratch and see if it will work then. I will update this thread if it does.

Our messages crossed... before you do that, see if what I posted in Message #17 works for you.

 

[Kanine]

Thanks Rick Rothstein. I tried your code and it works perfectly...except for those two cells for some reason.