excel - Remove comma and anything after VBA from cell -

i have following code use remove after space.

for = 2 lastrow33     if instr(ws3.cells(i, 28), " ") > 0         y = replace(ws3.cells(i, 28), " *", "")         x = left(y, instr(y, " ") - 1)         ws3.cells(i, 28) = x     end if next 

i'm trying modify remove comma end of string , after it. keep getting overflow though haven't declared integers longs.

for = 2 lastrow33     if instr(ws3.cells(i, 28), " ") > 0         y = replace(ws3.cells(i, 28), ",*", "")         x = left(y, instr(y, " ") - 1)         ws3.cells(i, 28) = x     end if next 

any idea might doing wrong? declarations below

dim lastrow33 long dim ws3 worksheet dim long, j long 

you checking if space present in cell, not comma. changed code accordingly.

for = 2 lastrow33     if instr(ws3.cells(i, 28), ",") > 0              `changed space comma here         y = replace(ws3.cells(i, 28), ",*", "")         x = left(y, instr(y, ",") - 1)                    `changed space comma here         ws3.cells(i, 28) = x     end if next 

edit:

the overflow error can originate instr function return integer. ofcourse depends on length of cell text.

however basicly doing same operation twice. under y replace comma , after "".

then under x going in result of y comma, isn't there anymore due operation in y, , take characters left of that. shorter code same be:

for = 2 lastrow33     if instr(ws3.cells(i, 28), ",") > 0         ws3.cells(i, 28) = replace(ws3.cells(i, 28), ",*", "")     end if next 

if still results in overflow caused instr result being large stored in integer. can force value long using following:

for = 2 lastrow33     if clng(instr(ws3.cells(i, 28), ",")) > 0         ws3.cells(i, 28) = replace(ws3.cells(i, 28), ",*", "")     end if next