0

I have 8 columns that i am iterating through with a for loop. I excuted my code for only one column and used this to get the range of one column Worksheets("x").Range("H:H"). The code executes fine.

Set study_visit_rng = Worksheets("wksht").Range("H:H") - this will work fine

however, when I use the .Columns object to iterate it throws a type mismatch when i am searching that column

For v = 8 To 18
    Dim study_visit_rng As Range
    Set study_visit_rng = Worksheets("wksht").Columns(v) 'using the columns object
    
    'iterating through the columns   
    
For Each cell In study_visit_rng
   If Trim(cell.Value) = "RES" Or Trim(cell.Value) = "SOC" Then '~ I get the type mismatch here
     If InStr(Range("C" & (cell.Row)).Value, "History") Then
       'i do something
     End If 
   End If
Next cell
BigBen
  • 46,229
  • 7
  • 24
  • 40
Zacharia Sawaged
  • 1
  • `Set study_visit_rng = Worksheets("wksht").Columns(v).Cells`, or `For Each cell In study_visit_rng.Cells`. – BigBen Sep 09 '20 at 18:36
  • With your current code, `cell` represents the *entire column*, not the individual cells in the column.... see the linked dupe for more detail. Since `cell` refers to the entire column, `cell.Value` is a 2D Variant array and attempting to compare it to a string like "RES" will throw a Type Mismatch error. – BigBen Sep 09 '20 at 18:39
  • Note that you could use `UsedRange` or [find the last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) .... otherwise you'll end up iterating over *every single cell* in the column, all the way down to the end of the spreadsheet. – BigBen Sep 09 '20 at 18:41

0 Answers0