I have a sheet with a list of employees that has percentages listed in 4
columns out from their name. How can I get an error message to pop up for
any row that doesn't equal 100% when you add up the 4 columns in each row?
Therefore, I want the macro to loop around so that it goes down the sheet
summing the 4 columns for each employee and an error message will pop when it
hits a row that doesn't equal 100%.

Something like this should do the job:

Sub test()

Dim i As Long
Dim LR As Long
Dim arr

LR = Cells(65536, 1).End(xlUp).Row

arr = Range(Cells(1), Cells(LR, 4))

For i = 1 To UBound(arr)
If arr(i, 1) + arr(i, 2) + arr(i, 3) + arr(i, 4) <> 1 Then
Range(Cells(i, 1), Cells(i, 4)).Select
MsgBox "row " & i & " (the selected range) does not add up to 100%!",
_
vbCritical, "checking row sums"
End If
Next i

End Sub


RBS


"Can't do Macros" <Can't do (E-Mail Removed)> wrote in
message news:12A93D3A-85EB-4A09-9E6C-(E-Mail Removed)...
>I have a sheet with a list of employees that has percentages listed in 4
> columns out from their name. How can I get an error message to pop up for
> any row that doesn't equal 100% when you add up the 4 columns in each row?
> Therefore, I want the macro to loop around so that it goes down the sheet
> summing the 4 columns for each employee and an error message will pop when
> it
> hits a row that doesn't equal 100%.

Thanks for your help. I entered the code you gave me, but I get an error
message for the line that says vbCritical, "checking row sums"
The error message I get is: Compile error: Invalid use of property.

Do you have any tips for getting rid of this error message?

Thanks again!

"RB Smissaert" wrote:

> Something like this should do the job:
>
> Sub test()
>
> Dim i As Long
> Dim LR As Long
> Dim arr
>
> LR = Cells(65536, 1).End(xlUp).Row
>
> arr = Range(Cells(1), Cells(LR, 4))
>
> For i = 1 To UBound(arr)
> If arr(i, 1) + arr(i, 2) + arr(i, 3) + arr(i, 4) <> 1 Then
> Range(Cells(i, 1), Cells(i, 4)).Select
> MsgBox "row " & i & " (the selected range) does not add up to 100%!",
> _
> vbCritical, "checking row sums"
> End If
> Next i
>
> End Sub
>
>
> RBS
>
>
> "Can't do Macros" <Can't do (E-Mail Removed)> wrote in
> message news:12A93D3A-85EB-4A09-9E6C-(E-Mail Removed)...
> >I have a sheet with a list of employees that has percentages listed in 4
> > columns out from their name. How can I get an error message to pop up for
> > any row that doesn't equal 100% when you add up the 4 columns in each row?
> > Therefore, I want the macro to loop around so that it goes down the sheet
> > summing the 4 columns for each employee and an error message will pop when
> > it
> > hits a row that doesn't equal 100%.
>
>

Probably the linebreaks messing things up.
Put your cursor after:
100%!",
and press the Dell key a few times till you got vbCritical right after the
above.
Then try again.

RBS


"Can''t do Macros" <(E-Mail Removed)> wrote in message
news:C5A39344-1DFA-494A-BAD9-(E-Mail Removed)...
> Thanks for your help. I entered the code you gave me, but I get an error
> message for the line that says vbCritical, "checking row sums"
> The error message I get is: Compile error: Invalid use of property.
>
> Do you have any tips for getting rid of this error message?
>
> Thanks again!
>
> "RB Smissaert" wrote:
>
>> Something like this should do the job:
>>
>> Sub test()
>>
>> Dim i As Long
>> Dim LR As Long
>> Dim arr
>>
>> LR = Cells(65536, 1).End(xlUp).Row
>>
>> arr = Range(Cells(1), Cells(LR, 4))
>>
>> For i = 1 To UBound(arr)
>> If arr(i, 1) + arr(i, 2) + arr(i, 3) + arr(i, 4) <> 1 Then
>> Range(Cells(i, 1), Cells(i, 4)).Select
>> MsgBox "row " & i & " (the selected range) does not add up to
>> 100%!",
>> _
>> vbCritical, "checking row sums"
>> End If
>> Next i
>>
>> End Sub
>>
>>
>> RBS
>>
>>
>> "Can't do Macros" <Can't do (E-Mail Removed)> wrote in
>> message news:12A93D3A-85EB-4A09-9E6C-(E-Mail Removed)...
>> >I have a sheet with a list of employees that has percentages listed in 4
>> > columns out from their name. How can I get an error message to pop up
>> > for
>> > any row that doesn't equal 100% when you add up the 4 columns in each
>> > row?
>> > Therefore, I want the macro to loop around so that it goes down the
>> > sheet
>> > summing the 4 columns for each employee and an error message will pop
>> > when
>> > it
>> > hits a row that doesn't equal 100%.
>>
>>