Solved: If range has values then.....

[Meatball]

I am trying to make a macro that will check a range for data, numbers or text, and put specific text in a cell above that range. I just ran into the Select Case statement but can't seem to get that or IF,Then to work.
Range to check, G18 to (last row of F), but F may have blanks above Row 18. If any data in this range put XXXX in G5. Also where can I find the numbers for Interior.ColorIndex =?

[Benzadeus]

Sub SearchPut()
Dim _
rngDB As Range, _
rngSearch As Range, _
rLast As Long, _
strData As String

strData = "Search This" '<== change to suit
With ActiveSheet

rLast = .Cells(.Rows.Count, "F").End(xlUp).Row
Set rngDB = .Range("F18:G" & rLast)
Set rngSearch = rngDB.Find(strData)

If Not rngSearch Is Nothing Then
.Range("G17") = strData
Else
MsgBox """" & """" & " not found on given range"
End If
End With
End Sub

[Benzadeus]

As for question #2, visit http://www.mvps.org/dmcritchie/excel/colors.htm

[Meatball]

Thanks Benzadeus. A couple of questions. Can I change Else
MsgBox """" & """" & " not found on given range" to Else Do Nothing or is there an equivilant. The other question was about the
strData = "Search This" '<== change to suit but I figured that out what you mean.

[mdmackillop]

As for question #2, visit http://www.mvps.org/dmcritchie/excel/colors.htm
or

Sub Colours()
Dim i
For i = 1 To 56
Cells(i, 1).Interior.ColorIndex = i - 1
Cells(i, 2) = i - 1
Next
End Sub

[Benzadeus]

Remove lines
Else
MsgBox """" & """" & " not found on given range"
from code.

[Meatball]

Well I was wrong about the line "strData = "Search This" '<== change to suit". I thought it refered to the result not the search. Exactly how would I write .Value <> "" into this as the search criteria? And so that means I am not clear on the result. With this code , if it found "Search This" in the range, what would happen?

[Meatball]

I have been testing the code and have some results. I have made changes to this Sub SearchPut()
Dim _
rngDB As Range, _
rngSearch As Range, _
rLast As Long, _
strData As String

strData = "Search This" '<== change to suit
With ActiveSheet

rLast = .Cells(.Rows.Count, "F").End(xlUp).Row
Set rngDB = .Range("F18:G" & rLast)
Set rngSearch = rngDB.Find(strData)

If Not rngSearch Is Nothing Then
.Range("G5") = "IN EXACTA"
Else
MsgBox """" & """" & " not found on given range"
End If
End With

End Sub

What I can not sucessfully do is add 2 more things to do if the search criteria is found. I want text added in G6 and color added to G5 & G6

[mdmackillop]

If Not rngSearch Is Nothing Then
.Range("G5") = "IN EXACTA"
.Range("G6") = "add your text here"
.Range("G5:G6).Interior.ColorIndex = 6
Else

[Bob Phillips]

or

Sub Colours()
Dim i
For i = 1 To 56
Cells(i, 1).Interior.ColorIndex = i - 1
Cells(i, 2) = i - 1
Next
End Sub



or ...
Here is an enumerated list which you can add to a module and then use the colour constant name.


Public Enum xlColorIndex
xlCIBlack = 1
xlCIWhite = 2
xlCIRed = 3
xlCIBrightGreen = 4
xlCIBlue = 5
xlCIYellow = 6
xlCIPink = 7
xlCITurquoise = 8
xlCIDarkRed = 9
xlCIGreen = 10
xlCIDarkBlue = 11
xlCIDarkYellow = 12
xlCIViolet = 13
xlCITeal = 14
xlCIGray25 = 15
xlCIGray50 = 16
xlChartCIPeriwinkle = 17 '-----------------------------
xlChartCIPlum = 18 ' chart colours
xlChartCIIvory = 19 '
xlChartCILightTurquoise = 20 '
xlChartCIDarkPurple = 21 '
xlChartCICoral = 22 '
xlChartCIOceanBlue = 23 '
xlChartCIIceBlue = 24 '
xlChartCIDarkBlue = 25 '
xlChartCIPink = 26 '
xlChartCIYellow = 27 '
xlChartCITurquoise = 28 '
xlChartCIViolet = 29 '
xlChartCIDarkRed = 30 '
xlChartCITeal = 31 '
xlChartCIBlue = 32 '-----------------------------
xlCISkyBlue = 33
xlCILightGreen = 35
xlCILightYellow = 36
xlCIPaleBlue = 37
xlCIRose = 38
xlCILavender = 39
xlCITan = 40
xlCILightBlue = 41
xlCIAqua = 42
xlCILime = 43
xlCIGold = 44
xlCILightOrange = 45
xlCIOrange = 46
xlCIBlueGray = 47
xlCIGray40 = 48
xlCIDarkTeal = 49
xlCISeaGreen = 50
xlCIDarkGreen = 51
xlCIBrown = 53
xlCIIndigo = 55
xlCIGray80 = 56
End Enum

[xluser2007]

Hi Bob,

Great use of Enumerations :yes.

Could you please explain the key advantages and examples of using them (Enumartions) in other code?

thanks and regards,

[Bob Phillips]

Good question!

IMO enumerated lists are a vastly underused function in VBA; I find them incredibly useful and use them all the time.

The obvious example is that they allow you to easily define a meaningful name in your application to represent system or application constants. For example, in the colorindex list I gave above, if you want to colour a cell red, instead of using



Activecell.Interior.Colorindex = 3


you can use



Activecell.Interior.Colorindex = xlCIRed


Not only is it more readable, it is more efficient.

But it doesn't just stop there.

When you have an enumerated list, you can then create a variable with that datatype. For instance, let us say that we have a list of return codes, a classic example of an enumerated list, that looks like so



Enum appReturnCodes

All_OK = 0
Invalid_Input = -1
Process_Error = -2
Too_Few_Params = -3
End Enum


In your code that looks for a return code, you can define that variable like so



Dim ReturnCode As appReturnCodes


which is nice as a documemtary feature, but gets better because when you then look to load that variable in the code, as you type

ReturnCode =

the typing of the = sign invokes intellisense for that list, so you can select it rather than type it.

Those are all good additions, but if you use late binding in your code, it is a good idea to create enumerated lists to emulate the application constants, applications such as Outlook that is. Here I am referring to some code that may use automation to interact with Outlook using late binding. You could (should?) use early binding in your testing, but in all likelihood you will use Outlook constants. When you convert to late binding, these constants will not be recognised. By building your own list, you can use those application constant names with no worries when you later switch to late binding.

This example will work with late or early binding



Private EnumOutlookConstants
olTo = 1
olCC = 2

olMailItem = 0
End Enum


Public Function SendMail()
Dim oOutlook As Object
Dim oMailItem As Object
Dim oRecipient As Object
Dim oNameSpace As Object


Set oOutlook = CreateObject("Outlook.Application")
Set oNameSpace = oOutlook.GetNameSpace("MAPI")
oNameSpace.Logon , , True

Set oMailItem = oOutlook.CreateItem(olMailItem)
Set oRecipient = _
oMailItem.Recipients.Add("bob.phillips@somewhere.com")
oRecipient.Type = olTo,
'keep repeating these lines with
'your names, adding to the collection.
With oMailItem
.Subject = "The extract has finished."
.Body = "This is an automatic email notification"
.Attachments.Add ("filename") 'you only need this if
'you are sending attachments?
.Display 'use .Send when all testing done
End With
End Function


_______
iosi enumeration

[Meatball]

mdmackillop, Thanks. I could have sworn that I tried that yesterday with no results but it works now.
xld, Thanks. I see your point about readability. The Outlook example may be useful also when I get time to tweak my process more.
The only thing I need to solve this thread is how to change the search criteria to look for any value at all in the search range, as in .Value <> "" . I believe it will require a change in these 3 areas of the code but do not know exacly how to change them;
strData As String
strData = "Search This"
Set rngSearch = rngDB.Find(strData)

[Benzadeus]

Very interesting. Thanks.

[xluser2007]

Good question!

IMO enumerated lists are a vastly underused function in VBA; I find them incredibly useful and use them all the time.

The obvious example is that they allow you to easily define a meaningful name in your application to represent system or application constants. For example, in the colorindex list I gave above, if you want to colour a cell red, instead of using

...





Bob,

What a great explanation.

My second question was going to be - what is the point of naming the Enumeration something specific e.g xlcolourindex. That is, why couldn't eveything be lumped into one name in a separate module as a public Enumeration.

But you have explained that with the 'in-built documentation' and intellisense advantages. Great stuff indeed :)!

Thanks and regards,

[Meatball]

To get back to the initial thread subject;

The only thing I need to solve this thread is how to change the search criteria to look for any value at all in the search range, as in .Value <> "" . I believe it will require a change in these 3 areas of the code but do not know exacly how to change them;
strData As String
strData = "Search This"
Set rngSearch = rngDB.Find(strData

[mdmackillop]

This will prompt you for a search term.

strData = InputBox("Enter search string")

[Meatball]

I do not want to search for anything specific. If there is anything at all in any of the cells in the Range then do this.........

Dim _
rngDB As Range, _
rngSearch As Range, _
rLast As Long, _
strData As Value

strData = '''''anything at all in Range("g18:G2000")'''''''''<== change to suit
With ActiveSheet

rLast = .Cells(.Rows.Count, "F").End(xlUp).Row
Set rngDB = .Range("F18:G" & rLast)
Set rngSearch = rngDB.Find(strData)

If Not rngSearch Is Nothing Then
.Range("G5") = "IN EXACTA"
Else
MsgBox """" & """" & " not found on given range"
End If
End With
End Sub

[mdmackillop]

Don't use a find, just loop through the cells and check for length.

[Meatball]

Thread is solved. Final code is
Sub ExLibrary()

Dim _
RangeToCheck As Range, _
CellInRangeToCheck As Range

Set RangeToCheck = Range("G18:G2000")

For Each CellInRangeToCheck In RangeToCheck

If (CellInRangeToCheck.Value <> "") Then
Range("G5").Select
Selection = "IN EXACTA"
Range("G6").Select
Selection = "LIBRARY AS"


Exit For
End If

Next
End Sub

Thanks to all for the help.

[mdmackillop]

Sub ExLibrary()
Dim RangeToCheck As Range
Set RangeToCheck = Range("G18:G2000")
If Application.CountA(RangeToCheck) > 0 Then
Range("G5") = "IN EXACTA"
Range("G6") = "LIBRARY AS"
End If
End Sub

or without code

=IF(COUNTA(G16:G2000)>0,"IN EXACTA","")