Ignore case of text in code

[Les Stout]

Hi all, I have a spread sheet that i am copying the entire line if
column 4 has the letters "lc" in, it works great as long as the letter
case is lower. If a user puts the case, in the system that the
spreadsheet gets it's information from, in upper case then my code will
ignore it.
Is there a way to "Ignore the case" that i can use ??
The code i use is below.

ANy help would be much appreciated.

Sub OnlyLcLcParts()
'
Dim todaysDateLong As String
Dim sh1 As Worksheet, sh2 As Worksheet
Dim rng1 As Range, cell As Range
Dim rw As Long, Gpls As Date, tDay As Date
Dim res As Variant, dDiff As Long
' Range("A1").Select
Set sh1 = Worksheets("ALL LC PARTS")
Set sh2 = Worksheets("QUALITY PARTS")
rw = 2
Set rng1 = sh1.Range(sh1.Cells(3, 1), sh1.Cells(3, 1).End(xlDown))
For Each cell In rng1
If Application.CountIf(rng1, cell.Value) = 0 Then

Else
If cell.Offset(0, 4) = "lc" Then '*** Data ****
cell.EntireRow.Copy sh2.Cells(rw, 1)
rw = rw + 1
End If
End If
Next
' PctDone = Counter + 0.35 '---1
' Call UpdateProgress1(PctDone)
ColorWhenValueChangeRepLc
End Sub


Best regards,

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

 

[Chip Pearson]

You can change ALL text comparisons in a module to ignore case by placing
Option Compare Text as the first line in the module, right up there with
Option Explicit. For a single conversions, use

change
If cell.Offset(0, 4) = "lc" Then '*** Data ****
to
If StrComp(Cell.Offset(0,4),"lc",vbTextCompare) = 0 Then "***Data***"


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[paul.robinson]

Hi all, I have a spread sheet that i am copying the entire line if
column 4 has the letters "lc" in, it works great as long as the letter
case is lower. If a user puts the case, in the system that the
spreadsheet gets it's information from, in upper case then my code will
ignore it.
Is there a way to "Ignore the case" that i can use ??
The code i use is below.

ANy help would be much appreciated.

Sub OnlyLcLcParts()
'
Dim todaysDateLong As String
Dim sh1 As Worksheet, sh2 As Worksheet
Dim rng1 As Range, cell As Range
Dim rw As Long, Gpls As Date, tDay As Date
Dim res As Variant, dDiff As Long
' Range("A1").Select
Set sh1 = Worksheets("ALL LC PARTS")
Set sh2 = Worksheets("QUALITY PARTS")
rw = 2
Set rng1 = sh1.Range(sh1.Cells(3, 1), sh1.Cells(3, 1).End(xlDown))
For Each cell In rng1
If Application.CountIf(rng1, cell.Value) = 0 Then

Else
If cell.Offset(0, 4) = "lc" Then '*** Data ****
cell.EntireRow.Copy sh2.Cells(rw, 1)
rw = rw + 1
End If
End If
Next
' PctDone = Counter + 0.35 '---1
' Call UpdateProgress1(PctDone)
ColorWhenValueChangeRepLc
End Sub

Best regards,

Les Stout

*** Sent via Developersdexhttp://www.developersdex.com***

Hi
Replace
cell.Offset(0, 4) = "lc"
with
lcase(cell.Offset(0, 4)) = "lc"

regards
Paul

 

[Les Stout]

Thanks so much Chip, much appreciated...

Best regards,

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

 

[Les Stout]

Thanks for your reply Paul, much appreciated.

Best regards,

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***