Code not working as I would like

[zakynthos]

Hi,


I’m using the following code in the hope of:

• moving cursor to cell C1 (on input of a ‘1’ to my Q1 on a userform
• moving cursor to cell C201 (on input of a ‘2’ to my Q1 on a userform
• moving cursor to cell C401 (on input of a ‘3’ to my Q1 on a userform
• moving cursor to cell C19801 (on input of a ‘99’ to my Q1 on a userform

(in other words, in steps of 200 from C1 to C19801, on increments of 1 to 99 answers in my Userform)


At present I’m just going to C201 whether I input a 1 or 2 etc




Can you help me sort it?

Many thanks

Dim y As Integer
  Dim ptr As Integer
  Dim myRow As Long
  SHEETS("TEST").Select
  ptr = 0
  Do While ptr <= y
   y = 1
  Range("C" & ptr * 0 + 1).Select
  'clear the data
  Me.txtfindrecipno.Value = ""
   y = 1
  Range("C" & ptr * 0 + 201).Select
  'clear the data
  Me.txtfindrecipno.Value = ""
  Range("C" & ptr * 200 + 1).Select
   ptr = ptr + 1
  Loop

 

[Andrew Poulsom]

I don't see you assigning the value of a control on a UserForm to the variable ptr there.

 

[jbeaucaire]

On the sheet with the C1, I presume it's NOT sheet Test, right-click the sheet tab and insert this event code to watch cell Q1:

Option Explicit

Private Sub worksheet_change(ByVal Target As Range)
Dim MyNum As Long
    
    If Not Intersect(Target, Range("Q1")) Is Nothing Then
        If Target < 1 Or Target > 99 Then Exit Sub
        MyNum = (Target.Value - 1) * 200
        Sheets("TEST").Activate
        Sheets("TEST").Range("C1").Offset(MyNum).Select
    End If

End Sub

 

[zakynthos]

Yes, that works perfectly!!!!

Your help is much appreciated, I've been playng around with the code for the best part of a day to sort it!

Thanks again.

 

[zakynthos]

I tried input the code to watch the ptr on a sheet other than ther TEST sheet and added y 1 to 20 as below, updating the range references.

I thought it was working OK but whichever value of Y I input to Q1 the cursor goes to the last value of Y, in the below example to C3601.

Any ideas?

Dim y As Integer
Dim ptr As Integer
Dim myRow As Long

SHEETS("TEST").Select
'y = 100

ptr = 0

y = 1
y = 2
y = 3
y = 4
y = 5
y = 6
y = 7
y = 8
y = 9
y = 10
y = 11
y = 12
y = 13
y = 14
y = 15
y = 16
y = 17
y = 18
y = 19
y = 20


Do While ptr <= y
  
'select cells

Range("C" & ptr * 0 + 1).Select
Range("C" & ptr * 0 + 201).Select
Range("C" & ptr * 0 + 401).Select
Range("C" & ptr * 0 + 601).Select
Range("C" & ptr * 0 + 601).Select
Range("C" & ptr * 0 + 801).Select
Range("C" & ptr * 0 + 1001).Select
Range("C" & ptr * 0 + 1201).Select
Range("C" & ptr * 0 + 1401).Select
Range("C" & ptr * 0 + 1601).Select
Range("C" & ptr * 0 + 1801).Select
Range("C" & ptr * 0 + 2001).Select
Range("C" & ptr * 0 + 2201).Select
Range("C" & ptr * 0 + 2401).Select
Range("C" & ptr * 0 + 2601).Select
Range("C" & ptr * 0 + 2801).Select
Range("C" & ptr * 0 + 3001).Select
Range("C" & ptr * 0 + 3201).Select
Range("C" & ptr * 0 + 3401).Select
Range("C" & ptr * 0 + 3601).Select

  
 'your code to do whatever with the
  'data at that point
  ptr = ptr + 1
Loop

'clear the data
Me.txtfindrecipno.Value = ""

 

[jbeaucaire]

Your code doesn't seem to do anything I can follow. With words, what are you attempting to do now?

 

[zakynthos]

What I'm trying to do with the code is this:

With an answer '1' to my first question recipient no (in Column A) I want to move to the first occurrence of that number (A1) and then 2 columns across to C1. An answer '2' would find the first '2' in column A in (A201)
then 2 columns across to C201. etc

And that's all I want from the code I've posted!!

The watch on the pointer doesn't seem to work as I add more y's it just goes to the cell for the last y.

So inputting y=1 to the code and answering Q1 with a '1' commenting y=2, y=3 etc does work ie cursor goes to C1.
Uncommenting y=2 and the range as below

Range("C" & ptr * 0 + 201).Select

also works if a '2' is then input into Q1, ie it goes to C201. But then if a 1 is again input it still goes again to C201 (the start of the 2's). If I type a '3' to Q1, after uncommenting y=3 etc then it still goes to C201. Somehow the pointer is not being reset after each answer or there are errors in the structure of the code.

I don't know!

y = 1

Do While ptr <= y

  
'select cells

Range("C" & ptr * 0 + 1).Select
'Range("C" & ptr * 0 + 201).Select

Just to explain what then happens (although I only need an answer to how to modify the part of the code above at the moment) If I can do that, i should be able to sort out the rest.

So to put Q1 into the context:

My second question (in the same type of code as for Q1) is one of 10 more moves down (a 1 moves down 21 to c21, a 2 moves down 20 to c41 etc)

My third question is the input of a no '1' or series of '1's going down from the last cell selected from answer to Q2.

To sum up:

Q1: I want to move down in blocks of 200 from 1 to 19801 with each answer incrementing from 1 to 99

Q2 I want to move down in blocks of 20 from within each answer from 1 incrementing from 1 to 10 in Q1

Q3 I want to input from 1 to 20 x '1's in rows down from the cell selected from answer to Q2


3 examples:

Q 1,2,3, are answered with a 1,1,1 - this should result in a '1' input to C1

Q 1,2,3, are answered with a 1,2,20 - this should result in a '1' input to C21:c41

Q 1,2,3, are answered with a 2,10,20 - this should result in a '1' input to C381:c401

 

[jbeaucaire]

I've read this three times and gotten lost all three times. I'm not connecting with this.

 

[zakynthos]

I really do understand why you don't get it!!! That's why I didn't want to explan too much previously

Ignore everything but this:

For userform Q1:
A '1' (answer) should move cursor to C1 (then reset pointer)
A '2' (answer) should move cursor to C201 (then reset pointer)
A '3' (answer) hould move cursor to C401 (then reset pointer)
then incrementing by 1 from 4 to 99 as above

A '99' (answer) should move cursor to C19801 (then reset pointer)

That's all I want to do, but the pointer doesn't re-set it always goes to the last cell for the last answer number!

Is that simpler?

 

[zakynthos]

I've just sorted it myself - but thanks again for all your help, it allowed me to focus on the issue and resolve it - so much appreciated!