Type missmatch error

[kma]

Hi,

I am trying to find out if the value of the first cell in a row is blank but unfortunately I get a type missmatch error in row 2. My code is the following:


1 For Each r2 In Worksheets(sheet).Range(regRange).Rows
2 If r2.Item(1).Value <> "" Then

Could you please help to solve this problem? Thanks a lot!

[Paul_Hossler]

r2 is a row and rows are made up of cells

I'd do something like



For Each r2 in Worksheets(sheet).Range(regRange).Rows
If Len(r2.Cells(1,1).Value) > 0 Then

'or maybe
If Len(r2.Cells(1).Value) > 0 Then

[Leith Ross]

Hello kma,

Change Item To Cells and it will work.

[kma]

Thanks for your quick help! The code works now.

Just for my understanding: What exactly was causing the error? Item(1).Value works fine to get the value of a cell.

[SamT]

Item is a generic term that refers to one 'thing' in a Generic Collection.

Cells is a specific Collection, that of all the cells in a given Range.

Depending on the specific syntax of the code, Item can work with a Range Collection. Since I always use the Range specific Collection, Cells, I don't know the details of making Item Work with Ranges every time.

[Leith Ross]

Hello kma,

This may provide a little more insight into the differences...



Range.Item Property


Returns a Range (http://www.vbaexpress.com/forum/ms-help://MS.EXCEL.DEV.14.1033/EXCEL.DEV/content/HV10331892.htm) object that represents a range at an offset to the specified range.


Range.Cells Property
Returns a Range (http://www.vbaexpress.com/forum/ms-help://MS.EXCEL.DEV.14.1033/EXCEL.DEV/content/HV10331892.htm) object that represents the cells in the specified range.