Mismatch error or method failed when using cells function

[immigrated4urjob]

I am learning VBA arrays, and have been fiddling around by replacing Range for Cells function by iterating within cells instead of Offset property. Debugger always points to line in bold.


Original line of code:

Please Login or Register  to view this content.

• Modified code (debugger pointing to this line):

Please Login or Register  to view this content.

• Another attempt to iterate on the iterator i

Please Login or Register  to view this content.

This seems to work:

Please Login or Register  to view this content.

-------------------------------------------------------------------------------------------------

Here is the full code:

Please Login or Register  to view this content.

Here is the test data picture, if it helps:

Attachment 744682

[6StringJazzer]

It's not clear what your question is. Your last code snippet says "This seems to work."

Also, we can't tell what's going on from a screen shot. Please see yellow banner at the top of the page and attach your actual file (it's easier to attach the Excel file than to take a screen shot and attach that).

In Excel it is best to take advantage of the object model rather than creating detailed steps. A range on a worksheet can be loaded into an array in one step. We can also take advantage of the fact that you are using a Table. I don't know your table name but let's say it's Table1. Note that you no longer care how many rows are in the table--Excel takes care of that for you.

Also, if this code is in the module for worksheet M you do not need to qualify your range references; the variable sh is unnecessary.

Here is your new and improved code.

Please Login or Register  to view this content.

[curiouscat408]

@immigrated4urjob.... First, Range("c2") and Cells(1,2) are not the same cell reference. The second syntax is Cells(row,col).

So C2 is Cells(2,3) or Cells(2,"c"). And in the For-loop, Cells(i,"c") cycles through rows i of column C.

To see the difference with your original references, test with the following code:

Please Login or Register  to view this content.

Aside.... If you are adept at using the Immediate Window (ctrl+g), replace MsgBox with Debug.Print.

Second, even Cells(i,"c") is not the same cell references as Range("c2").Offset(i). The latter addresses rows 2+i, as you can see in the output from the For-loop above.

So, Cells(i,"c") should be Cells(2+i,"c"). To confirm, plug that into the For-loop above.

[immigrated4urjob]

Sorry my question is how you would use sh.Cells(i,j).value instead of sh.Range().Offset.Value, without the debugger throwing Object method failure. Basically how do I make this version work:

Please Login or Register  to view this content.

The table range is A1: B4.

The point of using this method was to remove the need for using Offset method with range, and make this line compact.

[6StringJazzer]

Sorry my question is how you would use sh.Cells(i,j).value instead of sh.Range().Offset.Value, without the debugger throwing Object method failure. Basically how do I make this version work:

Please Login or Register  to view this content.

The table range is A1: B4.

You would not do that. You would put the code into the module for worksheet M and do this

Please Login or Register  to view this content.

However, the reason you are getting an error is that you are qualifying the Range with sh but you are not qualifying the Cells. This should get rid of the error

Please Login or Register  to view this content.

but it is not good coding to put a single Cell reference inside a Range.

[immigrated4urjob]

@immigrated4urjob.... First, Range("c2") and Cells(1,2) are not the same cell reference. The second syntax is Cells(row,col).

So C2 is Cells(2,3) or Cells(2,"c"). And in the For-loop, Cells(i,"c") cycles through rows i of column C.

To see the difference with your original references, test with the following code:

Please Login or Register  to view this content.

Aside.... If you are adept at using the Immediate Window (ctrl+g), replace MsgBox with Debug.Print.

Second, even Cells(i,"c") is not the same cell references as Range("c2").Offset(i). The latter addresses rows 2+i, as you can see in the output from the For-loop above.

So, Cells(i,"c") should be Cells(2+i,"c"). To confirm, plug that into the For-loop above.

I have changed to Range("B1"). It was a typo as the code was copied from excelmastery.com, and forgot to modify it to B1.

[curiouscat408]

students = Range("Table1[mark]")

Caveat.... While that might indeed be more efficient, the method above creates a 2-dimensional array, whereas the original example creates a 1-dimensional array.

Sometimes, the distinction is important, as it is with the original declaration Dim students(1 To 3) As Long.

Of course, 6StringJazzer made the correct additional changes. But the point is: sometimes we actually want a 1-dimensional array.

[curiouscat408]

I have changed to Range("B1"). It was a typo as the code was copied from excelmastery.com, and forgot to modify it to B1.

But even Range("b1").Offset(i) is not the same as Cells(i,"b").

The point is: Offset(i) adds the value of i to the row number of the Range or Cell reference.

If you want to cycle through B1, B2, B3 etc, you can write Range("b1").Offset(i-1) or Cells(i,"b").

[immigrated4urjob]

Please Login or Register  to view this content.

I tried simplifying to above, however it doesn't work either. Maybe arrays work with Range object instead of Cell objects?

Also tried your code but get Method 'Range of object worksheet failed

Please Login or Register  to view this content.

But even Range("b1").Offset(i) is not the same as Cells(i,"b").

The point is: Offset(i) adds the value of i to the row number of the Range or Cell reference.

If you want to cycle through B1, B2, B3 etc, you can write Range("b1").Offset(i-1) or Cells(i,"b").

That is the end goal however the mismatch error is puzzling. Trying to use students(i)=sh.Cells(i,2).Value.

[curiouscat408]

Students(i) = sh.Cells(i, 2).Value
[....]
I tried simplifying to above, however it doesn't work either. Maybe arrays work with Range object instead of Cell objects?

It is not a problem with "arrays working with" Range or Cells objects.

Range and Cells are simply two equivalent ways of referencing a cell (in this case).

It does not matter what is on the left-hand side (the array reference).

You have posted a code snippet out of context. It is no longer clear how "Students" is declared.

I suspect that you took part of 6StringJazzer's suggestions out of context. The following gives me a "type mismatch" error:

Please Login or Register  to view this content.

The code works if we restore the original declaration, to wit: Dim students(1 to 3) As Long.

If that does not remedy the "type mismatch" error (because my guess is wrong), heed the instructions in the bright yellow banner above, to wit: attach an Excel file (not an image) that has enough data to duplicate the problem(s). That does not have to be the original Excel file, and it should not include private data.

-----

Also tried your code but get Method 'Range of object worksheet failed
[....]
sh.Range(sh.Cells(i,2)).Value

I get a different error message (``application-defined or object-defined error``). Perhaps it varies with the Excel/VBA version.

In any case, that syntax is indeed wrong, although I don't know why. The following would work (but it is not recommended): sh.Range(sh.Cells(i, 2), sh.Cells(i, 2)).

But 6StringJazzer was not suggesting that, especially with my absurd "correction". He was merely pointing out that you must write sh.Cells, not simply Cells, in that context.

The real point is: even if we could write sh.Range(sh.Cells(i,2)), it is redundant.

It is sufficient to write sh.Cells(i,2) or sh.Range("B" & i).

Again, if that does not remedy the problem(s), heed the instructions in the bright yellow banner above, and attach an Excel file (not an image).

[immigrated4urjob]

@curiouscat408

I have attached the file in the original post.

It is the second procedure called Public Sub StudentMarksArr()

[curiouscat408]

@immigrated4urjob.... There is an obvious syntax error: the function UBound is split after "UB".

After that is fixed, the "type mismatch" error occurs on the statement Students(i) = Cells(i, 2).Value.

If you are adept at using the Immediate Window (after all, you have Debug.Print statements), you can use the debugger to see what is wrong.

? i
1
? Cells(i, 2).Value
mark

Now look at B1 in Excel: it is the "title" for the column ("mark").

We cannot assign a string (*) to a type Long variable ("students"). (* Unless VBA can interpret the string as a number.)

But that is not what you want to do, anyway.

Ostensibly, your loop should be For i = 2 to 4. Then you can write:

Students(i-1) = Cells(i, 2).Value

Or keep it Students(i), and change the declaration, to wit: Dim Students(2 to 4) As Long.

That said, your implementation is too specific for the data.

In fact, you have the comment "Declare an array to hold marks for 5 students", but your array holds only 3.

ERRATA.... Well, your implementation __is__ too specific for the data, IMHO. But the quoted comment probably is not relevant; it is probably a vestige of some code that you modified. For example, there is another comment that obviously is not intended to be relevant, to wit: "Read student marks from cells C3:C7 into array".