Excel VB help ?

[Clarkey]

Anyone au faire with Excel VB ?

 

What I'm trying to do is select a multiple range in an Excel worksheet from VB. Eg - From cells A8:K8 for instance. Selecting a block. But under a criteria - basically the condition is:

 

Select the block while column's Cx is equal to the words CRITICAL or MAJOR or MINOR.

 

To make it abit clearer - in Excel - I have a table containing fault reports - the rows are sorted by the severity so all of the CRITICAL's first, MAJOR's, then MINOR's. Now this table is dynamic - obviously more faults will be added in.

 

What I want in the VB code is select all of the CRITICAL faults - then select all the MAJORS etc.

 

Now, anyone know how to manipulate the range in the code ? What I mean is - something like this Range( "A8:K8+severityCount( "CRITICAL") ") - so that would become A8:Kx.

 

SeverityCount is a function I've written to traverse the entire worksheet - returning the total count of CRITICAL's, etc.

 

Now I know that won't work. But I've been racking my brains of how to do it. Every solution - I've come up with - I've discarded because they're getting more exotic and complex.

 

I expect Excel would've have some libs already there to do just that. But can't find them in the MSDN library.

 

Any ideas ?

[Jake]

Can you attach an example of the layout? It's not clear to me what you're trying to do.

I don't know what you mean by "column's Cx"

[Supragal]

You would need to use a loop, so set a starting point ie. Cell a1.

 

'This tells the offset function where to start

 

RowNum = 1

'this tells the loop to continue until the column with the error codes in no longer contains Critical, you would need to change the column from "c" to what ever column is used

 

do while sheet1.range("C1").offset(RowNum,0).value = "Critical"

rownum = rowNum + 1

loop

 

sheet1.range("A1:K" & RowNum & ").select

 

I think this is what you are after, difficult without seeing the data but if you are not trying to select lots of rows but are trying to select lots of columns then you just need to change the offset(RowNum,0) for offset(0,Rownum).

 

Let me know if I this is what you want.

[Clarkey]

Sorry should've attached it!

[Clarkey]

sheet1.range("A1:K" & RowNum & ").select

 

Yes! That's exactly it. I did think of concatenating the string - but didn't think I could concatenate a string with an integer. eg - K + 20 ... to make K20.

 

[Supragal]

No probs. Seriously though, was it you at JAE that won the shower? I am very confused about that...

[Clarkey]

Seriously though, was it you at JAE that won the shower? I am very confused about that...

 

I'm just as confused as you. I wondered why I was getting all these PM's - "How's the shower mate ?"

 

No, I wasn't at JAE. I could only afford to go to either JAE or TOTB. And to honest - JAE isn't really my thing.

 

I didn't buy a raffle ticket. All I can think of is someone else on here - that has "Clarkey" in their user name ... but just wrote "Clarkey" on their ticket .. and forgot the rest of it.

 

If it's unclaimed - keep that shower for a prize next year.

[Supragal]

How odd I will ask Mad Matt about it!!