VBA copy and past userform textbox values then index textbox name

[Birdy01962]

I have a spreadsheet Database that is created by a userform. I have 18 textboxes on userform that are divided into 2 x 9. Each textbox is named H1.....H18. once all 18 textboxes are completed they need to be transferred to 2 different sheets. first 9 (H1 - H9) to "front 9" H10 - 18 to "Back 9". I have the following code which works fine for first 9:- and pastes to "front 9" sheet

For ColNum = H1Col To H9Col
' IndexNum = ColNum - H1Col + 1
' If (Me(IndexNum)) = "" Then
' Me(IndexNum) = 0
' Else
' Cells(RowNumName, ColNum) = CInt(NewScore18(IndexNum))
' End If
'Next ColNum

However using the same code for the back 9

For ColNum = H10Col To H18Col
' IndexNum = ColNum - H1Col + 1
' If (Me(IndexNum)) = "" Then
' Me(IndexNum) = 0
' Else
' Cells(RowNumName, ColNum) = CInt(NewScore18(IndexNum))
' End If
'Next ColNum

always returns H1-9 values not H10 - H18 as needed.
If I replace " = CInt(NewScore18(IndexNum))" above with cell H10 value it does return H10 value but how do I index H10 to H11 value etc that gets pasted into the correct shreadsheet.

I do not understand the CInt instruction in this context.

Any guidance would be appreciated

 

[dmt32]

Hi,
untested but see if this update to your approach will help you

Code should be placed in your userform code page

Dim ws(1 To 2)  As Worksheet
    Dim RowNumName  As Long, i As Long, bx As Long, c As Long
   
    With ThisWorkbook
        Set ws(1) = .Worksheets("Front 9")
        Set ws(2) = .Worksheets("Back 9")
    End With
   
    For i = 1 To 2
        c = 1
       
        With ws(i)
            RowNumName = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
            For bx = 1 + IIf(i = 1, 0, 9) To 9 * i
                .Cells(RowNumName, c).Value = Val(Me.Controls("H" & bx).Value)
                c = c + 1
            Next bx
        End With
       
    Next i

You will need to adjust code to meet specific project need as required

Dave

 

[Birdy01962]

Thanks for your solution. Although not the same as my original code it got me out of the jam I was in.

My first 9 user fields H1 - H9 still use my older code but I've used you code to get the "back9" into the database.