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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,249
Office Version
  1. 365
Platform
  1. Windows
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.
 

JaccoVis90

New Member
Joined
Oct 30, 2015
Messages
34
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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,249
Office Version
  1. 365
Platform
  1. Windows
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:

JaccoVis90

New Member
Joined
Oct 30, 2015
Messages
34
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:

Forum statistics

Threads
1,136,328
Messages
5,675,137
Members
419,551
Latest member
thangxpm

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
Top