Cells(2, Columns.Count).End(1)(1, 2).Value = n |/| difficult to manipulate "for me"

[montecarlo2012]

Hello everyone.
I think I don't need for this question to use XL2BB, if so, please let me know will more than grateful to do it.
with this code:

Sub jumping_v2()
  Dim i As Long, n As Long
  Dim f As Range  
  For i = 2 To Range("B" & Rows.Count).End(3).Row  
    Set f = Range("B" & i).Resize(, 5).Find(1, , xlValues, xlWhole)   
    If Not f Is Nothing Then    
      Cells(2, Columns.Count).End(1)(1, 2).Value = n     
      n = -1
    End If
    n = n + 1
  Next
End Sub

I got this answer:

right beside my array is the answer, I really need my answer on T2
so I tried to change the line for:

Sub jumping_v2()
  Dim i As Long, n As Long
  Dim f As Range
  
  For i = 2 To Range("B" & Rows.Count).End(3).Row
  
    Set f = Range("B" & i).Resize(, 5).Find(1, , xlValues, xlWhole)
   
    If Not f Is Nothing Then
    
            Cells(Rows.Count, "P").End(xlUp).Offset(1, 0).Value = n
      

      n = -1
    End If
    n = n + 1
  Next
End Sub

and this is what I got:

so what I am looking for is for:

dante_skip_April.xls
	B	C	D	E	F
2	5	4	23	25	32
3	4	7	13	31	33
4	10	12	17	26	33
5	5	10	19	33	36
6	10	13	16	21	27
7	7	8	10	24	29
8	8	12	20	27	28
9	1	9	15	31	33
10	5	11	21	25	28
11	2	5	13	25	27
12	9	12	30	32	36
13	3	12	19	22	31
Sheet1

thank you for your understanding and time

 

[DanteAmor]

What I imagined. The number of results reached the limit of excel columns.

I leave you an alternative, when 30 columns are completed it will start in next row:

Sub jumping_v2()
  Dim i As Long, n As Long, col As Long, m As Long, j As Long
  Dim f As Range
  col = Columns("T").Column
  j = 2
  For i = 2 To Range("B" & Rows.Count).End(3).Row
    Set f = Range("B" & i).Resize(, 5).Find(5, , xlValues, xlWhole)
    If Not f Is Nothing Then
      Cells(j, col).Value = n
      n = -1
      col = col + 1
      m = m + 1
      If m = 30 Then   'Fit to the number you want.
        m = 0
        j = j + 1
        col = Columns("T").Column
      End If
    End If
    n = n + 1
  Next
End Sub

The result would be something like this:

I hope it is close to what you are looking for.
Sincerely
Dante Amor

 

[Micron]

I'd say you need to offset the column within the loop but I don't know exactly how because I can't replicate your results with either code and that data. I get only one execution of the value setting line of code. Nor do I understand why you're counting the used rows in P and starting your offset from P. If I offset like
Cells(Rows.Count, "P").End(xlUp).Offset(1, 4).Value = n then I get one value in T. You might get what you're after with something like

x = 4
Cells(Rows.Count, "P").End(xlUp).Offset(0, x).Value = n
x = x +1

 

[DanteAmor]

Hi @montecarlo2012.

In your example I only see a single 1. But testing with the number 5, I find more values, the result is:

Dante Amor
	A	B	C	D	E	F	G												S	T	U	V	W	X
1
2		5	4	23	25	32														0	2	4	0
3		4	7	13	31	33
4		10	12	17	26	33
5		5	10	19	33	36
6		10	13	16	21	27
7		7	8	10	24	29
8		8	12	20	27	28
9		1	9	15	31	33
10		5	11	21	25	28
11		2	5	13	25	27
12		9	12	30	32	36
13		3	12	19	22	31
14
Hoja1

I really need my answer on T2

We need another variable for the column counter, starting at column T:

Sub jumping_v2()
  Dim i As Long, n As Long, col As Long
  Dim f As Range
  col = Columns("T").Column
  For i = 2 To Range("B" & Rows.Count).End(3).Row
    Set f = Range("B" & i).Resize(, 5).Find(5, , xlValues, xlWhole)
    If Not f Is Nothing Then
      Cells(2, col).Value = n
      n = -1
      col = col + 1
    End If
    n = n + 1
  Next
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

 

[montecarlo2012]

Hello Dante, I got run-time error 1004
debug here

 

[DanteAmor]

Hello @montecarlo2012.
Modify something in the macro?
Do you have any cell with error?
Did you copy the entire macro?
As you can see in my post I put the result and I have no errors.
You can try it in a new file. With new data, copy the entire macro and come back here and tell me.

Cordially
Dante Amor

 

[montecarlo2012]

Good morning Dante, and thank you again for your time
1/Modify something in the macro? = No
2/Do you have any cell with error? = I clear everything, clear formatting rules,
3/Did you copy the entire macro? =Yes

So now I am getting results and after again the debug sign show up and highlight this line.
You can try it in a new file|||| happen with another macro, doing that do not address the problem.

Thank you DanteAmor.

 

[DanteAmor]

So now I am getting results and after again the debug sign show up and highlight this line.
You can try it in a new file|||| happen with another macro, doing that do not address the problem.

I don't understand.
It works fine, but at other times it doesn't work?

I have no problem with the macro.
You can share your file to review what problem you have.

You could upload a copy of your file to a free site such www.dropbox.com or google drive. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[montecarlo2012]

Thank you DanteAmor here we go

dante_skip_April.xls

Shared with Dropbox

www.dropbox.com

 

[montecarlo2012]

DanteAmor,
Hello
Thanks for your time

Before we close this thread
the code work, the detail is:

In this way all those results,
don't let me check
the data in the way I need,
because, for example,
the first road I don't know now,
what number is referring to.
would be so helpful,
If you don't mind of course, to:

("for better understanding let me show you the column I add")

Column S.
I know this is not what you see in the dropbox, sorry,
what I was trying to do is using your code like

Set f = Range("B" & i).Resize(, 5).Find(1, , xlValues, xlWhole) display T2
Set f = Range("B" & i).Resize(, 5).Find(2, , xlValues, xlWhole) " T3
Set f = Range("B" & i).Resize(, 5).Find(3, , xlValues, xlWhole) " T4
---
---
until Set f = Range("B" & i).Resize(, 5).Find(36, , xlValues, xlWhole) display T37

I was thinking taking your code and copy and paste the line Set f and change it 36 times.
is a complex matter for me this kind of -->looping manipulation<--

then now, I don't know what value is referring to.
I know I didn't say this, "at the first place", sorry about it.

I never imaging you ask me for the whole work book,
"I am so happy you do that",
thank you.