CODEWITHSUNDEEP
vbaexceldo-whiletype-mismatch
FatBatman
FatBatman

Reputation: 35

VBA type mismatch on DO WHILE NOT

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?

Upvotes: 0

Views: 836

Answers (2)

Pᴇʜ
Pᴇʜ

Reputation: 57683

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:

Upvotes: 1

Vityata
Vityata

Reputation: 43575

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

Upvotes: 0

Related Questions