MSG BROUPED BY GROUP

[AND]

HELLO,
In SHEET3 I have this table beginning from B column:
DES_CC COD NAME TIME1 TIME2 TIME3 DELTA
Exp A 10025 PIP 12,00 12,10 12,00 0
Exp A 10026 LIL 12,00 13,74 14,00 2
Exp B 10027 TOP 10,00 9,03 9,00 -1
Exp B 10028 PAPERIN 15,00 7,49 7,00 -8
Exp B 10029 GEP 10,00 9,36 9,00 -1
Exp B 10030 Z 5,00 4,71 5,00 0
Exp B 10031 MIN 10,00 10,10 10,00 0
Exp C 10032 PLUT 5,00 4,50 5,00 0
Exp C 10033 ZOR 5,00 4,51 5,00 0
Exp C 10034 TOR 4,00 2,71 3,00 -1
Exp C 10035 ORES 5,00 4,51 5,00 0

I've developed this script to get a msgbox for each DELTA different from 0.
Instead I need of a msgboxthat list all the DELTAs in a unique "BOX" for
each different "DES_CC". Here my script, different from that I want:

Private Sub CommandButton1_Click()
Dim i, y, lavorato As Long
Dim ws As Worksheet
Set ws = Worksheets("Foglio3")
With ws
For i = 2 To 61
For y = 5 To 8
If ws.Cells(i, y).Value < ws.Cells(i, y + 1).Value Then
lavorato = ws.Cells(i, y + 2).Value - ws.Cells(i, y).Value
MsgBox (ws.Cells(i, 4).Value & " di " & ws.Cells(i, 2).Value & " Ha fatto
straordinari/ferie di" _
& lavorato & " ore in più di quanto pianificato nella week " _
& ws.Cells(1, y))
End If
Next
Next
End With
Set ws = Nothing
End Sub

Here the result I'm looking for:
for example in the first msgbox I wanna see in:

"the following employes of EXP A office got these discrepancies:"
LIL 12,00 13,74 14,00 2

in the second msgbox:
"the following employes of EXP B office got these discrepancies:"
TOP -1
PAPERIN -8
GEP -1
Z 0
MIN 0

Please help me...




Can you help me please?

 

[PA]

Hi,
Basicly, you need to hold the text you want to display in a variable and
display one msgbox at the end. Exemple

Sub DemoMsgbox()

Dim sText As String
Dim i As Integer

sText = "Some data in a table:" & vbNewLine

For i = 1 To 10

sText = sText & 2 * i & vbTab & 3 * i & vbTab & 4 * i & vbNewLine

Next

MsgBox sText

End Sub

try that technique with your problem and give me soem feedback

PA

 

[AND]

unfortunately, I can't make a VBA scrip like this; i'm not so cute
Can u give me some infos else or a formula based about my example please?

 

[PA]

I'm not sure that I understand what you want. Do you want (based on your
sample sheet) to have 3 different msgbox popping one after the other, on for
each DES_CC. Each msgbox would contain the name of the DES_CC and all the
DELTAs.

or

A formula that would return all the deltas for one DES_CC on the same row?

And finaly, if you can't make a script because you don't know how, give me
the exact output you want and i'll write you one (if it's not a 3 days long
project)

PA

 

[AND]

Thanks or ur help PA.
As u've written, i'm looking for 3 different msgbox popping one after the
other, on for each DES_CC. Each msgbox would contain the name of the DES_CC
and all the
DELTAs.

here the output I want:
the first msgbox:

"the following employes of EXP A office got these discrepancies:"
LIL 2

the second msgbox:

"the following employes of EXP B office got these discrepancies:"
TOP -1
PAPERIN -8
GEP -1

the third:

"the following employes of EXP c office got these discrepancies:"
TOr -1

Thank you for your precious help.

 

[PA]

It's a pleasure to help! I'll try to send the code today but i'm not sure
i'll have the time today. What you want is much clearer for me now.

get back to you soon

PA

 

[PA]

This should work. Tell me if it works or not.

''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Description: This procedure display one message box for
' each office containing the names and deltas
' different from 0.
' This procedure sorts the data to group all
' the entries from the same office together.
' To work, it needs the following defined names:
' DataRange =Sheet1!$A$1:$G$12 all data including headers
' DELTA =Sheet1!$G$2:$G$12 all Delta entries excluding
headers
' DES_CC =Sheet1!$A$2:$A$12 all DES_CC entries excluding
headers
' NAME =Sheet1!$C$2:$C$12 all Name entries excluding
headers
'Date: March 5, 2009
'Author: PA
Sub DisplayDelta()

Dim rngCell As Range
Dim rngDES As Range
Dim rngDELTA As Range
Dim rngNAME As Range

Dim sMessage As String

On Error GoTo ErrorHandler

Set rngDES = Range("DES_CC")
Set rngDELTA = Range("DELTA")
Set rngNAME = Range("NAME")

'Sort the data to have all the same DES_CC group together
Range("DataRange").Sort _
key1:=rngDES, _
order1:=xlAscending, _
header:=xlNo

'Loop trough all entries to find the Deltas different from 0.
'Whenever the DES_CC changes, a message box display the names
'and deltas
For Each rngCell In rngDES

'Check for Deltas different from 0 and build the message
'if neccessary.
If Intersect(rngDELTA, rngCell.EntireRow).Value <> 0 Then
sMessage = sMessage & Intersect(rngNAME,
rngCell.EntireRow).Value & vbTab & _
Intersect(rngDELTA, rngCell.EntireRow).Value & vbNewLine
End If

'If this is the last DES_CC of the group, display the message.
If rngCell.Value <> rngCell.Offset(1, 0) Then
MsgBox "The following employes of " & rngCell.Value & _
" office got these discrepancies:" & vbNewLine & sMessage

sMessage = ""

End If

Next

ErrorExit:
Exit Sub

ErrorHandler:
MsgBox "Sorry, an error occured." & vbNewLine _
& Err.Description

Resume ErrorExit

End Sub


PA

 

[AND]

PA u're really lovely!
But it doesn't work...
where I'm wrong?

here the link to the file. in VBA(alt+f11) i pasted ur script but...nada.

http://it.swoopshare.com/file/aab754ed12f2e6b2718c655aa7c2cec0/demorange?tags=demorange&lang=it

just a bit of patience more please
thanks!!

 

[PA]

The problem is that you didn't defined the names required. If you're not
familiar with defined names, they are very, but very, useful and worth
learning.

Check this out
http://www.cpearson.com/excel/named.htm

For my script to work, you need to add the names described before the script:

PA

 

[AND]

thanks for your hel PA.
I've lernt so much.
I wanna thank you a lot, becouse you solved my issue.
i'm going still to complete the script, becouse i'm not so able to do it by
defined names, but i'm sure to solve it.
thanks again.
bye.