return an empty string in VBA

[Dave F]

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

 

[permarbor]

Instead of = "", you need to write = """"
So there are 4 sets of double inverted commas rather than the usual 2.

Hope this helps,

Peter

 

[Dave F]

Hi, I tried that and it still returns ""

Dave

Instead of = "", you need to write = """"
So there are 4 sets of double inverted commas rather than the usual 2.

Hope this helps,

Peter






- Show quoted text -

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Dave F]

Not sure what you mean in your post. Putting in 6 double quote marks
returns ""

Dave

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[permarbor]

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

 

[Dave F]

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").ClearContents
'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)
'where: 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

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Ron Rosenfeld]

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

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Dave F]

OK, entering

If R.Value = False Then R.clearcontents

seems to have resolved this issue.

Simple fix, thanks.

Dave

 

[Otto Moehrbach]

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