Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Question to revise VBS script excel conditional formatting

Posted on 2009-05-05
10
270 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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:
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

856 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