Cut dates to within user range, VBA

[katk]

Hi all,

I have a macro that converts a list of sale prices/dates from our database into a formatted price book. The way the database works, dates come out like this:

Item 1 -- 6/1/11 -- 7/31/11 -- $50
Item 1 -- 9/2/11 -- 12/1/11 -- $50
Item 2

etc.

The macro gets it to the point where every month with the sale prices (the 50s, in this case) is condensed and listed in a single cell like this:

Jun-Jul, Sep-Nov

The only problem I have right now is that our database won't run partial ranges for sale dates. So if Item 1 is on sale from June through July and September through November but I only want to show prices from July through September, I'll get prices outside the range I want. (This is only a problem because our contracts get renewed at different times, so some products might have accurate pricing through November before others. Having November on the sheet at all would be confusing if we only had fully correct pricing through September.)

So I get this: Jun-Jul, Sep-Nov
But I want this (if I'm looking for Jul-Sept pricing): Jul, Sep
(or it could end up being "Jul, Aug" or "Aug-Sep" or something, depending on the product)

I was thinking of putting in an input box of some sort for the dates, but I don't know how to code for excel to sort of "truncate" the date ranges into the intervals I want. I could probably also do this with a formula somehow if the desired range was entered into cells somewhere. I'm just not sure where I'd do it. The dates go through these stages:

8/1/11 -- 10/2/11 (2 cells) to "Aug-Sep" to "Aug-Sep, Oct, Nov-Dec,"
(however many sale ranges there are for one item)
Then the comma at the end gets taken out. To make it more confusing, sometimes the items are on special sales, which get marked like this:

Aug-Sep (DP), Oct, Nov-Dec (DP)

If anyone can lead me in the right direction as far as finding a way to get the dates to reorient themselves to user-inputted intervals, that would be great. Thanks so much,

Katherine

 

[katk]

Halfway there -- just need a little help!

I decided to tackle the problem at the very end when it's all formatted. I have two input boxes currently that pop up for the first and last months of the range (could consolidate to one form). Then I put in code to detect the range months and cut/add where necessary. The only problem I'm having (I think) is figuring out how to use Mid to take out middle characters when I have "(DP)" at the end. (Hard to find on the web for variable lengths.) If anyone could just help me fix the code below, that would be great. Thank you!

Here is what I have in terms of code:

Sub TruncateMonths()
Dim rng As Range
Dim Lgth1 As Integer
'2 input boxes. Consider one userform.
Month1 = InputBox("Start Month", "Enter Start Month")
Month2 = InputBox("End Month", "Enter End Month")
For Each rng In Range("E5:E10")
'If contains Month1
    If InStr(1, rng, Month1, 1) Then
        'If first 3 characters = Month1, nothing
        If Left$(rng, 3) = Month1 Then
        'If not, take out the first 4 characters
        Else: rng = Right(rng, Len(rng) - 4)
        End If
    Else
        'If doesn't contain month1, rng = month1 & original - first 3 characters
        rng = Month1 & Right(rng, Len(rng) - 3)
    End If
    'If contains Month2
    If InStr(1, rng, Month2, 1) Then
        'If contains month2 and ends with (DP), lgth1 = starting at 1st letter of last month before DP
        If Right$(rng, 4) = "(DP)" Then
            Lgth1 = Len(rng) - 6
            'If that last month is month 2, nothing
            If Mid$(rng, Lgth1, 3) = Month2 Then
            'otherwise, take out that last month and its dash (eg -Nov)
            Else: rng = Mid(rng, Lgth1 - 1, 4)
            End If
        Else
            'If contains Month2 and doesn't end with DP, and month2 is the last month, nothing
            If Right$(rng, 3) = Month2 Then
            'otherwise, take out the last 4 characters
            Else: rng = Left(rng, Len(rng) - 4)
            End If
        End If
    Else
        'If doesn't contain month2 and ends with (DP), lgth1 = as before, rng = take out last three char, add Month2
        If Right$(rng, 4) = "(DP)" Then
        Lgth1 = Len(rng) - 7
        rng = Mid(rng, Lgth1, 3) & Month2
        'If doesn't contain month2 or end with (DP), take out last 3 characters, replace with Month2
        Else: rng = Left(rng, Len(rng) - 3) & Month2
        End If
    End If
    Next rng
End Sub

Thanks again for any help!