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.

 

[RoryA]

Wrap text doesn't affect the value in the cell. Perhaps a manual line feed was also added, in which case the value would be something like "Example" & vbLf & "cell value"

 

[Rick Rothstein]

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.

If Excel automatically wrapped the text, then that text is actually all on one line... the wrapping is completely visual, and so, what you wrote should have worked (once you add the missing "Then" keyword). If the second line was artificially created by the user pressing ALT+ENTER, then that would be a different story. I have read your posting a couple of times now and I am still unsure exactly what problem you are having... could you please elaborate what the problem is for us?

 

[Norie]

There could be a line feed/carraige return character in the cell which could cause problems when comparing.

 

[Kanine]

Wow, thank you both for the quick response.

RoryA, I thought it was weird that it would affect it, but I figured it was similar to wrapping the text of a msgbox. I am new to VBA so thank you for the information.

Rick Rothstein, I have a habit of doing either, and sometimes both. I just re-typed the cell value letting Excel wrap the text and tested the existing code and it still did not work. I also tried adding vbLf, but again it did not work.

A little more info, I have a code that is supposed to find a match to the active cell value on another worksheet. However, it is only supposed to be for the values of a specific column. I have an error message that comes up if the button is clicked when the cursor is in a different column. At first, I just had it trigger if the active cell value was nothing, then realized it would trigger on the headers of the sheet. So, I added to the "If Then" statement (yes, I have the "Then" keyword, just left it out of the copy/paste to the forum) an "Or" statement to trigger the error message if one of the header cells was selected. It works for 3/5 headers, but two have the wrap text function on, and it is failing to trigger the error message on these two header cells.

 

[Kanine]

I also forgot to mention that there are no merged cells. I just increased the row height.

Norie, thank you for the response. I have re-typed it several times now, letting Excel wrap the text, and taking the wrap text function off and manually wrapping it. I tried the recommended code above and it still doesn't want to work right.

 

[Rick Rothstein]

...and it still doesn't want to work right.

Describe for us what "doesn't want to work right" means... tell what your data is, what code you are using, what you think should happen and what you see happening that is wrong.

 

[RoryA]

Just in case you aren't aware, your test is case sensitive so the two texts must match exactly.

 

[Joe4]

Just in case you aren't aware, your test is case sensitive so the two texts must match exactly.

Also be on the lookout for trailing/extra spaces. That can cause your strings not to match.

 

[Kanine]

My apologies Rick Rothstein, I am doing this for work and they are very strict on confidentiality so I am trying not to share too much. Let's see if I can appropriately elaborate.

I have rows of records on the first worksheet (5 columns worth of info). For example, B2 has a name, C2 has an identification, D2 has an address, E2 has a date, and F2 has a formula. Row 1 is used for the headers of the columns and has a height of 30 (to fit two lines of text if needed).

There is a second worksheet that has more specific detail related to the rows of records on the first worksheet. I have used the A column on the first sheet to add a record ID that the macro uses to find the respective specific detail on the second sheet related to the record selected on the first sheet.

The user will select the name in column B, click the button, and it will find the related specific detail. I put an error message in incase the user has a cell selected that is not in column B. I realized if the user selected one of the header cells (B1:F1), it would not trigger the error message and would run the rest of the macro wrong. So, I added this code:

 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
                        MsgBox ("Make sure you select a name first."), vbOKOnly, "Whoops..."
                        ActiveCell.Offset(, 1).Select
                        Exit Sub
                    Else

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 hope this is enough information. Please let me know if you need any more detail. Thank you again for your help with this.