Results 1 to 1 of 1

A complicated Loop with lots of IF's and a method of counting similar dates...

Threaded View

  1. #1
    Registered User
    Join Date
    03-01-2013
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    1

    Question A complicated Loop with lots of IF's and a method of counting similar dates...

    There's a tl;dr at the bottom that might be easier to read than my paragraphs up here. Just a warning


    I've got a list of rows with the following information:

    Drafts ordered - Drafts Recieved - Forwarded the Draft

    If I've only got a date under draft ordered, and not "Forwarded the Draft", then I'd like excel to make a task in outlook.
    If I've got a date under all 3, I'd like it to use the date from "Forwarded the draft".
    I'd also like it to keep a count on the number of "drafts" that i'm still waiting to receive, and those I've forwarded. If I've forwarded 4 drafts, I'd like excel to only make 1 task for all 4 done the same day. There will be cases where it takes longer to get a draft copy, so in the same spread sheet I'll have different forwarding dates
    .
    If I can get excel to make a seperate outlook task item for nonsimilar dates. That would be awesome.

    The whole thing has been giving me a headache, and currently, I have a Do loop and a counter that goes row by row. I've made it so if cell.offset(counter+1, 0)=cell.offset(counter,0) then counter = counter + 1.

    This avoids having more than 1 task item in Outlook. Problem is, if there's more than 1 row difference between "Drafts Forwarded", it doesn't recognize any other items.
    This is a section of my code:

    Do Until cell.Offset(counter, 5) = ""
    If cell.Offset(counter, 0) = "" And cell.Offset(counter, 10) = "" Then
    counter = counter + 1
    ElseIf cell.Offset(counter - 1, 10) = cell.Offset(counter, 10) Or     cell.Offset(counter - 1, 0) = cell.Offset(counter, 0) Then
    counter = counter + 1
    Else
    If OLApp Is Nothing Then Set OLApp = CreateObject("Outlook.Application")
    On Error GoTo 0
    If Not OLApp Is Nothing Then
    Set OLNS = OLApp.GetNamespace("MAPI")
    OLNS.Logon
     Set OLTask = OLApp.CreateItem(olTaskItem)
    <use outlook code here>
    If Not IsEmpty(cell.Offset(counter, 0)) Then
        With OLTask
    <create task>
    End With
    Else
    With OLTask
    <create task>
    End If
    End If
    
      counter = counter + 1
    
    
    End If
    Loop
    This code only creates tasks properly for one date. If I alter or add a new Draft Forwarded date, it does not create a task for that item.

    This basically is how excel looks like:

    DC Order Date (1st row) DC Receive Date (3rd row) DC Forward Date (10th row)
    date (may or may not have) date (may or may not have) date (may or may not have)
    date (may or may not have) date (may or may not have) date (may or may not have)
    ... ... ...
    date (may or may not have) date (may or may not have) date (may or may not have)


    ....
    Can anyone help me out?

    tl;dr: For all rows in columns

    • If cell (n, 10) isn't populated, use cell (n, 1)'s date to make an outlook item.
    • If cell (n, 10) is populated, use cell (n, 10)'s date.
    • if cell (n, 10) or cell (n, 1) date = cell (n, 10) or cell (n, 1) add to count
    • Try to count number of cell (n, 10)'s or cell (n, 1)'s with similar dates, create 1 task item for all similar dates.
    • If Cell (n, 1) or Cell (n, 10) have unique dates compared to the rest, create separate task item for each unique item.
    • if Cell (n, 3) has a value and Cell (n, 10) does not, do not make a task and do not count cell (n, 1) value.
    Last edited by hellsadvocate; 05-13-2013 at 01:15 PM. Reason: Clarification

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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