I want to select a cell that contain a specific word in it: I have
written as follow:
Sub Test()

Cells(2, 4) = 0
For i = 1 To 1000

If Cells(i, 1).Contain "SAFEWAY" Then
Cells(2, 4) = Cells(2, 4) - Cells(i, 2)
End If

Next i

End Sub

But it does't work
could you please make it correct for me.
Thanks
Ali

This should do it:

Sub Test()

Cells(2, 4) = 0
For i = 1 To 1000
If InStr(1, Cells(i, 1), "SAFEWAY", vbTextCompare) Then
Cells(2, 4) = Cells(2, 4) - Cells(i, 2)
End If
Next i
End Sub

Regards,
Per

On 16 Jun., 02:27, Ali wrote:
I want to select a cell that contain a specific word in it: I have
written as follow:
Sub Test()

Cells(2, 4) = 0
For i = 1 To 1000

If Cells(i, 1).Contain "SAFEWAY" Then
Cells(2, 4) = Cells(2, 4) - Cells(i, 2)
End If

Next i

End Sub

*But it does't work
could you please make it correct for me.
Thanks
Ali

Per Jessen explained :
This should do it:

Sub Test()

Cells(2, 4) = 0
For i = 1 To 1000
If InStr(1, Cells(i, 1), "SAFEWAY", vbTextCompare) Then
Cells(2, 4) = Cells(2, 4) - Cells(i, 2)
End If
Next i
End Sub

Regards,
Per

Another way:

Sub Test()
Dim i As Integer
Cells(2, 4) = 0
For i = 1 To 1000
If (InStr(Cells(i, 1), "SAFEWAY") 0) Then _
Cells(2, 4) = Cells(2, 4) - Cells(i, 2)
Next i
End Sub

regards,

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On Jun 15, 11:54*pm, GS wrote:
Per Jessen explained :

This should do it:

Sub Test()

Cells(2, 4) = 0
For i = 1 To 1000
If InStr(1, Cells(i, 1), "SAFEWAY", vbTextCompare) Then
* * Cells(2, 4) = Cells(2, 4) - Cells(i, 2)
End If
Next i
End Sub

Regards,
Per

Another way:

* Sub Test()
* * Dim i As Integer
* * Cells(2, 4) = 0
* * For i = 1 To 1000
* * * If (InStr(Cells(i, 1), "SAFEWAY") 0) Then _
* * * * *Cells(2, 4) = Cells(2, 4) - Cells(i, 2)
* * Next i
* End Sub

regards,

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Since you said select, this assumes you have only ONE to find and
select
Sub GotoSafewayinColA_SAS()
Columns("a").Find(What:="safeway", LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Select
End Sub

On Jun 16, 7:24*am, Don Guillett Excel MVP
wrote:
On Jun 15, 11:54*pm, GS wrote:





Per Jessen explained :

This should do it:

Sub Test()

Cells(2, 4) = 0
For i = 1 To 1000
If InStr(1, Cells(i, 1), "SAFEWAY", vbTextCompare) Then
* * Cells(2, 4) = Cells(2, 4) - Cells(i, 2)
End If
Next i
End Sub

Regards,
Per

Another way:

* Sub Test()
* * Dim i As Integer
* * Cells(2, 4) = 0
* * For i = 1 To 1000
* * * If (InStr(Cells(i, 1), "SAFEWAY") 0) Then _
* * * * *Cells(2, 4) = Cells(2, 4) - Cells(i, 2)
* * Next i
* End Sub

regards,

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Since you said select, this assumes you have only ONE to find and
select
Sub GotoSafewayinColA_SAS()
*Columns("a").Find(What:="safeway", LookIn:=xlValues, _
*LookAt:=xlWhole, SearchOrder:=xlByRows, _
*SearchDirection:=xlNext, MatchCase:=False).Select
End Sub- Hide quoted text -

- Show quoted text -

Quicker than a loop. This finds all in col D and gives your cell(2,4)
total
Sub findAllSafeways()
Set c = Columns("d").Find(what:="safeway", _
LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
FirstAddr = c.Address
Do
Cells(2, 4) = Cells(2, 4) - c.Offset(, -2)
Set c = Columns("A").FindNext(after:=c)
Loop While Not c Is Nothing And c.Address < FirstAddr
End If
End Sub

On Jun 16, 7:37*am, Don Guillett Excel MVP
wrote:
On Jun 16, 7:24*am, Don Guillett Excel MVP
wrote:





On Jun 15, 11:54*pm, GS wrote:

Per Jessen explained :

This should do it:

Sub Test()

Cells(2, 4) = 0
For i = 1 To 1000
If InStr(1, Cells(i, 1), "SAFEWAY", vbTextCompare) Then
* * Cells(2, 4) = Cells(2, 4) - Cells(i, 2)
End If
Next i
End Sub

Regards,
Per

Another way:

* Sub Test()
* * Dim i As Integer
* * Cells(2, 4) = 0
* * For i = 1 To 1000
* * * If (InStr(Cells(i, 1), "SAFEWAY") 0) Then _
* * * * *Cells(2, 4) = Cells(2, 4) - Cells(i, 2)
* * Next i
* End Sub

regards,

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Since you said select, this assumes you have only ONE to find and
select
Sub GotoSafewayinColA_SAS()
*Columns("a").Find(What:="safeway", LookIn:=xlValues, _
*LookAt:=xlWhole, SearchOrder:=xlByRows, _
*SearchDirection:=xlNext, MatchCase:=False).Select
End Sub- Hide quoted text -

- Show quoted text -

Quicker than a loop. This finds all in col D and gives your cell(2,4)
total
Sub findAllSafeways()
Set c = Columns("d").Find(what:="safeway", _
LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
FirstAddr = c.Address
Do
Cells(2, 4) = Cells(2, 4) - c.Offset(, -2)
Set c = Columns("A").FindNext(after:=c)
Loop While Not c Is Nothing And c.Address < FirstAddr
End If
End Sub- Hide quoted text -

- Show quoted text -
You don't say if "safeway" is the ONLY word in the cell. If NOT,
change =xlwhole to xlpart

Don Guillett Excel MVP submitted this idea :
On Jun 15, 11:54*pm, GS wrote:
Per Jessen explained :

This should do it:

Sub Test()

Cells(2, 4) = 0
For i = 1 To 1000
If InStr(1, Cells(i, 1), "SAFEWAY", vbTextCompare) Then
* * Cells(2, 4) = Cells(2, 4) - Cells(i, 2)
End If
Next i
End Sub

Regards,
Per

Another way:

* Sub Test()
* * Dim i As Integer
* * Cells(2, 4) = 0
* * For i = 1 To 1000
* * * If (InStr(Cells(i, 1), "SAFEWAY") 0) Then _
* * * * *Cells(2, 4) = Cells(2, 4) - Cells(i, 2)
* * Next i
* End Sub

regards,

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Since you said select, this assumes you have only ONE to find and
select
Sub GotoSafewayinColA_SAS()
Columns("a").Find(What:="safeway", LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Select
End Sub

Well, I think the OP uses the word "select" rather loosely as his
sample code clearly indicates that the intention is to 'find' the text
within a range of cells. Given what the code sample implies is being
done as a result of finding that text, I don't see any point in
"Selecting" anything.

One good point you've demonstrated that has not been address is "case".
To that I offer this revised version of my previous example:

* Sub Test()
* * Dim i As Integer
* * Cells(2, 4) = 0
* * For i = 1 To 1000
* * * If (InStr(UCase$(Cells(i, 1)), "SAFEWAY") 0) Then _
* * * * *Cells(2, 4) = (Cells(2, 4) - Cells(i, 2))
* * Next i
* End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

hello,

another way without loop:
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''
Sub Test()
Dim VisibleCells As Range
Application.ScreenUpdating = False
With ActiveSheet
If .AutoFilterMode Then .Cells.AutoFilter
.Range("A1:A1000").AutoFilter Field:=1, Criteria1:="=*SAFEWAY*"
If InStr(1, .Cells(1, 1), "SAFEWAY", vbTextCompare) Then .Cells(2, 4)
= .Cells(2, 4) - Cells(1, 2)
Set VisibleCells = .Range("B2:B1000").SpecialCells(xlCellTypeVisible)
.Cells(2, 4) = .Cells(2, 4) -
Application.WorksheetFunction.Subtotal(9, VisibleCells)
If .AutoFilterMode Then .Cells.AutoFilter
End With
Application.ScreenUpdating = False
End Sub





"Ali" a écrit dans le message de groupe de
discussion :
...
I want to select a cell that contain a specific word in it: I have
written as follow:
Sub Test()

Cells(2, 4) = 0
For i = 1 To 1000

If Cells(i, 1).Contain "SAFEWAY" Then
Cells(2, 4) = Cells(2, 4) - Cells(i, 2)
End If

Next i

End Sub

But it does't work
could you please make it correct for me.
Thanks
Ali

Another code (shorter):
'''''''''''''''''''''''''''''''''''''''''''''''''' ''
Sub Macro2()
Dim xFilter As Range, xSum As Range
With ActiveSheet
Set xFilter = .Range("A1:A1000")
Set xSum = xFilter.Offset(0, 1)
.Cells(2, 4) = .Cells(2, 4) + _
Application.SumIf(xFilter, "*SAFEWAY*", xSum)
End With
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''' ''




"Ali" a écrit dans le message de groupe de
discussion :
...
I want to select a cell that contain a specific word in it: I have
written as follow:
Sub Test()

Cells(2, 4) = 0
For i = 1 To 1000

If Cells(i, 1).Contain "SAFEWAY" Then
Cells(2, 4) = Cells(2, 4) - Cells(i, 2)
End If

Next i

End Sub

But it does't work
could you please make it correct for me.
Thanks
Ali

Sorry, replace:
.Cells(2, 4) = .Cells(2, 4) + _
Application.SumIf(xFilter, "*SAFEWAY*", xSum)
with
.Cells(2, 4) = .Cells(2, 4) - _
Application.SumIf(xFilter, "*SAFEWAY*", xSum)



"Charabeuh" a écrit dans le message de groupe de
discussion : ...
Another code (shorter):
'''''''''''''''''''''''''''''''''''''''''''''''''' ''
Sub Macro2()
Dim xFilter As Range, xSum As Range
With ActiveSheet
Set xFilter = .Range("A1:A1000")
Set xSum = xFilter.Offset(0, 1)
.Cells(2, 4) = .Cells(2, 4) + _
Application.SumIf(xFilter, "*SAFEWAY*", xSum)
End With
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''' ''




"Ali" a écrit dans le message de groupe de
discussion :
...
I want to select a cell that contain a specific word in it: I have
written as follow:
Sub Test()

Cells(2, 4) = 0
For i = 1 To 1000

If Cells(i, 1).Contain "SAFEWAY" Then
Cells(2, 4) = Cells(2, 4) - Cells(i, 2)
End If

Next i

End Sub

But it does't work
could you please make it correct for me.
Thanks
Ali