[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Question to revise VBS script excel conditional formatting

Posted on 2009-05-05
10
Medium Priority
?
280 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
8 Comments
 
LVL 6

Expert Comment

by:Boris Petrovic
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:Boris Petrovic
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
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:
Boris Petrovic earned 2000 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

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

834 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