VBA Excel If cell value is empty ERROR

JaccoVis90

New Member
Joined
Oct 30, 2015
Messages
34
I had a good working VBA program to copy in order and sum things up.

I wanted to make my program even better after the client worked with it a bit so I changed some small stuff and tryed to run the macro.

It came back with an error on the next line

Code:
For Each cell In newven.Range ("G:G")
If cell.Value <> "" Then

this was not the part I was working on so I tryed also a version that worked previosly good.

The same error occurred
Error 13 runtime,
mismatch

How is this possible and how can I make it work again
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
How is "newven" defined?
Is it a sheet in the same workbook as the VBA code?
And typically, you should NOT have a space between Range and ("G:G").

And do you really want to check every single cell in column G? That seems like a bit overkill. Usually, you try to find the last row with data in column G, and only go down that far to avoid unnecessary looping.
 
Upvote 0
newven is defined as a worksheet and is not in the same workbook as the VBA code

* the space is a type error only on the site, thanks for reading everything well

Good tip a will change it with finding last row in column G

In the same macro before the error I have the code

Code:
Dim newws As Worksheet

For Each cell In newws.Range("A:A")
If cell.Value = "ND" Then

This code works normal
 
Upvote 0
OK, if the worksheet is in a different workbook, you will need to Activate that workbook in your VBA code first before you can access worksheets and ranges in that workbook.

Also, the way you have written this code so far:
Code:
Dim newws As Worksheet

For Each cell In newws.Range("A:A")
If cell.Value = "ND" Then
You have declared the newws variable, but you haven't set it equal to anything yet. So there is nothing to loop through.
 
Last edited:
Upvote 0
I have set the variable
Code:
[FONT=Calibri][SIZE=3][COLOR=#000000]  Set newws =newwkb.Worksheets("verzameling")
 with newwkb set as Workbooks.add
[/COLOR][/SIZE][/FONT]

but i have a big code so I forgot to post is.

Also I found an error in 1 of the documents the code opens so I am now looking if this is giving the error.
in the document the cell returns an #-value
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top