Need help editing my VBA code to retreive values not equal to zero

[mahoney403]

Hi All,

I have a section in my macro that returns the values to a summary page whenever there is a value <>0 within a row. I was wondering, is there a way to exclude the cells that have a formula in them?(also, if there is, would =35 be considered a formula, in case the value was entered that way vs just "35").

Here is the piece of my code:

If ws.Name <> "Summary" Then
For c = 3 To 33 Step 1
If ws.Cells(39, c).Value <> 0 Then
i = i + 1
s(i, 1) = ws.Cells(4, c)
s(i, 2) = ws.Name
s(i, 3) = ws.Cells(39, c)
End If
Next c
End If

The columns I want to skip are the ones with subtotals, which is currently adding duplicates to my summary page. Also, the subtotals are throughout the row, not just the last column.

Any help is greatly appreciated,

Thanks

 

[James006]

Hi,

You can use a UDF such as

[COLOR=#333333]Function IsFormula(rng As Range)[/COLOR]
[COLOR=#333333]    If rng.HasFormula Then IsFormula = True[/COLOR]
[COLOR=#333333]End Function[/COLOR]

HTH

 

[mahoney403]

Thanks!