VBA type mismatch on DO WHILE NOT

Question

I've got o problem with my macro:

Dim nr_kol As Integer
nr_kol = ActiveCell.Column
Worksheets("dane").Range("I1").Copy
Worksheets("dystr hist").Range("a1").Select
    ActiveCell.End(xlToRight).Offset(0, 1).PasteSpecial xlPasteValues

Do While Not ActiveCell.Offset(0, -nr_kol + 1) = ""
Selection.Offset(1, 0).Select
    ActiveCell.Value = Application.VLookup(ActiveCell.Offset(0, -nr_kol + 1).Text, Worksheets("temp2").Range("B:I"), 8, False)
Loop

It should work like this: it takes week number from "dane" sheet and paste it in "dystr hist" sheet in first free cell in first row. Then we have vlookup for items, until the list is finished. It worked for previous sheet, but in this particular place it crashes with type mismatch. Can anyone explain me why?

Have you tried Do While Not IsEmpty(ActiveCell.Offset(0, -nr_kol + 1)) — Subodh Tiwari sktneer, May 24 '17 at 10:02
Side Note: (1) You should [avoid using Select and Activate](http://stackoverflow.com/documentation/excel-vba/1107/vba-best-practices/9292/avoid-using-select-or-activate). (2) Always use `Long` instead of `Integer` (read [here](http://stackoverflow.com/a/26409520/3219613) why). — Pᴇʜ, May 24 '17 at 10:08

score 1 · Accepted Answer · edited Sep 21 '17 at 15:16

This is the explanation why that you asked for:

The issue is you select range A1 with

Worksheets("dystr hist").Range("a1").Select 
'here you do some copy which doesn't change cell selection of A1

and next you try to move a probably negative column value in

ActiveCell.Offset(0, -nr_kol + 1)

where -nr_kol + 1 is negative for nr_kol > 1. This doesn't work because A1 is the most top most left cell in the sheet and you just can't move left.

Solution:

Avoid using ActiveCell
Avoid using Select
Fully qualify your Cells/Ranges with worksheets
Use Long instead of Integer

Vityata · Answer 2 · 2017-05-24T13:12:02.997

0

What is -nr_kol in Do While Not ActiveCell.Offset(0, -nr_kol + 1) = ""

Probably you want to write Do While Not ActiveCell.Offset(0, (nr_kol + 1) *-1) = ""

In general, probably you may try something like this:

Dim nr_kol As long
nr_kol = ActiveCell.Column
Worksheets("dane").Range("I1").Copy
Worksheets("dystr hist").Range("a1").Select
    ActiveCell.End(xlToRight).Offset(0, 1).PasteSpecial xlPasteValues
ActiveCell.End(xlToRight).select
Do While Not ActiveCell.Offset(0, -nr_kol + 1) = ""
Selection.Offset(1, 0).Select
    ActiveCell.Value = Application.VLookup(ActiveCell.Offset(0, -nr_kol + 1).Text, Worksheets("temp2").Range("B:I"), 8, False)
Loop

The difference is in the change of the ActiveCell with the following code: ActiveCell.End(xlToRight).select

edited May 24 '17 at 13:12

answered May 24 '17 at 10:07

Vityata

42,633
8
55
100

2

`nr_kol + 1 *-1` is the same as `nr_kol -1` isn't it? – Pᴇʜ May 24 '17 at 10:09
1

@Peh - I am still sleepy. – Vityata May 24 '17 at 10:10
1

Still won't work. Selected cell is A1 (from 2 lines above in his code) and you try to move in negative direction. Can't work (see my answer). – Pᴇʜ May 24 '17 at 13:07
@Peh - he was probably meaning that the active cell is the last cell in the selection... I guess... – Vityata May 24 '17 at 13:12
yes, hard to tell without any information of the OP. Or he thinks the ActiveCell of the beginning before any macro ran is still selected. Nevertheless the code needs some improvement. – Pᴇʜ May 24 '17 at 13:36

VBA type mismatch on DO WHILE NOT

2 Answers2