Solved

Question to revise VBS script excel conditional formatting

Posted on 2009-05-05
10
268 Views
Last Modified: 2013-11-25
Hi all,

Recently I was helped to generate this script pasted below.  What I would like to do is revise it, is it possible instead of where the column = "##" to state where NOT, simular to SQL where you can have a statement like:

WHERE NOT (myfield in('1234','6789','0987'))

The section I am focusing on in the code is this:

For Each cell In rngH
  If cell<>"" Then
    If Instr(cell,"##") Then

Thanks for your help in advance!
Call DoStuff

 

Sub DoStuff()

 

Set xlApp = CreateObject("Excel.Application")

Set xlWb = xlApp.Workbooks.Open("D:\NewMonthly\export\newplayers\newplayers.XLS")

Set xlWs = xlwb.Sheets("newplayers.XLS")   'amend as appropriate

 

with xlWs

  Set rngJ = xlApp.Intersect(.UsedRange,.Range("R:R"))

  Set rngH = xlApp.Intersect(.UsedRange,.Range("H:H"))

End With

 

For Each cell in rngJ

  If Isnumeric(cell) Then

    If cell>50000 Then 

       cell.EntireRow.Interior.ColorIndex = 6

       cell.EntireRow.Font.Bold = True

    Else

       cell.EntireRow.Interior.ColorIndex = -4142

       cell.EntireRow.Font.Bold = False

    End If

  Else

       cell.EntireRow.Interior.ColorIndex = -4142

       cell.EntireRow.Font.Bold = False   

  End If

Next

 

For Each cell In rngH

  If cell<>"" Then

    If Instr(cell,"##") Then

       cell.EntireRow.Interior.ColorIndex = 3

       cell.EntireRow.Font.Bold = True

'           cell.Interior.ColorIndex = 6

'      cell.Font.Bold = True

    End If

  End If

Next

 

xlWb.Save

xlWb.close

 

xlApp.Quit

Set xlApp = Nothing

 

End Sub

Open in new window

0
Comment
Question by:smyers051972
  • 4
  • 3
10 Comments
 
LVL 6

Expert Comment

by:zipa72
ID: 24305565
Instr function will return a value greater than zero if it founds the match. If not, the value will be zero.
You should write the function like this:

If InStr(cell, "##") <1 Then
0
 
LVL 1

Author Comment

by:smyers051972
ID: 24305687
What I am looking for however is a list of IF NOT's which could include criteria of 4 or 5 different ones and more of a BEGINS WITH scenario. I think I forgot to mention that above sorry...

So the criteria is more like : WHERE NOT '123%', '432%' and so fourth.
0
 
LVL 6

Expert Comment

by:zipa72
ID: 24305720
Do you have fixed number of criteria? If so, you can do something like:
If Instr(cell, "##")<1 And Instr(cell, "...")<1 And ... Then
0
 
LVL 1

Author Comment

by:smyers051972
ID: 24305809
Actually I used "##" as just an example.  This will be text based, I am looking at source of business called "sourcecode", so if the source code is anything like:

src1%
src2%
src5%

Delete (for example).

Under that criteria, if src15 was present, it would be deleted but if the source code was src35 it would remain.

Hope this helps :)
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
ID: 24320603
Assuming you want to delete :-
  • src1%
  • src2%
  • src5%
Then you can use the following set of code which will do what you want ..
If Instr(cell, "src1")>1 or Instr(cell, "src2")>1 or Instr(cell, "src5")>1 then
Now this will delete src15% since it has src1, similary stands for src25%, However it wont delete src35%
Saurabh...
 
0
 
LVL 1

Author Comment

by:smyers051972
ID: 24326269
Ok I will test this out, what about IF NOT's ? would I be correct to assume this can be done?
0
 
LVL 6

Accepted Solution

by:
zipa72 earned 500 total points
ID: 24326556
smyers051972,

The function InStr(...) returns the value grater than zero if the string is found.
That means that you can look at the equivalence for:

InStr(...)>=1   => DOES CONTAIN

and

InStr(...)<1 => DOES NOT CONTAIN

The rest is up to your program logic, for example if you want to contain "aaa" or "bbb", you will put:

If InStr(cell, "aaa")>=1 Or InStr(cell, "bbb") Then

but if you want it NOT to contain neither "ccc" and "ddd", you will put:

If InStr(cell, "ccc")<1 And InStr(cell, "ddd")<1 Then

hth
0
 
LVL 1

Author Closing Comment

by:smyers051972
ID: 31578038
VERY helpful! Thank you for explaining it to me!
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now