| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
return an empty string in VBA
I'm trying to use the following code to return an empty string in a
range of cells if any value in the range is FALSE: Application.EnableEvents = False For Each R In Range("A1:A7") If R.Value = False Then R.Value = "" Next Application.EnableEvents = True The problem is this code returns the actual quote marks "" in the cells, not an empty string. R is set as a variant. What am I doing wrong? Thanks. Dave |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
return an empty string in VBA
Instead of = "", you need to write = """"
So there are 4 sets of double inverted commas rather than the usual 2. Hope this helps, Peter On 7 Aug, 16:31, Dave F wrote: I'm trying to use the following code to return an empty string in a range of cells if any value in the range is FALSE: Application.EnableEvents = False For Each R In Range("A1:A7") If R.Value = False Then R.Value = "" Next Application.EnableEvents = True The problem is this code returns the actual quote marks "" in the cells, not an empty string. R is set as a variant. What am I doing wrong? Thanks. Dave |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
return an empty string in VBA
Hi, I tried that and it still returns ""
Dave On Aug 7, 11:38 am, wrote: Instead of = "", you need to write = """" So there are 4 sets of double inverted commas rather than the usual 2. Hope this helps, Peter On 7 Aug, 16:31, Dave F wrote: I'm trying to use the following code to return an empty string in a range of cells if any value in the range is FALSE: Application.EnableEvents = False For Each R In Range("A1:A7") If R.Value = False Then R.Value = "" Next Application.EnableEvents = True The problem is this code returns the actual quote marks "" in the cells, not an empty string. R is set as a variant. What am I doing wrong? Thanks. Dave- Hide quoted text - - Show quoted text - |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
return an empty string in VBA
Not sure what you mean in your post. Putting in 6 double quote marks
returns "" Dave On Aug 7, 11:51 am, "Sandy Mann" wrote: wrote in message ps.com... Instead of = "", you need to write = """" So there are 4 sets of double inverted commas rather than the usual 2. I need 6 sets to return "" ie """ returns " I was assuming that the OP wanted an empty string as in IF(A1=10,""...... his code returns what I would call an empty string as it is - LEN(A1) is zero and =ISTEXT(A1) is FALSE -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk wrote in message ps.com... Instead of = "", you need to write = """" So there are 4 sets of double inverted commas rather than the usual 2. Hope this helps, Peter On 7 Aug, 16:31, Dave F wrote: I'm trying to use the following code to return an empty string in a range of cells if any value in the range is FALSE: Application.EnableEvents = False For Each R In Range("A1:A7") If R.Value = False Then R.Value = "" Next Application.EnableEvents = True The problem is this code returns the actual quote marks "" in the cells, not an empty string. R is set as a variant. What am I doing wrong? Thanks. Dave- Hide quoted text - - Show quoted text - |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
return an empty string in VBA
Hi Dave,
I was actually replying to "permarbro" who said to use 4 sets of quotes to get "". I was saying, as you said, I need 6 sets to return "" But I am confused now as to what it is that you want. When I run your code, (XL97), I get nothing showing in the cells not "" which is what you said you get. -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Dave F" wrote in message ups.com... Not sure what you mean in your post. Putting in 6 double quote marks returns "" Dave On Aug 7, 11:51 am, "Sandy Mann" wrote: wrote in message ps.com... Instead of = "", you need to write = """" So there are 4 sets of double inverted commas rather than the usual 2. I need 6 sets to return "" ie """ returns " I was assuming that the OP wanted an empty string as in IF(A1=10,""...... his code returns what I would call an empty string as it is - LEN(A1) is zero and =ISTEXT(A1) is FALSE -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk wrote in message ps.com... Instead of = "", you need to write = """" So there are 4 sets of double inverted commas rather than the usual 2. Hope this helps, Peter On 7 Aug, 16:31, Dave F wrote: I'm trying to use the following code to return an empty string in a range of cells if any value in the range is FALSE: Application.EnableEvents = False For Each R In Range("A1:A7") If R.Value = False Then R.Value = "" Next Application.EnableEvents = True The problem is this code returns the actual quote marks "" in the cells, not an empty string. R is set as a variant. What am I doing wrong? Thanks. Dave- Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
return an empty string in VBA
Sandy, I was trying to show Dave how to get an empty cell, not how to
obtain "" in a cell, which is why I said to use 4 sets of double inverted commas, not 6. Hopefully that clears the confusion! Peter On 7 Aug, 17:18, "Sandy Mann" wrote: Hi Dave, I was actually replying to "permarbro" who said to use 4 sets of quotes to get "". I was saying, as you said, I need 6 sets to return "" But I am confused now as to what it is that you want. When I run your code, (XL97), I get nothing showing in the cells not "" which is what you said you get. -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Dave F" wrote in message ups.com... Not sure what you mean in your post. Putting in 6 double quote marks returns "" Dave On Aug 7, 11:51 am, "Sandy Mann" wrote: wrote in message oups.com... Instead of = "", you need to write = """" So there are 4 sets of double inverted commas rather than the usual 2. I need 6 sets to return "" ie """ returns " I was assuming that the OP wanted an empty string as in IF(A1=10,""...... his code returns what I would call an empty string as it is - LEN(A1) is zero and =ISTEXT(A1) is FALSE -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk wrote in message oups.com... Instead of = "", you need to write = """" So there are 4 sets of double inverted commas rather than the usual 2. Hope this helps, Peter On 7 Aug, 16:31, Dave F wrote: I'm trying to use the following code to return an empty string in a range of cells if any value in the range is FALSE: Application.EnableEvents = False For Each R In Range("A1:A7") If R.Value = False Then R.Value = "" Next Application.EnableEvents = True The problem is this code returns the actual quote marks "" in the cells, not an empty string. R is set as a variant. What am I doing wrong? Thanks. Dave- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
return an empty string in VBA
Do you mean you actually see the two quote marks in the cell after the code
is run? That is not what happens on my system... the cell is totally empty. Rick "Dave F" wrote in message ps.com... I'm trying to use the following code to return an empty string in a range of cells if any value in the range is FALSE: Application.EnableEvents = False For Each R In Range("A1:A7") If R.Value = False Then R.Value = "" Next Application.EnableEvents = True The problem is this code returns the actual quote marks "" in the cells, not an empty string. R is set as a variant. What am I doing wrong? Thanks. Dave |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
return an empty string in VBA
On Tue, 07 Aug 2007 08:31:33 -0700, Dave F wrote:
I'm trying to use the following code to return an empty string in a range of cells if any value in the range is FALSE: Application.EnableEvents = False For Each R In Range("A1:A7") If R.Value = False Then R.Value = "" Next Application.EnableEvents = True The problem is this code returns the actual quote marks "" in the cells, not an empty string. R is set as a variant. What am I doing wrong? Thanks. Dave It sounds as if you want to have an empty cell. If that is the case, try: If R.Value = False Then R.clearcontents --ron |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
return an empty string in VBA
Dave
To "return an empty string" means that you want to see a blank cell. Apparently that is not what you want to see. What do want to see? Otto "Dave F" wrote in message ps.com... I'm trying to use the following code to return an empty string in a range of cells if any value in the range is FALSE: Application.EnableEvents = False For Each R In Range("A1:A7") If R.Value = False Then R.Value = "" Next Application.EnableEvents = True The problem is this code returns the actual quote marks "" in the cells, not an empty string. R is set as a variant. What am I doing wrong? Thanks. Dave |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Help on filling empty cells with string + row() | Excel Discussion (Misc queries) | |||
| Return an empty or blank cell value? | Excel Worksheet Functions | |||
| return an EMPTY cell in an IF formula | Excel Discussion (Misc queries) | |||
| Return an empty cell | Excel Discussion (Misc queries) | |||
| how do I remove empty spaces trailing a text string? | Excel Worksheet Functions | |||
![Dave F[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
