Tier fee schedule macro

[jarvisaurus]

I have a spreadsheet (excel 2003) that requires a formula for a tier fee schedule(e.g., first occurence 100%, second 50% and third 25%). The spreadsheet is setup as follows:

Column A - Name

Column B - Code

so you'll have blocks of data:

MVP 300
MVP 300
MVP 300
VPM 300
VPM 300
VPM 300

Here's what I have so far:

Dim i As Integer
Dim LR As Long
Application.ScreenUpdating = False
LR = Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To LR Step 1


Select Case cells(i, "B").Value
    
    Case 300
    
        cells(i, "AD").Value = 500*100%

End Select

Next i
Application.ScreenUpdating = True

End Sub

How do I get it so after the first 300 it's 50% for the second 300, 25% for the third 300, and 25% anything there after.

 

[jasonb75]

Maybe

Dim i As Integer, LR As Long, Counter As Long
    Application.ScreenUpdating = False
        LR = Range("A" & Rows.Count).End(xlUp).Row
        Counter = 1
For i = 2 To LR Step 1
    If Cells(i, "B").Value = 300 Then
        Select Case Counter
            Case 1
                Cells(i, "AD").Value = 500 * 1
            Case 2
                Cells(i, "AD").Value = 250 * 0.5
            Case Else
                Cells(i, "AD").Value = 500 * 0.25
        End Select
    End If
Next i
    Application.ScreenUpdating = True
End Sub

 

[jarvisaurus]

No it resulted in same, I'm not sure but doesn't the counter have to count the # of occurences per i?

 

[jasonb75]

Oops, I forgot to add the increment line

Dim i As Integer, LR As Long, Counter As Long
    Application.ScreenUpdating = False
        LR = Range("A" & Rows.Count).End(xlUp).Row
        Counter = 1
For i = 2 To LR Step 1
    If Cells(i, "B").Value = 300 Then
        Select Case Counter
            Case 1
                Cells(i, "AD").Value = 500 * 1
            Case 2
                Cells(i, "AD").Value = 500 * 0.5
            Case Else
                Cells(i, "AD").Value = 500 * 0.25
        End Select
            Counter = Counter + 1
    End If
Next i
    Application.ScreenUpdating = True
End Sub

 

[jarvisaurus]

Works fine except I need the case to start over every time the value in Column A changes.

Right now it's taking everything after the Case 2 as

MVP 500
MVP 250
MVP 125
VPM 125
VPM 125
VPM 125

I would like Select Case to restart on the next value(VPM).


MVP 500
MVP 250
MVP 125
VPM 500
VPM 250
VPM 125

 

[jasonb75]

Try

Dim i As Integer, LR As Long, Counter As Long, cTest As String
    Application.ScreenUpdating = False
        LR = Range("A" & Rows.Count).End(xlUp).Row
        Counter = 1
        cTest = Cells(i, "A").Value
For i = 2 To LR Step 1
    If Cells(i, "A").Value <> cTest Then
        Counter = 1
        cTest = Cells(i, "A").Value
    End If
    If Cells(i, "B").Value = 300 Then
        Select Case Counter
            Case 1
                Cells(i, "AD").Value = 500 * 1
            Case 2
                Cells(i, "AD").Value = 500 * 0.5
            Case Else
                Cells(i, "AD").Value = 500 * 0.25
        End Select
            Counter = Counter + 1
    End If
Next i
    Application.ScreenUpdating = True

 

[jarvisaurus]

Run-time error "1004"

Application-defined or object-defined error.

on the following line:

cTest = cells(i, "A").Value

I removed the above and replaced the ctest with cells(i, "A").Value

    Counter = 1
For i = 2 To LR Step 1
    If cells(i, "C").Value <> cells(i, "C").Value Then
        Counter = 1
    End If

But the above resulted in the same. Is it possible to do a loop? I'm not sure how to go about that though.

Thanks for all your help by the way. Gladly appreciated.

 

[jasonb75]

Oops, that was me having a moment of stupidity (it happens often ) the first defenitions of Counter and cTest should have been deleted.

Dim i As Integer, LR As Long, Counter As Long, cTest As String
    Application.ScreenUpdating = False
        LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR Step 1
    If Cells(i, "A").Value <> cTest Then
        Counter = 1
        cTest = Cells(i, "A").Value
    End If
    If Cells(i, "B").Value = 300 Then
        Select Case Counter
            Case 1
                Cells(i, "AD").Value = 500 * 1
            Case 2
                Cells(i, "AD").Value = 500 * 0.5
            Case Else
                Cells(i, "AD").Value = 500 * 0.25
        End Select
            Counter = Counter + 1
    End If
Next i
    Application.ScreenUpdating = True

Where I've specified (i, "A") this should refer to the column with the names (MVP, PVM, etc)

 

[jarvisaurus]

PERFECTO!

can't thank you enough!

 

[jarvisaurus]

Hello,

I came upon a situation after running the code where I have blocks of data e.g.:


MVP 300 500
MVP 300 250
MVP 200 100
MVP 200 100
VPM 200 100
VPM 400 100
VPM 600 100
VPM 600 100

I want to run the same code above fill in all cases(200,400,600) except if any block of cells (MVP) contains case 300, I want to skip to the next block (VPM). Using the example above, each block is MVP or VPM where the formula for cases 200-600 = 100.

The ultimate result would look like:

MVP 300 500
MVP 300 250
MVP 200
MVP 400
VPM 200 100
VPM 400 100
VPM 600 100
VPM 600 100

I added the following before next i:

[B]If Cells(i, "B").Value <> 300 Then

Cells(i, "AD").Value = 100[/B]

But the above fills in all cells. I think I have to define each group of cells and then use the if statement but I'm not sure how to go about that.