How can I perform a conditional test based on the sum a row of cells using VBA

[mick86]

Hi

There is no doubt an exceedingly simple answer to this question however using VBA script to perform basic mathematical calculations is not something I am familiar with.

In the past I have used VBA script to change column with size based on cell values as per follows:

Please Login or Register  to view this content.

Now I wish to change row size in a similar fashion only the criteria will be a maths function rather than a string in a single cell. The maths function will simply be a sum of cells in a row. I will need to test whether or not the row ads up to a number greater than zero.

To illustrate what I am trying to achieve I have (poorly) written out a rough attempt at the code below:

Please Login or Register  to view this content.

I am unsure how the syntax works for this, I considered defining the range as a string first so I could test the value of a single variable (by defined name) rather than directly referencing the row of cells in the formula. As this code needs to be repeated many time for many rows the simplest solution would be b

An assistance greatly appreciated.

Michael.

[Izandol]

It is very close:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

[mick86]

Much appreciate the reply although unfortunately I cant seem to get either of these to work

Application code seems to cause:

Run-time error '13':

Type mismatch


Worksheetfunction code seems to cause:

Run-time error '438':
Object doesn't support this property or method

Maybe I should add that the code is within an option Explicit Worksheet Activate format as follows:

Please Login or Register  to view this content.

[xladept]

Maybe:

Option Explicit

Private Sub Worksheet_Activate()

If WorksheetFunction.Sum(Range("C6:Q6")) < 0 Then 'Week 1
Rows("6").RowHeight = 0
Else
Rows("6").RowHeight = 20
End If
End Sub

[Izandol]

I am sorry - I omitted the Range("c6:q6")
You omitted the .Sum

[Izandol]

Also you may use

Please Login or Register  to view this content.

[mick86]

Maybe:

Option Explicit

Private Sub Worksheet_Activate()

If WorksheetFunction.Sum(Range("C6:Q6")) < 0 Then 'Week 1
Rows("6").RowHeight = 0
Else
Rows("6").RowHeight = 20
End If
End Sub

Thank you, this does indeed work

I should add that for the exact purpose I wanted it for I changed the code to <=0, I should have typed it this way in my first example.

Please Login or Register  to view this content.

[xladept]

You're welcome! And, thanks for the rep