Type mismatch error obtaining value from a cell

**ssu95bm** · 03-09-2015, 07:21 AM

Hi there,

This is probably quite simple, but I haven't found the answer on the forum or elsewhere... please help

Dim ProjectNumber As Long
Dim TableRow As Integer
TableRow = 6

Do While Not IsEmpty(Workbooks(BacklogWorkbook).Sheets("Backlog Data").Range("a" & TableRow))

    ProjectNumber = Workbooks(BacklogWorkbook).Sheets("Backlog Data").Range("d" & TableRow)

' rest of the Do While loop follows

So the idea is that it is going through a set of data one row at a time (using TableRow), and the first task is to extract the ProjectNumber from column d of the row.

The final line of my code (ProjectNumber = etc) gives me a type mismatch error when I run it.
The same result occurs with:

Dim ProjectNumber as Integer

On the other hand, if I do this:

Dim ProjectNumber as Range

I get this error instead:
Object variable or With block variable not set

...which makes no sense to me as ProjectNumber is not an object (unless Range is an object of course, but in that case surely I have set it?)

Confused!!

Any help appreciated... thanks

ssu95bm

**jindon** · 03-09-2015, 07:31 AM

Most probably
Workbooks(BacklogWorkbook).Sheets("Backlog Data").Range("d" & TableRow)
is not a Long type data.

This is all that I can say from your code.

**ssu95bm** · 03-09-2015, 07:36 AM

Sorry, Is should have said what the cell contains... it is a 5 digit number, for example 20752. Right clicking on the cell shows it has a format of "general".

**jindon** · 03-09-2015, 07:42 AM

That's strange.

Can you upload a workbook with just that data?

**ssu95bm** · 03-09-2015, 07:54 AM

Hi there,

I have anonymized the data (and reduced it to 2 rows of data) and attached the file.

The mismatch occurs on line 111 of the code. Note that the code is divided into a number of numbered sections, line 111 is section 7.

Thanks!
ssu95bm

**jindon** · 03-09-2015, 08:10 AM

OK

You have formulas in col.A so it is not Empty.
change to

Do While Workbooks(BacklogWorkbook).Sheets("Backlog Data").Range("a" & TableRow) <> ""

**ssu95bm** · 03-09-2015, 09:08 AM

Hi there,

I have amended the code as suggested. Now however I get a type mismatch on the do while row...?

ssu95bm

**Norie** · 03-09-2015, 09:18 AM

It's not possible to test your code as there's no worksheet named 'Stored Data' in the workbook and there's a request to choose a CSV file to open.

PS If you want to refer to the workbook the code is in you can use ThisWorkbook.

**ssu95bm** · 03-09-2015, 09:30 AM

With a colleague as the second pair of eyes, I think I have found it. An earlier part of the macro deleted some rows on the sheet "OA data" based on certain values. On "Backlog data" this then broke the references to rows 1, 2, etc and replaced them with #ref. #REF!, of course, is not a long or an integer!

It looks like I will need to replace all my cell references on the Backlog Data tab with indirect references.

Thanks.
ssu95bm.

Type mismatch error obtaining value from a cell

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Type mismatch error obtaining value from a cell

Re: Type mismatch error obtaining value from a cell

Re: Type mismatch error obtaining value from a cell

Re: Type mismatch error obtaining value from a cell

Re: Type mismatch error obtaining value from a cell

Re: Type mismatch error obtaining value from a cell

Re: Type mismatch error obtaining value from a cell

Re: Type mismatch error obtaining value from a cell

Re: Type mismatch error obtaining value from a cell

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Run-time error '13': Type mismatch - How to work with Cell.value

putting an if condition based on text in a cell - runtime error 13: type mismatch

[SOLVED] Display cell reference - runtime error type mismatch

Type Mismatch Error when deleting a cell

Help: Compile error: type mismatch: array or user defined type expected

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions