Check the value of a range of cells

[PWD]

I'm sure the answer must be SIMPLE - like me! I have a variable i which equals the particular row number. My macro puts a formula in a cell provided the sum of cells AK(i) to AU(i) isn't 0. I've tried the following combinations,

             If Not (sum(cells("AK" & i):("AU" & i))) = 0 Then 'Only do when Cumulative figures present
            If Not (sum(Range("AK" & i):("AU" & i))) = 0 Then 
If Sum (cells(RiC31:RiC42)) <> 0 Then

I apologise if I've overlooked an obvious syntax error. It really doesn't seem to like the ':' "Compile error: Expected: list separator or )"

Many thanks, Des.

 

[mintjulep]

If Not (sum(cells("AK" & i:"AU" & i)) = 0 [red])[/red]Then 'Only do when Cumulative figures present
If Not (sum(Range("AK" & i:"AU" & i)) = 0 [red])[/red] Then
If Sum (cells(RiC31:RiC42)) <> 0 Then

I think.

 

[PWD]

Thanks, worth a try. Hoped it was that obvious but it still gived the same error. Dang.

 

[PWD]

This may be a bit of use to me, at least it doesn't fall over:-

            If Not (Range("AK" & i)) + (Range("AU" & i)) = 0 Then

Des.

 

[combo]

There is no 'sum' function in vba. To use the one from excel:
Application.WorksheetFunction.Sum(Range("AK" & i:"AU" & i))
(Active sheet cells without worksheet reference.)

combo

 

[newguy86]

Without the full code I am kind of shooting in the dark here but I would probably set each cell range to a defined value so that you may reference that range in your code.

<CODE>
Dim xlCellRange(1 to 2) to "String" (Or any definition that refers to the cells properties)

xlCellRange(1) = Range("AK":"AU").Value
xlCellRange(2) = Range("RiC31:RiC42").Value
If xlCellValue(1) <> 0 Then
If xlCellValue(2) <> 0 Then
</CODE>


Travis

http://www.enterprise.com

 

[PWD]

Hi Travis. Had a looong Christmas break. I put together this (clumsy) code

 R = ws.UsedRange.Rows.Count

        For i = 10 To R 'Start at 10 end at last used row

        Range("N" & i).Select 'Put this formula in Column M

 'Need to accomodate text put in the following range!!
 
 
            If Not (Range("Y" & i)) + (Range("Z" & i)) _
             + (Range("AA" & i)) + (Range("AB" & i)) _
             + (Range("AC" & i)) + (Range("AD" & i)) + (Range("AE" & i)) _
             + (Range("AF" & i)) + (Range("AG" & i)) + (Range("AH" & i)) _
             + (Range("AI" & i)) + (Range("AJ" & i)) = 0 _
             And (IsNumeric(Range("Y" & i))) Then 'Only do when depreciation figures present
            
          
            
            
            Application.StatusBar = "Updating Sheet " & ThisSheet _
            & " Row " & i 'Shows what Sheet & row are being amended
            ActiveCell.FormulaR1C1 = "=MonthlyCharge()" 'Puts in the Function
            End If

        Next I

This got me around the summing the values of these cells; then I found they’d put some notes in these cells of unused rows. Aaaarghh!

I've just come back into this cold so I guess I'll play around a bit more & see where I go.

Des.