Why does if activecell.value = range("S1") work only sometimes?

[nick1408]

I have the following code:

As you can see from the immediate window both cell values look the same but the code decides it isn't and skips to the 'Else' comment. Why would this be? I would expect the very next line to be completed in this instance. I have also tried using .text and get the same result from the code.

When the code looks for something that doesn't have a space (i.e. 'Cage1') it works. What is the best way to get the code to not care about the space?

Thanks!

 

[My Aswer Is This]

You need to copy the code and paste it in this forum like you have seen thousands of other poster do.

 

[JLGWhiz]

When the code looks for something that doesn't have a space (i.e. 'Cage1') it works. What is the best way to get the code to not care about the space?

You can use the Trim function to get rid of leading and trailing spaces. As an example

If Trim(Range("A1").Value) = Trimg(Range("B1").Value) Then
     'Do something
End If

 

[nick1408]

You can use the Trim function to get rid of leading and trailing spaces. As an example

If Trim(Range("A1").Value) = Trimg(Range("B1").Value) Then
     'Do something
End If

Thanks mate. It's actually the middle space that the activecell.value doesn't like. I'm not sure how to get the cell to ask if it is a match or not now.

 

[Michael M]

Check to make sure it really is a space !!
Is the data downloaded from another source ??
If so, it may be a NBSP not a whitespace.
Have a look here for code to TRIM all excess characters


Rearranging Data in Columns

 

[Norie]

You could remove all spaces using Replace.

If Replace(ActiveCell.Value, " ", "") = Replace(Range("B1").Value, " ", "") Then
     'Do something
End If