Do-while loop to erase cells with certain values in Excel -
i want delete cells have '+' , '-' in d column. i've tried following macro, thought work, nothing happens.
sub dowhile4() 'replace blank spaces underscores in range of cells, using vba loops; or 'remove blank spaces in range of cells, using vba loops. dim icell range dim textstring string dim n integer 'icell cell in specified range contains textstring 'textstring text in cell in blank spaces replaced 'underscores. n position of blank space(s) occurring in textstring each icell in activesheet.range("d2:d34") textstring = icell n = instr(textstring, "+") 'the vba instr function returns position of first occurrence of string within 'another string. using determine position of first blank space in 'textstring. while n > 0 textstring = left(textstring, n - 1) & right(textstring, len(textstring) - n) 'this line of code remove blank spaces in 'textstring n = instr(textstring, "+") loop icell = textstring next end sub
what doing wrong?
try clear out values if cell contains either + or -
option explicit sub dowhile4() 'replace blank spaces underscores in range of cells, using vba loops; or 'remove blank spaces in range of cells, using vba loops. dim icell range dim textstring string dim n integer dim hasplus boolean dim hasminus boolean 'icell cell in specified range contains textstring 'textstring text in cell in blank spaces replaced 'underscores. n position of blank space(s) occurring in textstring each icell in activesheet.range("d2:d34") textstring = icell 'reset our boolean variables each iteration hasplus = false hasminus = false if instr(textstring, "+") > 0 hasplus = true if instr(textstring, "-") > 0 hasminus = true 'the vba instr function returns position of first occurrence of string within 'another string. using determine position of first blank space in 'textstring. if hasplus or hasminus icell.value = vbnullstring next end sub
Comments
Post a Comment