Convert

[billandrew]

Evening All

Question

I am converting a text value to a percentage with the below script. If there is no value in the cell I am receiving a #VALUE ! error.

What is the most efficient approach.

I believe I can insert a zero value if the cell is blank and then covert, however this does not seem very efficient.

Thank You

With Range("A1", Range("A" & Rows.Count).End(xlUp)).Value = Application.Evaluate("=" & .Address & "/" & 100)
.NumberFormat = "0.0%"
End With

 

[Countryboy69]

Not very good with code but try throwing a
Range = false,("")
I could be totally wrong

 

[Michael M]

This works for me

With Range("A1", Range("A" & Rows.Count).End(xlUp))
.Value = Application.Evaluate("=" & .Address & "/" & 100)
.NumberFormat = "0.0%"
End With

 

[billandrew]

Micheal

If a cell in the range is blank then the cell returns the #VALUE error

 

[billandrew]

@countryboy

Thank you, unfortunately not in this case.

 

[Michael M]

As I said it worked fine for me....blank or otherwise.
Are you sure they are really blank ?
Is it imported data ?

 

[billandrew]

I'm checking to see if the cell is actually blank.

The data is imported, any insight there?

Thanks

 

[billandrew]

You are right. the cell is not blank. Any ideas?

 

[Michael M]

what is actually in the "blank" cell ?
Try this
Use the Find and Replace box. Click in the Find box, hold Alt key and type 0160 on the number keypad then release Alt; leave the Replace box blank, and hit Replace All.

Then rerun the code

 

[billandrew]

There is a space. I need a script.

Thanks