Problem with if statement and empty cells

**ExcelsiorLux** · 06-14-2013, 02:12 PM

Hi there! So yeah im pretty new to VBA and I am trying for hours to use an if statement to apply a function only if the values of my column is a number (not empty). I have been using :

HTML Code:

But it seems that it calculates the Z-Score even if a cell is empty, resulting in a major spread (-5.85 z-score answers everywhere). I am kind of lost here, if someone has an idea of how to state it so it works I would be grateful! Thank you in advance,

Excelsior

**shg** · 06-14-2013, 02:27 PM

It's pretty hard to see what you're trying to do with that code fragment.

**ExcelsiorLux** · 06-14-2013, 02:35 PM

My bad.
.Range(.Cells(5, 11).End(Direction = x1down)) are all the values i need to test (with the for loop), but some cells are empty so i would like to skip them
.Cells(A,AA+2) refers to a cell in the range of values
.Cells(CC + 2, AA + 2) refers to the average of the values in range
.Cells(CC + 1, AA + 2 refers to the standard deviation of the values in the range

HTML Code:

I would like to check if the cell in the range is not empty, if so calculate the z-score. Thank very much for your help!

**shg** · 06-14-2013, 02:46 PM

1. The code doesn't compile.

2. This

Please Login or Register  to view this content.

... could be replaced with

Please Login or Register  to view this content.

... which passes the argument correctly, and corrects the name of the enumeration (xlDown, not x1Down), but makes no sense; the only empty cell it would ever land on would be in the last row of the sheet.

Post a workbook and explain clearly in context what you're trying to do.

**ExcelsiorLux** · 06-14-2013, 03:03 PM

Z_Score_Modeltry.xlsm
Ok so I wanted to calculate the Z-score on every data of SPX/TLT and SPX/TSX columns. So first I did the ratio, then i calculated the average and the st_dev of both column. The last thing i wanted to do is to do this : (ratio - average ratio ) / st_deviation on each cell of both column (which gives the z-scores).

Most of all I need to do it only with cells having values

**ExcelsiorLux** · 06-17-2013, 10:28 AM

Hi guys, just checking up if anybody comes up with an answer for me please ! I think it's mostly a syntax error but i've spent ages searching for it(or them). Thank you in advance.