| 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
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 |
|
#7
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 - |
|
#8
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 - |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
return an empty string in VBA
I get quotes appearing in the cell whether I use four quotes or six
quotes. Maybe it would help if I included the entire macro...there may be something else in there affecting this portion of code. So here's the entire macro: Option Explicit Sub CalculateWACC() Dim MyString1 As String, MyString2 As String Dim MyString3 As String, MyString4 As String Dim MyString5 As String, R As Variant Worksheets("Analysis").Range("A1:A7").ClearContent s 'Prompts the user for values to input to calculate the weighted average 'cost of capital (WACC), based on the formula 'c = (E/K) * y + (D/K) * b(1 - t) 'whe K = D + E 'c = weighted average cost of capital (%) 'y = required or expected return on equity (%) 'b = required or expected return on borrowings (%) 't = corporate tax rate (%) 'D = total debt and leases (currency) 'E = total equity and equity equivalents (currency) 'K = total capital invested in the going concern (currency) MyString1 = Application.InputBox("enter required or expected return on equity") MyString2 = Application.InputBox("enter required or expected return on debt") MyString3 = Application.InputBox("enter corporate tax rate") MyString4 = Application.InputBox("enter total debt and leases") MyString5 = Application.InputBox("enter total equity and equity equivalents") Worksheets("Analysis").Range("A1") = MyString1 Worksheets("Analysis").Range("A2") = MyString2 Worksheets("Analysis").Range("A3") = MyString3 Worksheets("Analysis").Range("A4") = MyString4 Worksheets("Analysis").Range("A5") = MyString5 Worksheets("Analysis").Range("A6").Select ActiveCell.Formula = "=Sum(A4 + A5)" Worksheets("Analysis").Range("A7").Select ActiveCell.Formula = "=(A5/A6)*A1+(A4/A6)*A2*(1-A3)" Application.EnableEvents = False For Each R In Range("A1:A7") If R.Value = False Then R.Value = """""" Next Application.EnableEvents = True End Sub On Aug 7, 12:24 pm, wrote: 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 oups.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 -- Hide quoted text - - Show quoted text - |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
return an empty string in VBA
Hi Peter,
I'm becoming more confused by the minute. <g When I put 4 sets of quotes in Dave F's code I get a single quote " returned in the cells. This is the first three " " " return the quote in the cell and the 4th is doing nothing. (spaces added just for illustration) -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk wrote in message ups.com... 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 - |
|
#11
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 |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
return an empty string in VBA
I get quotes appearing in the cell whether I use four quotes or six
quotes. And with your original two quotes you get two quotes as well? From your original post: ************************************************ The problem is this code returns the actual quote marks "" in the cells, not an empty string. ************************************************ If you want nothing in the cell why not use: If R.Value = False Then R.ClearContents -- HTH 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... I get quotes appearing in the cell whether I use four quotes or six quotes. Maybe it would help if I included the entire macro...there may be something else in there affecting this portion of code. So here's the entire macro: Option Explicit Sub CalculateWACC() Dim MyString1 As String, MyString2 As String Dim MyString3 As String, MyString4 As String Dim MyString5 As String, R As Variant Worksheets("Analysis").Range("A1:A7").ClearContent s 'Prompts the user for values to input to calculate the weighted average 'cost of capital (WACC), based on the formula 'c = (E/K) * y + (D/K) * b(1 - t) 'whe K = D + E 'c = weighted average cost of capital (%) 'y = required or expected return on equity (%) 'b = required or expected return on borrowings (%) 't = corporate tax rate (%) 'D = total debt and leases (currency) 'E = total equity and equity equivalents (currency) 'K = total capital invested in the going concern (currency) MyString1 = Application.InputBox("enter required or expected return on equity") MyString2 = Application.InputBox("enter required or expected return on debt") MyString3 = Application.InputBox("enter corporate tax rate") MyString4 = Application.InputBox("enter total debt and leases") MyString5 = Application.InputBox("enter total equity and equity equivalents") Worksheets("Analysis").Range("A1") = MyString1 Worksheets("Analysis").Range("A2") = MyString2 Worksheets("Analysis").Range("A3") = MyString3 Worksheets("Analysis").Range("A4") = MyString4 Worksheets("Analysis").Range("A5") = MyString5 Worksheets("Analysis").Range("A6").Select ActiveCell.Formula = "=Sum(A4 + A5)" Worksheets("Analysis").Range("A7").Select ActiveCell.Formula = "=(A5/A6)*A1+(A4/A6)*A2*(1-A3)" Application.EnableEvents = False For Each R In Range("A1:A7") If R.Value = False Then R.Value = """""" Next Application.EnableEvents = True End Sub On Aug 7, 12:24 pm, wrote: 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 oups.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 -- Hide quoted text - - Show quoted text - |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
return an empty string in VBA
OK, entering
If R.Value = False Then R.clearcontents seems to have resolved this issue. Simple fix, thanks. Dave On Aug 7, 12:45 pm, "Sandy Mann" wrote: I get quotes appearing in the cell whether I use four quotes or six quotes. And with your original two quotes you get two quotes as well? From your original post: ************************************************ The problem is this code returns the actual quote marks "" in the cells, not an empty string. ************************************************ If you want nothing in the cell why not use: If R.Value = False Then R.ClearContents -- HTH 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... I get quotes appearing in the cell whether I use four quotes or six quotes. Maybe it would help if I included the entire macro...there may be something else in there affecting this portion of code. So here's the entire macro: Option Explicit Sub CalculateWACC() Dim MyString1 As String, MyString2 As String Dim MyString3 As String, MyString4 As String Dim MyString5 As String, R As Variant Worksheets("Analysis").Range("A1:A7").ClearContent s 'Prompts the user for values to input to calculate the weighted average 'cost of capital (WACC), based on the formula 'c = (E/K) * y + (D/K) * b(1 - t) 'whe K = D + E 'c = weighted average cost of capital (%) 'y = required or expected return on equity (%) 'b = required or expected return on borrowings (%) 't = corporate tax rate (%) 'D = total debt and leases (currency) 'E = total equity and equity equivalents (currency) 'K = total capital invested in the going concern (currency) MyString1 = Application.InputBox("enter required or expected return on equity") MyString2 = Application.InputBox("enter required or expected return on debt") MyString3 = Application.InputBox("enter corporate tax rate") MyString4 = Application.InputBox("enter total debt and leases") MyString5 = Application.InputBox("enter total equity and equity equivalents") Worksheets("Analysis").Range("A1") = MyString1 Worksheets("Analysis").Range("A2") = MyString2 Worksheets("Analysis").Range("A3") = MyString3 Worksheets("Analysis").Range("A4") = MyString4 Worksheets("Analysis").Range("A5") = MyString5 Worksheets("Analysis").Range("A6").Select ActiveCell.Formula = "=Sum(A4 + A5)" Worksheets("Analysis").Range("A7").Select ActiveCell.Formula = "=(A5/A6)*A1+(A4/A6)*A2*(1-A3)" Application.EnableEvents = False For Each R In Range("A1:A7") If R.Value = False Then R.Value = """""" Next Application.EnableEvents = True End Sub On Aug 7, 12:24 pm, wrote: 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 oups.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 -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#14
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)
Linear Mode
