Efficient way of finding string and changing cell value
 

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!

Efficient way of finding string and changing cell value
 

On Jan 24, 10:36*pm, ulfb wrote:
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!

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

Efficient way of finding string and changing cell value
 

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.


"ulfb" wrote:

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!

Efficient way of finding string and changing cell value
 

Thank you, works nicely!
/ulf

"Mike H" wrote:

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.


"ulfb" wrote:

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!