Results 1 to 14 of 14

I am using scripting dictionary method -If no data - need a message box -

Threaded View

  1. #1
    Forum Contributor
    Join Date
    09-06-2011
    Location
    northern ireland
    MS-Off Ver
    Excel 365
    Posts
    180

    I am using scripting dictionary method -If no data - need a message box -

    
    Dim x, y(), i&, j&, k&, l&, s$
    x = Range("C1:I" & Cells(Rows.Count, 3).End(xlUp).Row).Value
    ReDim y(1 To UBound(x), 1 To 5)
    
    With CreateObject("Scripting.Dictionary")
        .CompareMode = 1
        For i = 1 To UBound(x)
                    If x(i, 4) = "Overtime" Or x(i, 4) = "Overtime On Saturday" Or x(i, 4) = "Overtime On Sunday" Or x(i, 4) = "Saturday Premium" Or x(i, 4) = "Sunday Premium" Or x(i, 4) = "Travel" Or x(i, 4) = "Travel Saturday" Or x(i, 4) = "Travel Sunday/Holiday" Then
                s = x(i, 2) & x(i, 4)    'BEMSID&HOURS_DESCR is the unique key
                'if key already exists in the dictionary, we sum AMOUNT
                If .exists(s) Then
                    k = .Item(s): y(k, 5) = y(k, 5) + x(i, 7)    'y(k, 5) is 'AMOUNT'
                Else 'if key not exists
                    j = j + 1: .Item(s) = j 'add key in the dictionary,
                    ' and fill the output array
                    y(j, 1) = x(i, 2): y(j, 2) = x(i, 1) 'column BEMSID
                    y(j, 2) = Split(x(i, 1), ",")(0)  'column EMPLOYEE, surname only
                    y(j, 3) = x(i, 4): y(j, 4) = x(i, 7) 'column HOURS_DESCR
                    y(j, 4) = "" 'column Value, 
                    y(j, 5) = x(i, 7)    'column AMOUNT (Units)
                End If
            End If
        Next i
    End With
    If j = 0 Then Exit Sub
    So this is my code as above, but what it there is nothing in the data when I open the file ? It doesn't do anything apart from open the data file to extract the info from so how can I put something in here to say if no results obtained then have a message box appear to say "no data to import this month" and to exit the sub....

    Many thanks in advance
    Leanne
    Last edited by leanne2011; 02-03-2012 at 04:48 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1