Efficient way of finding string and changing cell value

  • Thread starter Thread starter ulfb
  • Start date Start date
U

ulfb

As amateur I would appreciate your help:

My sheet has >30 000 rows. In col D I need to find "district", not case
sensitive, beeing part of a longer string. If true, value in col C should be
set to "Dis".

Using VBA, Excel 2003 - how can this be done in shortest possible time?
Filter, Find, Search or ??

Thank you!
 
As amateur I would appreciate your help:

My sheet has >30 000 rows. In col D I need to find "district", not case
sensitive, beeing part of a longer string. If true, value in col C shouldbe
set to "Dis".

Using VBA, Excel 2003 - how can this be done in shortest possible time?
Filter, Find, Search or ??

Thank you!
Click to expand...

In C1, try =IF(ISERROR(SEARCH("district",D1)),"","Dis")
 
Hi,

Try this

Sub Dont_Dis_me()
Dim LastRow As Long
Dim MyRange As Range, C As Range
Set sHt = Sheets("Sheet1") ' change to suit
LastRow = sHt.Cells(Cells.Rows.Count, "D").End(xlUp).Row
Set MyRange = sHt.Range("D1:D" & LastRow)
For Each C In MyRange
If InStr(1, C.Value, "District", vbTextCompare) > 0 Then
C.Offset(, -1).Value = "Dis"
End If
Next
End Sub

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
Thank you, works nicely!
/ulf

Mike H said:
Hi,

Try this

Sub Dont_Dis_me()
Dim LastRow As Long
Dim MyRange As Range, C As Range
Set sHt = Sheets("Sheet1") ' change to suit
LastRow = sHt.Cells(Cells.Rows.Count, "D").End(xlUp).Row
Set MyRange = sHt.Range("D1:D" & LastRow)
For Each C In MyRange
If InStr(1, C.Value, "District", vbTextCompare) > 0 Then
C.Offset(, -1).Value = "Dis"
End If
Next
End Sub

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
Click to expand...
 
Back
Top