Prev: convert to capital letters
Next: retrieve data into a spreadsheet from a copy of that spreadsheetwhich is saved as its own xls file
From: Kash on 19 May 2010 04:31
I have the below code to search and copy the rows that matched some
criteria.. I want to add one more search criteria to this code which checks
for matching words..

something like below line..

(Sheets("Details").Range("B" & lngRow) = .Range("G2") Or
Len(.Range("G2").Value) = 0)

But the thing is the word entered may not be the full word

Eg., Range("G2") = "it"

Then it should check for words that contains "it".. Like

profitless
sit out
Italy


my code
--------------------------------
lngLastRow = Sheets("Details").Cells(Rows.Count, "B").End(xlUp).Row
lngNewRow = Sheets("Search").Cells(Rows.Count, "A").End(xlUp).Row + 1
With Sheets("Search")
Select Case UCase(.Range("C2"))

Case "FIRST", "": TheCol = "I": TheCol1 = "J"
Case "SECOND": TheCol = "L": TheCol1 = "M"
Case "FINAL": TheCol = "N"

Case Else 'the default/error option
'TheCol = "J"
MsgBox "Check cell C2 on the Search sheet"
Exit Sub
End Select
For lngRow = 1 To lngLastRow

If (Sheets("Details").Range("H" & lngRow) > Date - 90 Or
Len(.Range("E4").Value) = 0) And _
(Sheets("Details").Range("F" & lngRow) = .Range("E2") Or
Len(.Range("E2").Value) = 0) And _
(Sheets("Details").Range("E" & lngRow) = .Range("E6") Or
Len(.Range("E6").Value) = 0) And _
(Sheets("Details").Range(TheCol & lngRow) = .Range("C4") Or
Len(.Range("C4").Value) = 0) And _
(Sheets("Details").Range(TheCol1 & lngRow) = .Range("C6") Or
Len(.Range("C6").Value) = 0) Then

varTemp = Sheets("Details").Range(lngRow & ":" & lngRow)
Sheets(4).Range(lngNewRow & ":" & lngNewRow) = varTemp
lngNewRow = lngNewRow + 1
End If
Next
End With
From: p45cal on 19 May 2010 08:35

VBA Code:
--------------------



(InStr(1, Sheets("Details").Range("B" & lngRow), .Range("G2"), vbTextCompare) > 0 Or Len(.Range("G2").Value) = 0)

--------------------


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=203737

http://www.thecodecage.com/forumz

 | 
Pages: 1
Prev: convert to capital letters
Next: retrieve data into a spreadsheet from a copy of that spreadsheetwhich is saved as its own xls file