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

 

[palaeontology]

is there no vba that can do what the formula function 'indirect' does ?

Instead of writing code that tries to send them under one rule, for example .... For i = 2 To 10 ... could they not be coded one at a time, sending B2 to whatever reference is in TextBox 3, sending B3 to one cell to the right of whatever reference is in TextBox3, etc etc etc ?

 

[palaeontology]

oops, I think I meant 'Offset' in vba

 

[Fluff]

Is this what you're after

Private Sub CommandButton1_Click()

    Dim Rng As Range
    
    Set Rng = Range(TextBox3.Value)
    Rng.Value = Range("B2").Value
    Rng.Offset(, 1).Value = Range("B3").Value
    Rng.Offset(, 2).Value = Range("B4").Value
    '
    '
    Rng.Offset(, 12).Value = Range("B10").Value
End Sub

 

[palaeontology]

That is completely brilliant, thank you so much, however, is there a way to add to that code the other three things I need to happen ... so, in order of events, the code should ...

USER CLICKS CommandButton97 FOR THE FIRST TIME ...

1) when Column N has zero time-stamps, apply the Rng.Offset exactly as you (Fluff) have written it ... ie: B2 goes to the cell referenced in TextBox3, B3 goes to the cell beside that, etc etc etc

2) then throw a time-stamp into the first available cell in column N (column N will collect these time-stamps, and at closing of the spreadsheet, Column N will be cleared of time-stamps, ready for the next user to start filling it).

3) clear the contents of B2:B10


USER CLICKS CommandButton97 FOR THE SECOND TIME ...

1) now that Column N has one time-stamp (from the previous button press), send the new B2:B10 values to the row below the row that was sent to when there were zero time-stamps ... ie: if the previous 'step 1' sent the B2:B10 data to V18, W18, X18, Z18, AA18, AB18, AD18, AF18, AH18, then this new set of B2:B10 data should go to V19, W19, X19, Z19, AA19, AB19, AD19, AF19, AH19

2) throw another time-stamp into the first available cell in column N

3) clear the contents of B2:B10 again




USER CLICKS CommandButton97 FOR THE THIRD TIME ...

1) now that Column N has two time-stamps (from the previous two button presses), send the new B2:B10 values to the row below the row that was sent to when there was one time-stamp ... ie: if the previous 'step 1' sent the B2:B10 data to V19, W19, X19, Z19, AA19, AB19, AD19, AF19, AH19, then this new set of B2:B10 data should go to V20, W20, X20, Z20, AA20, AB20, AD20, AF20, AH20

2) throw another time-stamp into the first available cell in column N

3) clear the contents of B2:B10 again



etc etc etc for further clicks of CommandButton97

This is what I've tried so far ... it's entering the time-stamps into Column N correctly, and clearing B2:B10 at the end, but it's not reading the number of time-stamps and therefore it's not sending the new B2:B10 data to the row below the previous 'send' ... instead, it just keeps writing over the original 'send' .. the one that sent the data to, for example, V18, W18, X18, Z18, AA18, AB18, AD18, AF18, AH18

Private Sub CommandButton97_Click()


Dim lastrow As Long
lastrow = Cells(Rows.Count, "N").End(xlUp).Row + 1
    Cells(lastrow, "N").Value = Date


Dim Rng As Range
    
    Set Rng = Range(TextBox3.Value)
    Rng.Value = Range("B2").Value
    Rng.Offset(, 1).Value = Range("B3").Value
    Rng.Offset(, 2).Value = Range("B4").Value
    Rng.Offset(, 4).Value = Range("B5").Value
    Rng.Offset(, 5).Value = Range("B6").Value
    Rng.Offset(, 6).Value = Range("B7").Value
    Rng.Offset(, 8).Value = Range("B8").Value
    Rng.Offset(, 10).Value = Range("B9").Value
    Rng.Offset(, 12).Value = Range("B10").Value
    
Range("B2:B10").ClearContents


End Sub

I apologise if this is confusing, I've tried to make it as clear as I can, but have probably failed.

Very kind regards,

Chris

 

[Fluff]

Try

Private Sub CommandButton97_Click()


    Dim Cnt As Long
    Dim NxtRw As Long
    
    NxtRw = Cells(Rows.Count, "N").End(xlUp).Offset(1)
    Cnt = WorksheetFunction.Count(Range("N2:N" & NxtRw))
    Range("N" & LastRow).Value = Date



Dim Rng As Range
    
    Set Rng = Range(TextBox3.Value)
    Rng.Value = Range("B2").Value
    Rng.Offset(Cnt, 1).Value = Range("B3").Value
    Rng.Offset(Cnt, 2).Value = Range("B4").Value
    Rng.Offset(Cnt, 4).Value = Range("B5").Value
    Rng.Offset(Cnt, 5).Value = Range("B6").Value
    Rng.Offset(Cnt, 6).Value = Range("B7").Value
    Rng.Offset(Cnt, 8).Value = Range("B8").Value
    Rng.Offset(Cnt, 10).Value = Range("B9").Value
    Rng.Offset(Cnt, 12).Value = Range("B10").Value
    
Range("B2:B10").ClearContents


End Sub

 

[palaeontology]

Hi Fluff, thankyou again for all you're doing.

I tried your code, but it's giving an error message on the line ...

    Cnt = WorksheetFunction.Count(Range("N2:N" & NxtRw))

the error message says ...

Run-time error '1004'
Method 'Range' of object '_Global' failed

Kind regards,

Chris

 

[Fluff]

Apologies, couple of mistakes.
Try

    NxtRw = Cells(Rows.Count, "N").End(xlUp).Offset(1).[COLOR=#ff0000]Row[/COLOR]
    Cnt = WorksheetFunction.Count(Range("N2:N" & NxtRw))
    Range("N" & [COLOR=#ff0000]NxtRw[/COLOR]).Value = Date

 

[palaeontology]

Thanks, so much, Fluff ... it's working correctly except for the B2 transfer ... while the other B3:B10 values are correctly transferring, the B2 value is only ever transferring to the cell reference seen in TextBox3, so instead of being transferred to, let's say, V18 on the first click, then V19 on the second and V20 on the third, etc etc, it's only ever going into V18, hence writing over the previous value that had been transferred to V18.

Is there any way to prevent it doing that ?

Very kind regards,

Chris

 

[Fluff]

It should be

Rng.Offset(Cnt).Value = Range("B2").Value

 

[palaeontology]

That did the trick .... thank you so very much, seriously, a huge thankyou for the entire code you put together !!!

You're completely appreciated.

Very kind regards,

Chris