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

 

[RickXL]

Hi,

It is a bit difficult to ensure that something works when part of it is missing.

However, if I had written it it would look more like the following.
Note, you may need to change the worksheet name from Sheet1 to whatever you are using.
Also note the dots at the start of come properties (e.g. .Cells() or .Range() ). You need those for the With statement to have an effect.

Sub CaseStatement()

    Dim current As Integer
    Dim merchantID As String
    Dim stlAmt As Double
    Dim intChng As Double
    Dim MCC As Integer
    Dim LastRow As Long
    Dim n As Long
    Dim ws As Worksheet
    
    Application.ScreenUpdating = False
    
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    With ws

        LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
        For n = LastRow To 1 Step -1

  
            merchantID = .Cells(n, "B").Value
            MCC = .Cells(n, "A").Value
            stlAmt = .Cells(n, "C").Value
            intChng = .Cells(n, "D").Value
            
        Next
        
    End With

End Sub

 

[osscie3]

Hi, thanks so much for your reply.

I edited my code to to follow yours exactly. Getting a type 13 mismatch, debug points to MCC = .Cells(n, "A").Value

It looks like it's not getting through the loop so something is throwing it off. The calculations aren't being done. Sorry I'm still learning the ropes on For loops.

Thoughts?

 

[RickXL]

Hi,

Mismatch means what is in that cell is not what MCC is expecting - which is an integer.

So what is in that cell?

You could get Excel to tell you. In the line above the MCC one add this line:

Debug.Print n; .Cells(n, "A").Value

That should print the value of n followed by the value in the cell in the Immediate window.

 

[icecreame]

Hi,
Does anyone knows how to stop the loop at a string example, the "(blank)" of a pivot chart


Do Until I = "(blank)"


K = ActiveCell.Row
Range("M5").Select
Call Diver
Range("E" & K).Select
Call Diver
Sheets("FREQUENCY").Select
ActiveChart.ChartArea.Select
ActiveChart.ChartArea.Copy
Workbooks(X).Activate
Range("A" & F).Select
ActiveSheet.Pictures.Paste.Select
Selection.ShapeRange.ScaleWidth 0.7, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 0.7, msoFalse, msoScaleFromTopLeft


F = F + 11
Workbooks("SETUP OOC").Activate
Sheets("PIVOT").Select
Call View_All
Range("E" & K).Select
ActiveCell.Offset(1, 0).Select
I = ActiveCell.Value




Loop

 

[Michael M]

icecreame
Can you please start a new thread of your own.....logging to others threads only causes confusion for the original poster trying to solve their own issue.
AND
could you please use code tags when posting code....see the bottom of my tag.

 

[icecreame]

sorry my bad

 

[osscie3]

So I've put in the Debug.Print code, ran the macro, and it seems like it completely skipped over that line of code. Same runtime 13 mismatch error came up and highlighted the MCC line. In my test data, I've also ensured that column A is formatted to the number property as opposed to general or a string so I'm really confused. Here's my code:

Sub test()


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
Dim ws As Worksheet
Dim LastRow 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)
Set ws = ThisWorkbook.Worksheets("Sheet1")
    With ws


        LastRow = .Cells(Rows.Count, "D").End(xlUp).Row
        For n = LastRow To 1 Step -1
        
            Debug.Print n; .Cells(n, "A").Value
            MCC = Cells(n, "A").Value
            merchantID = Cells(n, "B").Value
            stlAmt = Cells(n, "C").Value
            intChng = Cells(n, "D").Value
            MCC = Cells(n, "A").Value


'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


'MsgBox (MCC)

    Next
End With


'End With
'Next n

 

[osscie3]

So I've done some editing to my code. I tweaked some of my formulas (going from something like Range("C" & (ActiveCellRow)).Value = 1+2, to .Cells(n, "D").Value = 1 + 2. This seemed to clean up a lot. The macro will now run through the entire sheet and do all the correct calculations. Basically it does what it's supposed to do. However, when I run the macro, before anything happens, I still get the mismatch error (still pointing to MCC = .Cells(n, "A").Value, I will click End and then the macro runs through everything fine.

From what it looks like, the first 3 problems in my original post have been solved so I really appreciate your help on all this.

At this point we are splitting hairs I know but any thoughts as to why it would still be a mismatch? I've declared the MCC variable as an integer. I've ensured that column A is formatted to a number and even has numbers in it. At this point, I'm almost fine with putting in an On Error Resume Next type thing.

Thanks

 

[RickXL]

Are you sure you are looking in the Immediate window for the output? Ctrl + G when in the VB Editor switches it on if it is not there. Also, View--> Immediate Window will work as well.

It should be showing a list of the values of n even if there is nothing else.

My view of "On Error Resume Next" is that it should only be used if there is no alternative. You would be amazed how many questions we get here from people who say their program is failing but is not giving them an error message then when you look at the code you see that they have switched them all off! If I am asked to fix some failing code and it has On Error Resume Next in it I always comment it out as a first step.