Selecting cell next to a value

[KimH]

Hi,

I need to look at a column and search for the cell that
contains "Grand Total" and select the cell that is to the
left of that cell. Is there a way to do this? Thanks

 

[Bob Phillips]

Set oCell = Cells.Fins("Grand Total")
If Not oCell Is Nothing Then
oCelol.Offset(0,-1).Select
End If

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[keepitcool]

This should do:
Sub test()
Dim r As Range
On Error Resume Next
Set r = ActiveSheet.Cells.Find("Grand Total", , xlValues, xlWhole,
xlByRows, xlNext, False).Offset(0, -1)
If Err = 0 Then MsgBox r.Value Else MsgBox "Can't find GrandTotal"
End Sub

keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[papou]

Hi
Please amend accordingly:
With Worksheets(1).Range("a1:C500")
Set c = .Find("Grand Total", LookIn:=xlValues)
If Not c Is Nothing Then
c.Offset(0, -1).Select
End If
End With

HTH
Regards

 

[Ron de Bruin]

Try this for column B

Sub Find_First()
Dim FindString As String
Dim Rng As Range
FindString = "Grand Total"
Set Rng = Range("B:B").Find(What:=FindString, _
After:=Range("B" & Rows.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then Application.Goto Rng.Offset(0, -1), True
End Sub