Problem with For..Next Loop - Type mismatch error

Question

I have a large Excel macro that formats a spreadsheet of raw, downloaded information.  I posted a question about this loop situation back in March of 2015 and was able to use the replies to create the steps needed to add borders for each line containing the word "total" (Total, Subtotal, etc.).  The steps were working fine (2 years without issue!), until recently and now I cannot get the borders to appear.  It stops at this section of my macro, indicating a Type Mismatch.  Can you tell me what I need to change to eliminate the error?

'Sub DoBorders

 Dim rangData As Excel.Range

 Dim M As Long

 Dim longBottomRow As Long

 Const STR_TEXTS As String = "Total"

   Set rangData = Selection.Columns(1).Cells

   longBottomRow = rangData.Rows.Count

  For M = longBottomRow To 1 Step -1

     If VBA.InStr(1, rangData(M).Value2, STR_TEXTS, vbTextCompare) > 0 Then '(this line highlighted in the debug window)

       rangData(M).Offset(0, -1).Range("A1:O1").Select

       Selection.Borders(xlEdgeTop).LineStyle = xlContinuous

     End If

  Next 'M

Thanks in advance for any advice.

Answer 1

JBurnside

re: code error

Your code works for me in xl2010 and it should work in xl2016.

From the many complaints I've seen about xl2016, I've concluded it is not worth having.

What you can try is to relace .Value2 with .Value 

Probably won't make a difference, but try it.

Also, you can simplify your code by replacing...

  rangData(M).Offset(0, -1).Range("A1:O1").Select

  Selection.Borders(xlEdgeTop).LineStyle = xlContinuous

With...

  rangData(M).Offset(0, -1).Range("A1:O1").Borders(xlEdgeTop).LineStyle = xlContinuous

'---

Jim Cone

Portland, Oregon USA

https://goo.gl/IUQUN2 (Dropbox)

(free & commercial excel add-ins & workbooks)

Answer 2

Thank you!  I think that fixed it!  I appreciate the quick reply. - jb