loop from cell i to last cell non-empty cell

Question

As the title says, I want to loop from cell i to the last non-empty cell. But I keep getting a type mismatch error even though I am declaring it the same type.

Dim wsList as Worksheet
Dim wsCode as Worksheet

Dim i as Variant
Dim j as Long

Dim LastCell as Variant
Dim LastCell2 as Long

With wsList 
    LastCell = .Cells(.Rows.Count, "G").End(xlUp) 
    LastCell2 = .Cells(.Rows.Count, "F").End(xlUp)
End With

    For i = 1 To LastCell
        wsList.Range("G2").Offset(i - 1).Copy _
            wsCode.Range("C2").Offset((i - 1) * 14)
    Next i

    For j = 1 To LastCell2
        wsCode.Range("F11").Offset((j - 1) * 14).Value = _
            wsList.Range("F2").Offset(j - 1).Value
    Next j

End Sub

Mismatch error occurs between LastCell and i even though I declared them both as variant. I also tried declaring them as string but still getting the same error. The data type in that particular column is "AB12345" including quotation marks. How can I fix this?

Thanks in advance!

It's in the correct format in my code, just messed it up as I was copying it here. — actuarial.codes, Jul 22 '21 at 16:12
it should be `LastCell = .Cells(.Rows.Count, "G").End(xlUp).Row` and `LastCell2 = .Cells(.Rows.Count, "F").End(xlUp).Row` and declare them both as `Long` — Scott Craner, Jul 22 '21 at 16:15
[Finding last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) — Siddharth Rout, Jul 22 '21 at 16:22

score 1 · Accepted Answer · answered Jul 22 '21 at 16:40

1

As stated in the comments, you're missing a .row. It would be best to define all your row variables as Long. One other thing to consider is that your method of using end(xlUP) will not account for any rows that are hidden. This answer discusses a variety of ways to address this.

With wsList 
    LastCell = .Cells(.Rows.Count, "G").End(xlUp).row
    LastCell2 = .Cells(.Rows.Count, "F").End(xlUp).row
End With

answered Jul 22 '21 at 16:40

pgSystemTester

8,979
2
23
49

1

That worked, thanks a lot! variant type was okay for both. I will look over the links you mentioned but for now I am not worried about hidden rows as my current sheet doesn't have any hidden rows. – actuarial.codes Jul 22 '21 at 17:34

score -1 · Answer 2 · answered Jul 22 '21 at 16:15

-1

I think you need to change

Dim LastCell as Variant

To

Dim LastCell as Long

Also, you should clearly identify the sheets for wsList and wsCode.

answered Jul 22 '21 at 16:15

Dave Lett

88
2

No. OP doesn't define .row at end of statement. Variant would be fine. – pgSystemTester Jul 22 '21 at 16:18
Declaring them Variant and Long doesn't work either. And I have set wsList and wsCode in the workbook. There is no mismatch between j and LastCell2 as that column contains numbers. But column G is formatted as "AB12345" as I mentioned. – actuarial.codes Jul 22 '21 at 16:26
Can you show all of the code, including where you're setting the variables for wsList and wsCode? – Dave Lett Jul 22 '21 at 16:46
The other answer worked! Thanks for your input I appreciate it :) – actuarial.codes Jul 22 '21 at 17:35

loop from cell i to last cell non-empty cell

2 Answers2

Linked