Find a number i a range
 

Hi,
In sheet "Div" range R6:R36 I have a chain of numbers ( 11123333334455566
etc) , each timea number in the chain change there is a text in the cell on
the right side. In a For.. Next loop I want to pick upp this textstrings (and
use it in Mysheet) but the sub just want find the first one (1).
In the For..Next loop nLag willbe changed from 1 o 6

Sub HamtaKlubb()
klassCol = 17
nLag = 2
Sheets("Div").Activate
ActiveSheet.Range(Cells(6, klassCol), Cells(36,
klassCol)).Find(nLag).Offset(0, 1).Select
MyLag = Selection.Value
Sheets(MySheet).Activate

End Sub
Thanks in advance for your help! /KentAE
--
If you are old enough, you have your future behind you.

Find a number i a range
 

I am not sure what you are looking for. The code below is for
a loop that looks at each value in a column and compares it
to the preceding value for all cells in rows greater than row 6.
If the values are different then it returns the value of the cell
to the right of the first cell in the search col. with a different value.


Sheets("Div").Activate
Set SrcRng = ActiveSheet.Range(Cells(6, klassCol), Cells(36, klassCol))
For Each c In SrcRng
If c.Row 6 Then
If c.Value < C.Offset(-1, 0).Value Then
MsgBox c.Offset(0, 1).Value
End If
End If
Next




"KentAE" wrote:

Hi,
In sheet "Div" range R6:R36 I have a chain of numbers ( 11123333334455566
etc) , each timea number in the chain change there is a text in the cell on
the right side. In a For.. Next loop I want to pick upp this textstrings (and
use it in Mysheet) but the sub just want find the first one (1).
In the For..Next loop nLag willbe changed from 1 o 6

Sub HamtaKlubb()
klassCol = 17
nLag = 2
Sheets("Div").Activate
ActiveSheet.Range(Cells(6, klassCol), Cells(36,
klassCol)).Find(nLag).Offset(0, 1).Select
MyLag = Selection.Value
Sheets(MySheet).Activate

End Sub
Thanks in advance for your help! /KentAE
--
If you are old enough, you have your future behind you.

Find a number i a range
 

It works, thanls / KentAE
--
If you are old enough, you have your future behind you.


"JLGWhiz" skrev:

I am not sure what you are looking for. The code below is for
a loop that looks at each value in a column and compares it
to the preceding value for all cells in rows greater than row 6.
If the values are different then it returns the value of the cell
to the right of the first cell in the search col. with a different value.


Sheets("Div").Activate
Set SrcRng = ActiveSheet.Range(Cells(6, klassCol), Cells(36, klassCol))
For Each c In SrcRng
If c.Row 6 Then
If c.Value < C.Offset(-1, 0).Value Then
MsgBox c.Offset(0, 1).Value
End If
End If
Next




"KentAE" wrote:

Hi,
In sheet "Div" range R6:R36 I have a chain of numbers ( 11123333334455566
etc) , each timea number in the chain change there is a text in the cell on
the right side. In a For.. Next loop I want to pick upp this textstrings (and
use it in Mysheet) but the sub just want find the first one (1).
In the For..Next loop nLag willbe changed from 1 o 6

Sub HamtaKlubb()
klassCol = 17
nLag = 2
Sheets("Div").Activate
ActiveSheet.Range(Cells(6, klassCol), Cells(36,
klassCol)).Find(nLag).Offset(0, 1).Select
MyLag = Selection.Value
Sheets(MySheet).Activate

End Sub
Thanks in advance for your help! /KentAE
--
If you are old enough, you have your future behind you.