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.
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.
Code:
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