Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 88
  • 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
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.

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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