Return data last row vba user form excel

Question

I'm making some sort of football database where I would input data using a userform and where I want to retrieve data from my excel database.

I have a worksheet named "wedstrijden". This worksheet contain the columns: Date, HomeTeam, AwayTeam, HomeScore,AwayScore, HomeOdds and AwayOdds.

My other worksheet is named "ingevenuitslagen". This worksheet contains my userform called "UitslagenIngeven".

Using the code below I'm able to input my data from the userform to my "wedstrijden" worksheet:

Private Sub putAway_Click()
Dim ingevenuitslagen As Worksheet
Set ingevenuitslagen = ThisWorkbook.Sheets("wedstrijden")
NextRow = ingevenuitslagen.Cells(Rows.Count, 1).End(xlUp).Row + 1
ingevenuitslagen.Cells(NextRow, 1) = CDate(date_txt.Text)
ingevenuitslagen.Cells(NextRow, 2) = UitslagenIngeven.cboHomeTeam
ingevenuitslagen.Cells(NextRow, 3) = UitslagenIngeven.cboAwayTeam
ingevenuitslagen.Cells(NextRow, 4) = UitslagenIngeven.cboHScore
ingevenuitslagen.Cells(NextRow, 5) = UitslagenIngeven.cboAScore
ingevenuitslagen.Cells(NextRow, 6) = Val(UitslagenIngeven.hodds_txt.Text)
ingevenuitslagen.Cells(NextRow, 7) = Val(UitslagenIngeven.aodds_txt.Text)
End Sub

But now I want to return the values of the last row (worksheet "wedstrijden") to my userform using a button called "GetData", but I have no idea how to code that button.

You can get the last row by using SpecialCells `Dim lastRow As Long lastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row` — hmrc87, Nov 10 '15 at 08:42
`Set ingevenuitslagen = ThisWorkbook.Sheets("wedstrijden")` ? Don't you meant `Set ingevenuitslagen = ThisWorkbook.Sheets("ingevenuitslagen")` ? Or `Set wedstrijden = ThisWorkbook.Sheets("wedstrijden")`? It's a bit confusing^^ — R3uK, Nov 10 '15 at 08:43
@MalawiM : I don't think that is problem is to get the last row as he used `NextRow = ingevenuitslagen.Cells(Rows.Count, 1).End(xlUp).Row + 1`. Btw `UsedRange` is not a good method for this kind of issue (finding data), take a look at that : http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba/11169920#11169920 — R3uK, Nov 10 '15 at 08:57
Tnx for the input guys. Can you tell me if it's also possible with vba to put away (or retrieve) 10 rows at once? So if I would make a userform where I have the possibility to enter 10 rows. Would I be able to put away those 10 rows at once? — Gerben69, Nov 10 '15 at 09:15

R3uK · Accepted Answer · 2015-11-10T09:55:27.747

This button will look like a lot with what you already have, something like this :

Private Sub GetData_Click()
Dim wedstrijden As Worksheet
Set wedstrijden = ThisWorkbook.Sheets("wedstrijden")

With wedstrijden
    NextRow = .Cells(.Rows.Count, 1).End(xlUp).Row

    UitslagenIngeven.date_txt.Text = .Cells(NextRow, 1)
    UitslagenIngeven.cboHomeTeam = .Cells(NextRow, 2)
    UitslagenIngeven.cboAwayTeam = .Cells(NextRow, 3)
    UitslagenIngeven.cboHScore = .Cells(NextRow, 4)
    UitslagenIngeven.cboAScore = .Cells(NextRow, 5)
    UitslagenIngeven.hodds_txt.Text = .Cells(NextRow, 6)
    UitslagenIngeven.aodds_txt.Text = .Cells(NextRow, 7)
End With
End Sub

How to work with a Data Array

Code to place in the UserForm to fill the data array :

Public DataA() 'This line should be at the top of the module

'Code to Set the dimension of the Data array
Private Sub UserForm_Initialize()
    '5 is the number of information that you want to store
    Dim DataA(5,0) 
    '----Or
    'Take the number of column of your Data Base
    Dim DataA(ThisWorkbook.Sheets("DB").Range("A1").End(xlToRight).Column + 1,0) 

    'Rest of your code
End Sub

'Code to add a data set to the data array
Private Sub CommandButton1_Click()
    UnFilter_DB 'See below procedure

    DataA(1) = Now()
    DataA(2) = Me.Lb_Data.Caption
    DataA(3) = Me.Lb_Year.Caption
    DataA(4) = Me.Lb_BL.Caption
    DataA(5) = Me.Lb_Country

    ReDim Preserve DataA(Lbound(DataA,1) To Ubound(DataA,1), Lbound(DataA,2) To Ubound(DataA,2)+1)
End Sub

'Code to sent the data array to the DB
Private Sub CommandButton2_Click()
    ReDim Preserve DataA(Lbound(DataA,1) To Ubound(DataA,1), Lbound(DataA,2) To Ubound(DataA,2)-1)

    SetData DataA
End Sub

Procedure to print the data array that you pass from the user form :

Public Sub SetData(ByVal Data_Array As Variant)
Dim DestRg As Range, _
    A()
'Find the last row of your DataBase
Set DestRg = ThisWorkbook.Sheets("DB").Range("Db_Val").Cells(ThisWorkbook.Sheets("DB").Range("Db_Val").Rows.Count, 1)
'Print your array starting on the next row
DestRg.Offset(1, 0).Resize(UBound(Data_Array, 1), UBound(Data_Array, 2)).Value = Data_Array
'Set Increasing ID
ThisWorkbook.Sheets("DB").Cells(Rows.Count, 1).End(xlUp) = ThisWorkbook.Sheets("DB").Cells(Rows.Count, 1).End(xlUp).Offset(-1, 0) + 1
End Sub

Sub to unfilter the DB you are working with (Here it is the Named Range Db_Val in DB sheet)

Public Sub UnFilter_DB()
'Use before "print" array in sheet to unfilter DB to avoid problems (always writing on the same row if it is still filtered)
Dim ActiveS As String, CurrScreenUpdate As Boolean
CurrScreenUpdate = Application.ScreenUpdating
Application.ScreenUpdating = False
ActiveS = ActiveSheet.Name
    Sheets("DB").Activate
    Sheets("DB").Range("A1").Activate
    Sheets("DB").ShowAllData
    DoEvents
    Sheets(ActiveS).Activate
Application.ScreenUpdating = CurrScreenUpdate
End Sub

Is it also possible to have a form where I can, for instance, first put in 10 rows in my user form and when I press the PutAway all of those rows are put away in my databasesheet ("wedstrijden") — Gerben69, Nov 10 '15 at 09:03
Yup, you would have to store them into an array (lets say `A()`) and use something like `.Cells(NextRow,1).Resize(Ubound(A,1),Ubound(A,2)).Value = A` to "print" it into your sheet! ;) — R3uK, Nov 10 '15 at 09:17
Can you give me a code example how you would do this. Because I'm new to vba :) and still learning — Gerben69, Nov 10 '15 at 09:21
@Gerben69 : I edited answer to include what I had, you'll need to tune it a bit but the structure is there! ;) Btw if you could upvote my answer now that you can, it would nice! :) Enjoy SO! ;) — R3uK, Nov 10 '15 at 09:56
thanks for everything. Will try to make it work now :) I upvoted your answer ;) — Gerben69, Nov 10 '15 at 10:01

Return data last row vba user form excel

1 Answers1

How to work with a Data Array