I'm not sure of the correct VBA wording

Mav93

Board Regular
Joined
Feb 21, 2005
Messages
107
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!!!!!!
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Here is an example:

Code:
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

Well-known Member
Joined
Oct 3, 2005
Messages
1,569
Good afternoon

How about :

Code:
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
 

Forum statistics

Threads
1,137,328
Messages
5,680,848
Members
419,935
Latest member
analyst0503

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top