VBA help: Nested If statement with IsNumeric and NumberFormat etc

[lizzym]

Hi,

I have a large spreadsheet that I receive often from a lab that I need to perform some statistical analysis on. I have been able to get what I need from nested if statements with regular cell functions, but I fear they may be confusing for my colleagues and am hoping for something a bit smoother (ie module code in VBA)

Below is an example of what I have:

[TABLE="width: 500"]
<tbody>[TR]
[TD]A/1[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]110[/TD]
[TD]100[/TD]
[TD]0.1[/TD]
[TD]-[/TD]
[TD]<500[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]General[/TD]
[TD]General[/TD]
[TD]General[/TD]
[TD]General[/TD]
[TD]< General[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]-[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD]-[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
All cells have a format of General, except for the cells with a <, which are custom "< General"
The first thing I need to do is return a value of 1 in row 4 if the cells (D2, E2, and F2) have a numerical value without the "<" sign. So in this example D2 would return a value of 1 (which would be put in D4). To do this, I have had to use a VBA function to return the number format of the cells in row 2 into row 3 (either General or "< General"). Then I have had to do the function:

=IF(CELL("type",D2)="v",IF(ISNUMBER(SEARCH("<",D3)),0,1),"-") in row 4

Then, if the value in row 2 has a <, and is greater than the value in column B or C, I want a 1 value in row 5:

=IF(CELL("type",D2)="v",IF(ISNUMBER(SEARCH("<",D3))*AND(OR((D2>$B2),(D2>$C2))),1,0),"-")

Then, if the value in row 2 doesn't have an <, and is greater than the value in column B or C, I want a 1 value in row 6:

=IF(CELL("type",D2)="v",IF(ISNUMBER(SEARCH("<",D3)),0,IF(OR(D2>$B2,D2>$C2),1,0)),"-")

This works fine but I feel there must be an easier way to do this through visual basic. I've tried coding it out but keep getting #VALUE in my spreadsheet. Also, I don't like how I have to create an additional row to write out "General" or "< General". I feel this takes away from a bit of the functionality of the spreadsheet. Is anyone able to point me in the right direction?

Thank you.

 

[iliace]

You can use UDFs to do the same thing, which will make the formulas easier to read. Personally, I prefer to shade calculated cells in a specific color, and provide a legend/instructions, along the lines of: color - Formula; do not modify.

I've tried coding it out but keep getting #VALUE in my spreadsheet.

It might help you post what you have already tried.

Since you already have the UDF for number format, you can recycle it in your new ones. I created one of my own for purposes of illustration. For example, this might work, except not using my ugly function names:

Public Function msGetNumberFormat(rng As Excel.Range) As String
  msGetNumberFormat = rng.NumberFormat
End Function


Public Function mvCalcRow4(rng As Excel.Range) As Variant
  Dim sFormat As String
  
  If Not IsNumeric(rng.Value) Then
    mvCalcRow4 = "-"
    Exit Function
  End If
  
  sFormat = msGetNumberFormat(rng)
  
  If InStr(1, sFormat, "<") > 0 Then
    mvCalcRow4 = 0
  Else
    mvCalcRow4 = 1
  End If
End Function

Sheet1

	B	C	D	E	F
B	C	D	E	F
-
General	General	General	General	<general< td=""></general<>
		-

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]0.1[/TD]

[TD="align: right"]<500[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]0[/TD]

</tbody>

Formeln der Tabelle

Zelle Formel D3 =msgetNumberFormat(D2) D4 =mvCalcRow4(D2) <tbody> </tbody>

<tbody>
</tbody>

Note that the calculation is no longer dependent on row 3, in other words you can get away with this:

	B	C	D	E	F
-
		-

<tbody>
[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]0.1[/TD]

[TD="align: right"]<500[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]0[/TD]

</tbody>

Formeln der Tabelle

Zelle Formel D3 =mvCalcRow4(D2) <tbody> </tbody>

<tbody>
</tbody>