Carriage return VLOOKUP issue

[chirayu]

Hi guys,

This is an odd one & I can't seem to get past it. Problem below:

- I have 2 sheets e.g. Sheet1 & Sheet2
- Trying to do a VLOOKUP in Sheet2 (Main table is on Sheet1)
- VBA gives an error because Sheet1 name has a carriage return in it
- Tried storing sheet name as a String variable but its not working

 

[Marc L]

Hi,

so just remove CR from worksheet's name …
​

 

[chirayu]

yeah I ended up doing below

Sht = ActiveSheet.Name
Sht = Replace(Sht, chr(10), "")
ActiveSheet.Name = Sht

 

[Marc L]

So I was not a CR but a LF (line feed) !
A must see in VBA inner help : VB constants like vbCr and vbLf …

Activesheet.Name = Replace(Activesheet.Name, vbLf, "")
​