Macro - IsEmpty and Offset problems when trying to find "."

[ad19]

Hi everybody,

I wonder if someone could help. I'm writing some code to help calculate a variance between actual and target figures. Unfortunately, the spreadsheet sometimes contains . which need to be ignored, if not they skew the calculation. I've used the following code:

Sub ConvertPercentage()
Do
If IsEmpty(ActiveCell.Offset(0, -1)) And IsEmpty(ActiveCell.Offset(0, -2)) Or IsEmpty(ActiveCell.Offset(0, -1)) And ActiveCell.Offset(0, -2) >= 0 Or ActiveCell.Offset(0, -1) And IsEmpty(ActiveCell.Offset(0, -2)) Or ActiveCell.Offset(0, -1) = "." And ActiveCell.Offset(0, -2) = "." Or IsEmpty(ActiveCell.Offset(0, -1)) And ActiveCell.Offset(0, -2) = "." Or IsEmpty(ActiveCell.Offset(0, -2)) And ActiveCell.Offset(0, -1) = "." Then
ActiveCell.Value = ""
Else
Selection.ClearContents
ActiveCell.FormulaR1C1 = "=(RC[-1]-RC[-2])/RC[-2]"
ActiveCell.Select
Selection.NumberFormat = "0.0%"
End If
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell = "End"
End Sub

Unfortunately, i get a run time error. I've tried taking away the value command as "." is obviously not a value but that doesn't work either.

I'm a bit stuck and your help would be much appreciated.

Thanks,

ad

 

[ChrisM]

Really hard to read all that code, but couldn't you just loop through your range first, and replace all "." with ""?

If Cell(x,y) = "." Then Cell(x,y) = ""

 

[ad19]

Thanks Chris, will give that a go now

 

[ad19]

The only problem is that some cells contain values to one or two decimal places and so using replace will unfortunately cause problems. Unless there's a way to ignore certain cells with decimal points?

 

[ChrisM]

Sorry, I assumed from your earlier code that you were only testing for cells equal to ".". I don't see anything in your code about dealing with decimal numbers.

I'm only proposing if a cell is equal to ".", then replace it with a blank.

Can you be a little more clear about what you are trying to do?