VBA Excel If cell value is empty ERROR

[JaccoVis90]

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

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

 

[Joe4]

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]

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

Dim newws As Worksheet

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

This code works normal

 

[Joe4]

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:

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.

 

[JaccoVis90]

I have set the variable

[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