Select Range

  • Thread starter Thread starter Ali
  • Start date Start date
A

Ali

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
 
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
Click to expand...

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,
 
Per Jessen explained :





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
Click to expand...

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
 
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 -
Click to expand...

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
 
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 -
Click to expand...
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 :
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
Click to expand...

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
 
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" <[email protected]> a écrit dans le message de groupe de
discussion :
(e-mail address removed)...
 
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" <[email protected]> a écrit dans le message de groupe de
discussion :
(e-mail address removed)...
 
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)
Click to expand...
 
Back
Top