I have used the following syntax to determine the range for a For Each
statement. However I'd like the end row to be 1 less than is returned so I
enclosed a -1 in the bracket. I'm now getting a mismatch error from VBA.
How do I fix?

Many thanks
Jacqui

Set myRng = .Range("c20", .Cells(.Rows.Count, "c").End(xlUp) - 1)

Hi Jaqui,

Try:

Set myRng = .Range("c20", _
.Cells(.Rows.Count, "c").End(xlUp))

Set myRng = myRng.Resize(myRng.Count - 1)


---
Regards,
Norman



"Jacqui" wrote in message
...
I have used the following syntax to determine the range for a For Each
statement. However I'd like the end row to be 1 less than is returned so
I
enclosed a -1 in the bracket. I'm now getting a mismatch error from VBA.
How do I fix?

Many thanks
Jacqui

Set myRng = .Range("c20", .Cells(.Rows.Count, "c").End(xlUp) - 1)

How about:

Set myRng = .Range("c20:C" & .Cells(.Rows.Count, "c").End(xlUp).row - 1)

well, as long as there was something in column C after row 1.



Jacqui wrote:

I have used the following syntax to determine the range for a For Each
statement. However I'd like the end row to be 1 less than is returned so I
enclosed a -1 in the bracket. I'm now getting a mismatch error from VBA.
How do I fix?

Many thanks
Jacqui

Set myRng = .Range("c20", .Cells(.Rows.Count, "c").End(xlUp) - 1)

--

Dave Peterson

Try

Set myRng = .Range("c20", .Cells(.Rows.Count, "c").End(xlUp).row - 1)

HTH

"Jacqui" wrote:

I have used the following syntax to determine the range for a For Each
statement. However I'd like the end row to be 1 less than is returned so I
enclosed a -1 in the bracket. I'm now getting a mismatch error from VBA.
How do I fix?

Many thanks
Jacqui

Set myRng = .Range("c20", .Cells(.Rows.Count, "c").End(xlUp) - 1)