HELP!! Search for condition then report cell row/column

J

jbstarkey

New Member
Joined
Aug 4, 2015
Messages
2
This my first posting, I have used this site dozens of times to find answers, now I NEED HELP!!

I have a series of numbers (angles in degrees) in columns. I need to know the process for searching the column and displaying the results based on my rules below:

Search Column A > When there are 5 consecutive cells of increasing values > Display cell where the sequence BEGAN

AND in a different formula

Search Column C > When there are 3 or more consecutive cells of the same value > Display LAST cell location

Sample Sheet:
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style><style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Shoulder (A1)
[/TD]
[TD]Elbow (B1)[/TD]
[TD]Hip (C1)[/TD]
[TD]Knee (D1)[/TD]
[TD]Hip Rotation (E1)
[/TD]
[/TR]
[TR]
[TD="align: right"]24[/TD]
[TD="align: right"]166[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]174[/TD]
[TD="align: right"]81[/TD]
[/TR]
[TR]
[TD="align: right"]24[/TD]
[TD="align: right"]167[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]174[/TD]
[TD="align: right"]96[/TD]
[/TR]
[TR]
[TD="align: right"]24[/TD]
[TD="align: right"]166[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]174[/TD]
[TD="align: right"]106[/TD]
[/TR]
[TR]
[TD="align: right"]24[/TD]
[TD="align: right"]166[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]174[/TD]
[TD="align: right"]91[/TD]
[/TR]
[TR]
[TD="align: right"]24[/TD]
[TD="align: right"]166[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]174[/TD]
[TD="align: right"]83[/TD]
[/TR]
[TR]
[TD="align: right"]24[/TD]
[TD="align: right"]167[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]174[/TD]
[TD="align: right"]92[/TD]
[/TR]
[TR]
[TD="align: right"]24[/TD]
[TD="align: right"]167[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]174[/TD]
[TD="align: right"]104[/TD]
[/TR]
[TR]
[TD="align: right"]24[/TD]
[TD="align: right"]166[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]174[/TD]
[TD="align: right"]89[/TD]
[/TR]
[TR]
[TD="align: right"]24[/TD]
[TD="align: right"]166[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]174[/TD]
[TD="align: right"]80[/TD]
[/TR]
[TR]
[TD="align: right"]24[/TD]
[TD="align: right"]166[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]174[/TD]
[TD="align: right"]90[/TD]
[/TR]
[TR]
[TD="align: right"]23[/TD]
[TD="align: right"]166[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]174[/TD]
[TD="align: right"]104[/TD]
[/TR]
[TR]
[TD="align: right"]23[/TD]
[TD="align: right"]167[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]173[/TD]
[TD="align: right"]94[/TD]
[/TR]
[TR]
[TD="align: right"]23[/TD]
[TD="align: right"]167[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]172[/TD]
[TD="align: right"]89[/TD]
[/TR]
[TR]
[TD="align: right"]24[/TD]
[TD="align: right"]167[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]171[/TD]
[TD="align: right"]94[/TD]
[/TR]
[TR]
[TD="align: right"]24[/TD]
[TD="align: right"]166[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]171[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]24[/TD]
[TD="align: right"]166[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]168[/TD]
[TD="align: right"]91[/TD]
[/TR]
[TR]
[TD="align: right"]25[/TD]
[TD="align: right"]166[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]165[/TD]
[TD="align: right"]82[/TD]
[/TR]
[TR]
[TD="align: right"]25[/TD]
[TD="align: right"]167[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]164[/TD]
[TD="align: right"]98[/TD]
[/TR]
[TR]
[TD="align: right"]26[/TD]
[TD="align: right"]168[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]155[/TD]
[TD="align: right"]109[/TD]
[/TR]
[TR]
[TD="align: right"]27[/TD]
[TD="align: right"]168[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]149[/TD]
[TD="align: right"]87[/TD]
[/TR]
[TR]
[TD="align: right"]28[/TD]
[TD="align: right"]168[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]138[/TD]
[TD="align: right"]89[/TD]
[/TR]
[TR]
[TD="align: right"]30[/TD]
[TD="align: right"]169[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]127[/TD]
[TD="align: right"]91[/TD]
[/TR]
[TR]
[TD="align: right"]31[/TD]
[TD="align: right"]168[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]136[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]33[/TD]
[TD="align: right"]171[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]136[/TD]
[TD="align: right"]85[/TD]
[/TR]
[TR]
[TD="align: right"]34[/TD]
[TD="align: right"]170[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]137[/TD]
[TD="align: right"]85[/TD]
[/TR]
[TR]
[TD="align: right"]36[/TD]
[TD="align: right"]171[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]127[/TD]
[TD="align: right"]92[/TD]
[/TR]
[TR]
[TD="align: right"]38[/TD]
[TD="align: right"]172[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]133[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]40[/TD]
[TD="align: right"]174[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]140[/TD]
[TD="align: right"]86
[/TD]
[/TR]
</tbody>[/TABLE]

Visually I can spot this location. A20:A24 is the first place where values increase for 5 consecutive cells. So output should be A20

I can also spot the second location. C6:C11 is the first of same values for 3 or more consecutive cells. So output should be it stops at C11

I NEED HELP.

Thanks
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Sort by date Sort by votes
I threw this together and it seems to work OK. Give it a try.
Code: 
Sub t()
Dim sh As Worksheet, lr As Long, a As Long, b As Long, c As Long, d As Long, e As Long
Set sh = ActiveSheet 'Change to sheet name in Sheets("Sheet1") format.
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To lr - 4
        With sh
            a = .Cells(i, 1).Value
            b = .Cells(i + 1, 1).Value
            c = .Cells(i + 2, 1).Value
            d = .Cells(i + 3, 1).Value
            e = .Cells(i + 4, 1).Value
            x = .Cells(i, 3).Value
            y = .Cells(i + 1, 3).Value
            Z = .Cells(i + 2, 3).Value
            If a < e Then
                If b < e And b > a Then
                    If c < e And c > b Then
                        If d < e And d > c Then
                            MsgBox "The progressive sequence begins in cell " & .Cells(i, 1).Address, vbInformation, "PROGRESSIVE SEQUENCE"
                        End If
                    End If
                End If
            End If
            If x = y And x = Z Then
                MsgBox "The last cell with the same value for this sequence is " & .Cells(i + 2, 3).Address, vbInformation, "SAME VALUE SEQUENCE"
            End If
       End With
    Next
End Sub
 
Upvote 0
Wow, that was fast. Thanks.

I will try to implement this. I have never run a code editor in an excel sheet. This would be a first. I will keep the forum in the loop.
 
Upvote 0
jbstarkey said:
Wow, that was fast. Thanks.

I will try to implement this. I have never run a code editor in an excel sheet. .
Click to expand...

this is not event code and should not be installed in the worksheet code module. It should be installed in the standard code module 1. Press Alt + F11 to open the VB editor. If the large pane is dark, then click 'Insert' on the VBE tool/menu bar, then click 'Module'. Once the code is copied to the large pane, close the editor and save the workbook as a Macro Enabled Workbook (.xlsm) to preserve the code when the workbook closes. See the note at the bottom of this post for running the code. Also, if your system has never used macro code before, you might have to adjust your secrutiry settings to allow macros to run. That is a one time setting and is done through the windows controls.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Highlighting cells based on criteria
Replies
0
Views
418
rahildhody
R
C
  • Question Question
Excel Formula to keep records of drawn cards
Replies
3
Views
280
Cquake
C
S
  • Solved
Formula to split date and time and work out energy usage trend
Replies
6
Views
460
Sundance_Kid
S
F
  • Question Question
Sumifs for week of month
Replies
4
Views
323
awoohaw
awoohaw
A
  • Solved
Lookup PAAF ID based on Worker ID and date range
Replies
3
Views
123
Fluff
Fluff

Forum statistics

Threads
1,223,896
Messages
6,175,260
Members
452,627
Latest member
KitkatToby

Share this page

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
Back
Top