Looping through data

[osscie3]

Hi all. I'll preface this with that my code works but is very sensitive so I'm calling on some of you Excel gods to help me out.

I've written some code that will loop through the range I've defined and apply some formulas. The formulas section works fine but I'm hoping you guys can help me tweak my looping process.

Problem 1: The loop will run through the entire range that contains data but for some reason, omits the last row that has data in it. Then returns a type mismatch error.

Problem 2: It seems to only want to work when I've converted my data into a table. This may be a standard thing that I need to do and am just ignorant.

Problem 3: I have to have cell D2 as the active for the loop to work.

Thoughts? Here's my code. I've left out my calculation part because it's proprietary but you get the gist of it.

Sub CaseStatement()

Dim current As Integer
Dim merchantID As String
Dim stlAmt As Double
Dim intChng As Double
Dim MCC As Integer
Dim rng As Range
Dim n As Long




Application.ScreenUpdating = False




Set rng = Range(Range("D2"), Range("D" & Rows.Count).End(xlUp))
For n = rng.Count To 1 Step -1
    With Range("E" & n)






Range("A" & n).Select
merchantID = Range("B" & (ActiveCell.Row)).Value
MCC = Range("A" & (ActiveCell.Row)).Value
stlAmt = Range("C" & (ActiveCell.Row)).Value
intChng = Range("D" & (ActiveCell.Row)).Value

End With
Next n


End Sub

 

[osscie3]

Always helps when you can look at the Immediate window for debugging. Sheesh. Ok, so it may be doing a mismatch on my column header. For my test data I have 8 rows, the Immediate window for debugging is listing all of my number values in that column then the last one is my column header (MCC). Here is what the debugger sent back in detail:

 8  5555 
 7  555 
 6  5555 
 5  5555 
 4  5555 
 3  5555 
 2  5555 
 1 MCC

 

[RickXL]

so:

 For n = LastRow To 1 Step -1

should be:

For n = LastRow To 2 Step -1