Remove Spaces from a Cell using VBA

Hi All,

I am working on a userform that uses a lookup function from one of the text fields. I am using a UK postcode (i.e L27 9HD) and using the LEFT, LEN function to remove the last 4 characters so I can then use the remaining L27 part of the postcode to Lookup the value I need.

Here is the code I have used and it works fine:

WorksheetFunction.VLookup(Left(txtPost, Len(txtPost) - 4), Sheets("Postcodes").Range("A1:E5000"), 5, False)

The problem with this is that it relies on the user inputting the postcode with the 'space' (i.e L27 9HD not L279HD). If the user forgets the space then the code will go into error. I tried the following formula that removes the space between the postcode: =SUBSTITUTE(D1," ","") and then just removed the last 3 characters.

This works in Excel but I can't seem to get it to work in VBA. I basically want the formula to remove the space if it is there or just proceed if it isn't. My code therefore is:

WorksheetFunction.VLookup(Left(SUBSTITUTE(txtPost, " ",""), Len(txtPost) - 4), Sheets("Postcodes").Range("A1:E5000"), 5, False)

The trouble is that the VBA debug doesn't like the SUBSTITUTE formula. Am I missing something in the formula or is there another way around this?

Thanks