I'm not sure of the correct VBA wording

[Mav93]

Hi everybody!!!!!


I have a range of cells D11:D30 and I'm trying to write a piece of code that will let me search throught the range and if the word "Parapet" is located in any of the cells then that cell in that same row but column G will be selected and then macro 1 should be called for.

For example
If "Parapet" is found in Range("D22") then
Range("G22").select
call macro1

Thanks for help in advance!!!!!!

 

[Andrew Poulsom]

Here is an example:

Sub Test()
    Const Str As String = "Parapet"
    Dim Rng As Range
    With Worksheets("Sheet1").Range("D11:D30")
        Set Rng = .Find(What:=Str, After:=.Cells(1, 1), LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False)
        If Not Rng Is Nothing Then
            Rng.Offset(0, 3).Select
        Else
            MsgBox Str & " not found"
        End If
    End With
End Sub

It assumes you want to find it on Sheet1 - change to suit.

Note: If that is not the ActiveSheet:

Rng.Offset(0, 3).Select

will cause an error.

 

[DominicB]

Good afternoon

How about :

Sub test()
Set RangeToCheck = Range("D22:D30")
For Each UsrCell In RangeToCheck
If UsrCell.Value = "Parapet" Then
Range(UsrCell.Address).Offset(, 3).Activate
Call Macro1
End If
Next UsrCell
End Sub

HTH

DominicB