Finding Text in specific cell

[SS]

I would like to run a Do Loop to look at any row that has
a blank cell in column A. If "A" is blank then it should
look in "E". If any of the text in "E" includes a
specific word then it will set the value in "A" to
somethiing other than blank.

This is what I have tried.


Count = 1

Do While Count < 20
Count = Count + 1

If Worksheets(1).Cells(Count, 1) = "" Then
If Worksheets(1).Cells.Find(What:="Solvent",
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False) = True Then

Worksheets(1).Cells(Count, 1) = "SOLVENTS"

End If


ElseIf Worksheets(1).Cells(Count, 1) <> "" Then
Exit Do
End If
Loop

It does not seem to like my trying to use a Find command
as part of an If statement. Any thoughts on a better way
to do this?

Thanks

SS

 

[Bernie Deitrick]

SS,

Sub Macro1()
Dim myCell As range
For Each myCell In range("A:A").SpecialCells(xlCellTypeBlanks)
If InStr(1, myCell(1, 5).Value, "solvent") Then
myCell.Value = "SOLVENTS"
End If
Next myCell
End Sub

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

SS,

My first post wase case sensitive - for case insentivity, use this

Sub Macro2()
Dim myCell As range
For Each myCell In range("A:A").SpecialCells(xlCellTypeBlanks)
If InStr(1, LCase(myCell(1, 5).Value), "solvent") Then
myCell.Value = "SOLVENTS"
End If
Next myCell
End Sub

HTH,
Bernie
MS Excel MVP

 

[Guest]

Try something like this

Sub Foo(
Dim rng As Rang
' This range will contain cells in ColumnA that are not "blank"
Set rng = Range(Cells(1, 1),
Cells(Rows.Count, 1).End(xlUp)).SpecialCells(xlCellTypeBlanks

Dim cell As Rang
' Now we can loop through each cell in the above range an
' check the E column for a certain string, and if it exist
' then change the value of the cell in the A column
For Each cell In rn
If InStr(1, cell.Offset(0, 4).Value, "searchString") <> 0 The
cell.Value = strSomeOtherValu
End I
Nex
End Su

HTH

-Brad

 

[Tom Ogilvy]

Count = 1

Do While Count < 20
Count = Count + 1

If Worksheets(1).Cells(Count, 1) = "" Then
If Instr(1,Worksheets(1).Cells(count,5)Value, _
"solvent",vbTextCompare) then
Worksheets(1).Cells(Count, 1) = "SOLVENTS"

End If
ElseIf Worksheets(1).Cells(Count, 1) <> "" Then
Exit Do
End If
Loop

As written it will stop after finding the first instance a blank cell in A
with the word solvent in the value of column E. If you want it to check all
20 cells, then remove the Exit Do

 

[JMay]

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 3/25/2004 by James May
'
Columns("A:A").EntireColumn.Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=IF(RC[4]=""solvent"",""solvent"","""")"
End Sub

worked for me..