select a range

[hotherps]

Is there anyway that I can apply this statement to the 1st to the 42n
cell without a for next loop?

And .Cells(x, y).Offset(0, 1) <> "IND" _

As you can see it checks to see if the value = "IND" in the first cell
I also want to check the next 41 cells in that row for the sam
criteria.

So I want to see if "IND" is anywhere between 1 and 42

Thank

 

[BrianB]

Why not use Find instead ? or would COUNTIF() be better

 

[Tom Ogilvy]

So I want to see if "IND" is anywhere between 1 and 42
And Application.Countif(.Cells(x,y).offset(0,1).Resize(1,41),"IND") > 0 then

if you want to know if they all contain IND, then change > 0 to = 42

Seems like you have received this advice before.

 

[hotherps]

I guess I did not realize that Resize would work for this also.
thought that Resize would actually place values in all of the cells
not just count them.

I used it before in a situation where I needed to complete a ro
because the value fell short due to some other criteria and when I use
Resize I thought it just forced the value in the remaining cells.

Sorry for the similar pos

 

[Tom Ogilvy]

Resize itself does nothing. It expands the reference

Range("A1")
refers to A1
Range("A1").Value = 3
puts 3 in A1

Range("A1").Resize(1,3)
refers to A1:C1
Range("A1").Resize(1,3).Value = 3
puts 3 in A1, B1, C1

application.Countif(Range("A1").Resize(1,3),3)
is equivalent to

Application.Countif(Range("A1:C1"),3)

 

[hotherps]

I printed that explanation out, I'm sure I'll use it over and over.

Thanks To