If statement based on a Find in excel... 2

[brans]

Can you do an If statement in Excel based on a Search finding certain criteria?

I want it to do one thing if A1 contains Branch and another thing if A1 contains Region...

Range("A1&quot.Select
Cells.Find(What:="branch", After:=Activecell, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Activate
Cells.FindNext(After:=Activecell).Activate
ActiveCell.Offset(0, 50).Select
...

I want to be able to find branch and it run the correct code, then I want it to find region and it run different code (or the same code in a different order).

Is there any way to do this?

 

[ilses]

Could you make this a little more specific?
Do you have one single cell that can contains either the text Branch or the text Region? And is this always the same cell on the same sheet in the workbook?

IS

 

[brans]

Sorry,

The workbook, sheet, and column remain constant, but the row changes. I am doing a search in Column A on "branch", when it is found, a marker is setup in AY, all rows above it are deleted, and code is activated based on that marker. Then branch is found again so the process can start over.

I need it to find BOTH branch and region and set the marker in AY (in this code it won't work to just create a new find).

I hope this helps. Thanks!

 

[Dreamboat]

I don't do code much, but have seen people concatenate to find two values. Such as:

if(a1&b1=branch&region,"do this","do that&quot

Probably lame, but hope it gives you an idea.
techsupportgirl@home.com

http://www.PiersonTech.com

Brainbench MVP for Microsoft Word

 

[ilses]

The following code may help you. Find is not a good idea for your problem, instead a range is determined and the cell values in this range are compared to the criteria for putting a marker in AY or deleting the row. As I don't know what you'd like to use as marker, I just picked a string value.

Sub DeleteUnwantedRows()

Dim lngRows As Long
Dim rngArea As Range
Dim lngCounter As Long

'Find the last row with data in column A
lngRows = Worksheets("Blad1&quot.Cells(65536, 1).End(xlUp).Row

'Set the range to work on to A1:AlngRows
Set rngArea = Worksheets("Blad1&quot.Range(Cells(1, 1), Cells(lngRows, 1))

'Loop through range, from last row to A1
With rngArea
For lngCounter = lngRows To 1 Step -1
'If the uppercase version of the text in the cell
'is not equal to "BRANCH"
'or "REGION"
If UCase(.Cells(lngCounter, 1).Text) <> (&quot;BRANCH&quot Then
If UCase(.Cells(lngCounter, 1).Text) <> (&quot;REGION&quot Then
'Delete the entire row
.Cells(lngCounter, 1).EntireRow.Delete
End If
End If
'Put corresponding markers in cell AYlngRows
If UCase(.Cells(lngCounter, 1).Text) = (&quot;BRANCH&quot Then
.Cells(lngCounter, 51).Value = &quot;BranchMarker&quot;
Else
.Cells(lngCounter, 51).Value = &quot;RegionMarker&quot;
End If
Next lngCounter
End With

End Sub

 

[brans]

Thank you, thank you, thank you. That will work perfectly.

It will also help on a similar project I am doing.

 

[DaleWatson123321]

Thanks also to &quot;ilses&quot; ...gave him another star.

I was about to submit a response, but his was obviously a (much) better solution.

...Dale Watson dwatson@bsi.gov.mb.ca