[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 89
  • Last Modified:

How to modify my code to not be case sensitive?

The below code searches for the string “BUILDING:", but it will only find it if it’s all in caps. How do I make it not be case sensitive?

strTmp = ""
strTmp = Filter(aLines, "BUILDING:")(0)
Range("X1") = Trim(Split(strTmp, ":")(1)

Open in new window

0
kbay808
Asked:
kbay808
  • 5
  • 2
1 Solution
 
Nick67Commented:
Up at the top of the code module:
It's always good practice to have Option Explicit
Do you have an Option Compare set, too?

This will override it

strTmp = ""
strTmp = Filter(aLines, "BUILDING:",true,vbTextCompare)(0)
Range("X1") = Trim(Split(strTmp, ":")(1)
0
 
kbay808Author Commented:
I’m a novice so you lost me.  Here is my code.
Sub Description_Data()
On Error Resume Next
Range("W1:AK1").ClearContents
Range("W3:AK3").ClearContents
Range("W5:AK5").ClearContents
Range("W7:AK7").ClearContents
Range("W9:AK9").ClearContents
Range("W11:AK11").ClearContents
Range("W13:AK13").ClearContents

aLines = Split(Range("B11"), vbLf)

'Bldg

strTmp = ""
strTmp = Filter(aLines, "BLDG:")(0)
Range("W1") = Trim(Split(strTmp, ":")(1))

strTmp = ""
strTmp = Filter(aLines, "BUILDING:")(0)
Range("X1") = Trim(Split(strTmp, ":")(1))

strTmp = ""
strTmp = Filter(aLines, "BLDG")(0)
Range("Y1") = Trim(Split(strTmp, "G")(1))

strTmp = ""
strTmp = Filter(aLines, "BUILDING")(0)
Range("Z1") = Trim(Split(strTmp, "G")(1))

strTmp = ""
strTmp = Filter(aLines, "BLD")(0)
Range("AA1") = Trim(Split(strTmp, "D")(1))


End Sub

Open in new window

0
 
Nick67Commented:
At the very top of the code put
Option Explicit

This requires you to define all your variables, and keeps the source of many subtle errors at bay
On Error Resume Next
You should use this ONLY as a very last ditch resort and certainly not while you actively developing.
This tells the code to keep going along merrily until it can't, ignoring all problems.
This can be VERY unhappy when you want a series of dependent things to occur.

You'll likely need
DIm aLines() as string
dim strTmp  as string
0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
Nick67Commented:
Try this

Option Explicit
Option Compare Text
Sub Description_Data()
Dim aLines() As String
Dim strTmp  As String
'On Error Resume Next
Range("W1:AK1").ClearContents
Range("W3:AK3").ClearContents
Range("W5:AK5").ClearContents
Range("W7:AK7").ClearContents
Range("W9:AK9").ClearContents
Range("W11:AK11").ClearContents
Range("W13:AK13").ClearContents

aLines = Split(Range("B11"), vbLf)

'Bldg

strTmp = ""
strTmp = Filter(aLines, "BLDG:")(0)
Range("W1") = Trim(Split(strTmp, ":")(1))

strTmp = ""
strTmp = Filter(aLines, "BUILDING:")(0)
Range("X1") = Trim(Split(strTmp, ":")(1))

strTmp = ""
strTmp = Filter(aLines, "BLDG")(0)
Range("Y1") = Trim(Split(strTmp, "G")(1))

strTmp = ""
strTmp = Filter(aLines, "BUILDING")(0)
Range("Z1") = Trim(Split(strTmp, "G")(1))

strTmp = ""
strTmp = Filter(aLines, "BLD")(0)
Range("AA1") = Trim(Split(strTmp, "D")(1))


End Sub

Open in new window

0
 
kbay808Author Commented:
That did the trick.  Thank you very much for your help.
0
 
Nick67Commented:
This would be better, as it is commented
Option Explicit
Option Compare Text
Sub Description_Data()
Dim aLines() As String
Dim strTmp  As String
'On Error Resume Next

'clear cells as required
Range("W1:AK1").ClearContents
Range("W3:AK3").ClearContents
Range("W5:AK5").ClearContents
Range("W7:AK7").ClearContents
Range("W9:AK9").ClearContents
Range("W11:AK11").ClearContents
Range("W13:AK13").ClearContents

'split B11 based on the linefeed character into an array
'usually though its vbCrLF
aLines = Split(Range("B11"), vbLf)

'Bldg

strTmp = ""
'look through the spilt for "BLDG:"
'Put the first instance in strTemp
strTmp = Filter(aLines, "BLDG:", True, vbTextCompare)(0)
'split the result on the colon, hammer the seond bit into the cell
Range("W1") = Trim(Split(strTmp, ":")(1))

'rinse and repeat
strTmp = ""
strTmp = Filter(aLines, "BUILDING:")(0)
Range("X1") = Trim(Split(strTmp, ":")(1))

strTmp = ""
strTmp = Filter(aLines, "BLDG")(0)
Range("Y1") = Trim(Split(strTmp, "G")(1))

strTmp = ""
strTmp = Filter(aLines, "BUILDING")(0)
Range("Z1") = Trim(Split(strTmp, "G")(1))

strTmp = ""
strTmp = Filter(aLines, "BLD")(0)
Range("AA1") = Trim(Split(strTmp, "D")(1))


End Sub

Open in new window

0
 
Nick67Commented:
And I can't test this, but if it is bug-free, this would be best and most maintainable
Option Explicit
Option Compare Text
Sub Description_Data()

'clear cells as required
Range("W1:AK1").ClearContents
Range("W3:AK3").ClearContents
Range("W5:AK5").ClearContents
Range("W7:AK7").ClearContents
Range("W9:AK9").ClearContents
Range("W11:AK11").ClearContents
Range("W13:AK13").ClearContents

Call Pound(Range("B11"), Range("W1"), "BLDG:", ":")
Call Pound(Range("B11"), Range("X1"), "BUILDING:", ":")
Call Pound(Range("B11"), Range("Y1"), "BLDG", "G")
Call Pound(Range("B11"), Range("Z1"), "BUILDING", "G")
Call Pound(Range("B11"), Range("AA1"), "BLD", "D")


End Sub

Sub Pound(RInput As Range, ROutput As Range, TheString As String, SplitChar As String)
Dim aLines() As String
Dim strTmp  As String
'split RInput based on the linefeed character into an array
'usually though its vbCrLF
aLines = Split(RInput, vbLf)
'look through the spilt for TheString
'Put the first instance in strTemp
strTmp = Filter(aLines, TheString, True, vbTextCompare)(0)
'split the result on the final character, hammer the second bit into the cell
ROutput = Trim(Split(strTmp, SplitChar)(1))
End Sub

Open in new window

0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now