Userform query

[palaeontology]

I have a UserForm called ... TouchPadFor6

On the UserForm, I have ...

* a TextBox called .. TextBox1 .. which a user will type in a cell reference ... let's say E8

* a TextBox called .. TextBox3 .. which ...

if there are no timestamps in column N then it would display the same as TextBox1 (in this example .. E8)
if there is one timestamp in column N then it would display the cell location 1 cell lower than what is displayed in TextBox1 (in our example, it would be E9)
if there are two timestamps in column N then it would display the cell location 2 cells lower than what is displayed in TextBox1 (in our example, it would be E10)
etc etc etc

* a CommandButton called ... CommandButton97 ... which, when pressed, needs to do 3 things ...

1) send the 9 values in the range B2:B10 to 9 different cell locations ... given that our TextBox3 is saying E8, then ...

B2 would be sent to E8 .. same cell as indicated in TextBox3
B3 would be sent to F8 .. one cell to the right of what is indicated in TextBox3
B4 would be sent to G8 .. two cells to the right of what is indicated in TextBox3
B5 would be sent to I8 .. four cells to the right of what is indicated in TextBox3
B6 would be sent to J8 .. five cells to the right of what is indicated in TextBox3
B7 would be sent to K8 .. six cells to the right of what is indicated in TextBox3
B8 would be sent to M8 .. eight cells to the right of what is indicated in TextBox3
B9 would be sent to O8 .. ten cells to the right of what is indicated in TextBox3
B10 would be sent to Q8 .. twelve cells to the right of what is indicated in TextBox3

2) clear the contents of cells B2:B10 ready for the next entries

3) send a timestamp to the first available cell in Column N

So, finally to my query.... is someone able to help me with the code needed for both TextBox3, and Commandutton97 ?

Very, very kind regards,

Chris

 

[Norie]

Chris

Are there any values other than timestamps in column N?

Where in column N do the timestamps start?

 

[palaeontology]

Column N will be set up to only receive timestamps, so there will be nothing in it ... upon closing the spreadsheet, I'll be coding to have Column N cleared of all timestamps, ready for the next user to open it and do their entries.

As there will be nothing else in Column N when a user opens the spreadsheet, I suppose timestamps could start in N1.

Kind regards,

Chris

 

[My Aswer Is This]

I do not think you need those two textboxes
Try this script and see if it does what you want,
The script can find the first empty cell in column "N"

Private Sub CommandButton97_Click()
Dim lastrow As Long
lastrow = Cells(Rows.Count, "N").End(xlUp).Row + 1
    For i = 2 To 10
        Cells(lastrow, Choose(i - 1, "E", "F", "G", "I", "J", "K", "M", "O", "Q")).Value = Cells(i, "B").Value
    Next
    Cells(lastrow, "N").Value = Date
Range("B2:B10").ClearContents
End Sub

 

[palaeontology]

That works brilliantly, except, E, F, G, I, J, K, M, O, Q are the columns only when E is the first column of choice, also your code threw the first set of results to E2, F2, G2 etc etc etc, when they should have started at row 8.

I need the TextBox to allow the user to choose which cell they wish the first result to go to ....

if they choose E8, then the results in B2:B10 should be sent to E8, F8, G8, I8, J8, K8, M8, O8, Q8
if they choose M8, then the results in B2:B10 should be sent to W8, X8, Y8, AA8, AB8, AC8, AE8, AG8, AI8
if they choose BR8, then the results in B2:B10 should be sent to BR8, BS8, BT8, BV8, BW8, BX8, BZ8, CB8, CD8
ETC ETC ETC

The user needs to be able to choose any cell to be the first cell in the list of 9 locations

What your code does do correctly is send the second set of results (found in B2:B10 when there's one time-stamp in column N) to the row below the first set
etc etc etc

Any thoughts on how to amend the code ?

Kind regards,

Chris

 

[My Aswer Is This]

So your saying the columns these value go into are not always the same?
And you always want to start in row 8

 

[My Aswer Is This]

I f you want the data to always go in the next row why do you need to enter "E8" into the textbox. Why not just "E"

 

[palaeontology]

You're right ... now that I think of it, it won't always be row 8 (as the starting point) ... the first row would be whatever is seen in TextBox 3, so if they enter M42, then yes, column M and row 42 is the first starting point ... here's an abbreviated excerpt from my first post ...

B2 would be sent to same cell as indicated in TextBox3
B3 would be sent to one cell to the right of what is indicated in TextBox3
B4 would be sent to two cells to the right of what is indicated in TextBox3
B5 would be sent to four cells to the right of what is indicated in TextBox3
B6 would be sent to five cells to the right of what is indicated in TextBox3
B7 would be sent to six cells to the right of what is indicated in TextBox3
B8 would be sent to eight cells to the right of what is indicated in TextBox3
B9 would be sent to ten cells to the right of what is indicated in TextBox3
B10 would be sent to twelve cells to the right of what is indicated in TextBox3

Kind regards,

Chris

 

[My Aswer Is This]

I'm going to let someone else here at Mr. Excel help you.

The columns have no rational order that I can see. And its very hard to sort out things if you want a user to enter E23 into a text box and then sort out what row and what columns to follow. Your columns are not like every other column or every third column.

 

[My Aswer Is This]

And see from a programming point of view here is what we have to do using what you want.

We have to:
Look in the TextBox and determined how many Letter are in the textbox and see how many numbers are in the textbox.

And then we have to use the letters to determine first column and use the numbers to determined what row.

Now I'm sure there is someone else here who could work all that out but I'm not able to do that.