Is there a formula that can find the cell corresponding to a trans

[Guest]

Hey Discussion Group,

Is there a formula that can find a cell reference corresponding to the row
of another column that is undergoing a transition?

For example:

Column A Column B
2
2
2
2
3
3
3
4
4
4 Need macro here

The macro that I am looking for returns a cell reference (ie B4, B7)
corresponding to the row when column A increases by one.

Thanks a lot!

 

[Ken Johnson]

Hi beecher,

This UDF seem to do that...

Public Function INCREMENTROW(SEARCHRANGE As Range, _
INSTANCE As Long) As String
Dim rngCell As Range
Dim K As Long
Dim lInstanceCheck As Long
Dim lCells As Long
lCells = SEARCHRANGE.Cells.Count
For Each rngCell In SEARCHRANGE
K = K + 1
If K = lCells - 1 Then Exit For
If rngCell.Value = rngCell.Offset(1, 0).Value - 1 Then
lInstanceCheck = lInstanceCheck + 1
If lInstanceCheck = INSTANCE Then Exit For
End If
Next rngCell
If lInstanceCheck <> INSTANCE Then Exit Function
INCREMENTROW = rngCell.Address
End Function

Ken Johnson

 

[Guest]

Hey Ken,

Thanks for the help. I am a little unfamiliar with UDF's. Where should I
put this formula? In the visual basics editor? Also, would there happen to
be a macro that I could put straight into the excel spreadsheet to give the
desired results? Thanks so much, Beecher

 

[Ken Johnson]

Hi beecher,

the UDF can be pasted into a standard module in the VBA editor.
When you need to use it you can either find it in the Insert Function
dialog that pops up when you go Insert|Function... (with "All" in the
"Or select a category:" box), or you can simply type "=INCREMENTROW("
then type in the range to search followed by a comma, then the instance
value, then close the arguments bracket and press enter.

If you want to see all instances, one in each cell of a series of
contiguous cells, then you could type this formula into a cell (I have
assumed the range to search is A1 to A100)...

=INCREMENTROW($A$1:$A$100,ROW(A1))

then keep filling that formula down until it results in a blank cell.
This works because filling ROW(A1) down a column increases by 1 each
time.

If ordinary macro is what you prefer then try this...

Public Sub WhereDoesSelectionIncrement()
Dim rngCell As Range
Dim rngSEARCH As Range
Dim K As Long
Dim lInstanceCheck As Long
Dim lCells As Long
Dim strOutput As String
Do
Set rngSEARCH = Application.InputBox( _
prompt:="From one column only, select a range to search.", _
Title:="Search for increment row.", _
Default:=Selection.Address, _
Type:=8)
If rngSEARCH.Columns.Count > 1 Then
MsgBox "Try again!" & vbNewLine _
& "Select cells from one column only."
End If
Loop While rngSEARCH.Columns.Count > 1
For K = 1 To rngSEARCH.Cells.Count - 1
If rngSEARCH.Cells(K).Value = _
rngSEARCH.Cells(K + 1).Value - 1 Then
Let strOutput = strOutput _
& rngSEARCH.Cells(K).Address(False, False) & ", "
End If
Next K
If strOutput = "" Then
Let strOutput = "No increments found."
Else: strOutput = Left(strOutput, Len(strOutput) - 2)
End If
MsgBox strOutput 'If you prefer the output to be in a cell
'then change MsgBox strOutput to something like
'Range("A1").value = strOutput
End Sub

It produces a string containing the addresses, separated by
comma+space, of the cells where incrementing by 1 occurs .

See the comment at the end that will show you how to change the output
to a worksheet cell.

I hope this helps you solve your problem.

Ken Johnson

 

[Ken Johnson]

Hi beecher,

If you decide to use the macro, I've added an improvement that prevents
the error message when the user clicks the Cancel button on the "Search
for increment row." InputBox...


Public Sub WhereDoesSelectionIncrement()
Dim rngCell As Range
Dim rngSEARCH As Range
Dim K As Long
Dim lInstanceCheck As Long
Dim lCells As Long
Dim strOutput As String
Do
On Error GoTo CANCELLED
Set rngSEARCH = Application.InputBox( _
prompt:="From one column only, select a range to search.", _
Title:="Search for increment row.", _
Default:=Selection.Address, _
Type:=8)
If rngSEARCH.Columns.Count > 1 Then
MsgBox "Try again!" & vbNewLine _
& "Select cells from one column only."
End If
On Error GoTo 0
Loop While rngSEARCH.Columns.Count > 1
For K = 1 To rngSEARCH.Cells.Count - 1
If rngSEARCH.Cells(K).Value = _
rngSEARCH.Cells(K + 1).Value - 1 Then
Let strOutput = strOutput _
& rngSEARCH.Cells(K).Address(False, False) & ", "
End If
Next K
If strOutput = "" Then
Let strOutput = "No increments found."
Else: strOutput = Left(strOutput, Len(strOutput) - 2)
End If
MsgBox strOutput 'If you prefer the output to be in a cell
'then change MsgBox strOutput to something like
'Range("A1").value = strOutput
Exit Sub
CANCELLED:
End Sub

Ken Johnson

 

[Guest]

Hey Ken,

Thanks again for the help. I put the macro into visual basics and was able
to compile a sequential list using the formula
=incrementrow($A$1:$A$100,Row(A1). I, however, ran into some problems when
trying to plug in this formula into each column of data. For instance, I'm
using the following spreadsheet:

Column A Column B
2 $A$1
2 $A$1
2 $A$1
2 $A$1
3 $A$5
3 $A$5
3 $A$5
4 $A$8
4 $A$8
4 $A$8

I'm trying to get an address plugged in to each cell of column B that
corresponds with the address of a cell in column A that represents the last
transition. Let me know if these instructions need clarification, thanks
again, beecher

 

[Ken Johnson]

Hey Ken,

Thanks again for the help. I put the macro into visual basics and was able
to compile a sequential list using the formula
=incrementrow($A$1:$A$100,Row(A1). I, however, ran into some problems when
trying to plug in this formula into each column of data. For instance, I'm
using the following spreadsheet:

Column A Column B
2 $A$1
2 $A$1
2 $A$1
2 $A$1
3 $A$5
3 $A$5
3 $A$5
4 $A$8
4 $A$8
4 $A$8

I'm trying to get an address plugged in to each cell of column B that
corresponds with the address of a cell in column A that represents the last
transition. Let me know if these instructions need clarification, thanks
again, beecher

Hi beecher,

Clarification is needed.

Is the example you gave...

Column A Column B
2 $A$1
2 $A$1
2 $A$1
2 $A$1
3 $A$5
3 $A$5
3 $A$5
4 $A$8
4 $A$8
4 $A$8

an example of what you are trying to achieve?

If so, then except for the $A$1s,
=IF(A2=A1+1,ADDRESS(ROW(),COLUMN(A1),B1) in column B, starting at the
row containing the second column A value (can't be in the first row
because it refers to the row above it, and there isn't a row above row
1) then filled down produces the same result. If you want the $A$1's
then type $A$1 in B1 and the rest will be generated by the formula.

Does your column A data always increment by just one?

Ken