Sum

[Francis Hookam]

It's probably obvious and I've just missed it!

Visual basic

Using variables, in this case RowNumber and ColNumber, I should like to
check whether or not the sum of the values in the following = 45

Range(Cells(RowNumber, ColNumber), Cells(RowNumber, ColNumber + 8))

Ditto

Range(Cells(RowNumber, ColNumber), Cells(RowNumber + 2, ColNumber + 2))

I am not sure what is the Visual Basic equivalent of the worksheet

=if(sum(R2C2:R2C10)=45,"Yes","No")

and

=if(sum(R2C2:R4C4)=45,"Yes","No")

(in the macro "Yes" and "No" will be some action, as yet not determined)

Please show me how

Francis Hookham

 

[JE McGimpsey]

It's probably obvious and I've just missed it!

Visual basic

Using variables, in this case RowNumber and ColNumber, I should like to
check whether or not the sum of the values in the following = 45

Range(Cells(RowNumber, ColNumber), Cells(RowNumber, ColNumber + 8))

Ditto

Range(Cells(RowNumber, ColNumber), Cells(RowNumber + 2, ColNumber + 2))

I am not sure what is the Visual Basic equivalent of the worksheet

=if(sum(R2C2:R2C10)=45,"Yes","No")

and

=if(sum(R2C2:R4C4)=45,"Yes","No")

(in the macro "Yes" and "No" will be some action, as yet not determined)

Please show me how

A few ways:

Dim sYesNo As String
sYesNo = IIf(Application.Sum(Range(Cells(RowNumber, ColNumber), _
Cells(RowNumber, ColNumber + 8))) = 45, "Yes", "No")
MsgBox sYesNo

or

Dim bYesNo As Boolean
bYesNo = Application.Sum(Cells(RowNumber, ColNumber). _
Resize(2, 2)) = 45
If bYes Then
'Do Yes Stuff
Else
'Do No Stuff
End If

or

Dim dSum As Double
Dim rCell As Range
For Each rCell In Cells(RowNumber, ColNumber).Resize(1, 8)
dSum = dSum + rCell.Value
Next rCell
If dSum = 45 Then
'Do yes stuff
Else
'Do no Stuff
End If

or

Dim vArr As Variant
Dim dSum As Double
Dim i As Long
vArr = Cells(RowNumber, ColNumber).Resize(2, 2).Value
For i = LBound(vArr, 1) To UBound(vArr, 1)
dSum = dSum + vArr(i, 1)
Next i
If dSum = 45 Then
'Do yes stuff
Else
'Do no Stuff
End If

 

[Francis Hookam]

Wow! Where did 'IIf' come from? Just what I wanted

Any guidelines for when to use

IIf(xxxxxxx,xxx,xxx)

as against

IF xxxxxxxxxxxx THEN
xxxxxxxxxx
END IF

The former comes naturally for me from IF formulae in the spreadsheets

Dim sYesNo As String
sYesNo = IIf(Application.Sum(Range(Cells(RowNumber, ColNumber), _
Cells(RowNumber, ColNumber + 8))) = 45, "Yes", "No")
MsgBox sYesNo

=======================
Thanks for
Variable in footer
and
Footer date format
Not tried yet but shall do over w/e

Francis Hookham

 

[JE McGimpsey]

Any guidelines for when to use

IIf(xxxxxxx,xxx,xxx)

as against

IF xxxxxxxxxxxx THEN
xxxxxxxxxx
END IF

Iif() has a significant disadvantage over

If...Then
...
Else
...
End If

since both terms in the Iif() statement are evaluated on execution, so
if there's an error in a non-executed branch (for instance:

a = Iif(b>0, c, d/e)

will error if e=0, even if b>0).

Note also that your examples aren't comparable. Iif() requires both the
true and false branches to have arguments, whereas

If xxx Then
yyyy
End If

doesn't.

Finally, Iif() can only have relatively simple arguments.
If...Else...End If can have hundreds of lines of code executed in each
branch.