Adding userform textbox to last row + 1 of named range

[WayneAli]

Hi all,

Being new to VBA I have spent the last few days trying to name 43 columns of my spreadsheet as ranges then adding data to the lastrow.

I nearly posted yesterday looking for the entire code, but with a fresh head today I decided to have another stab, utilising a "Teach VBA App" on the phone and good old google.

The spreadsheet combines with a Userform and is basically for keeping my financial accounts on, the idea is that I can open up the userform, choose from a couple of comboboxes where I will be presented with a selection of textboxes that I can add data to, ie dates, figures, receipt numbers, etc and with the click of an "Add" button the data in the textboxes is transferred into the lastrow of the corresponding named column range of the sheet.

So far I have managed to put together the VBA to name each column (range) according to the contents of the cell at row 1, the accounts data actually starts at row 10 and column "A" is the only column that always has data (Date of transaction):

Private Sub CommandButton1_Click()
Dim ws As Worksheet, i As Integer, lastrow As Long
Set ws = ThisWorkbook.Sheets("Accounts")
i = 1
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
Do While ws.Cells(1, i).Value <> ""
If ws.Cells(1, i).Value <> "" Then
Range(Cells(9, i), Cells(lastrow + 1, i)).Select
ws.Names.Add Name:=ws.Cells(1, i).Value, RefersTo:=Selection
End If
i = i + 1
Loop

End Sub

This works perfectly (although being a VBA newbie, I'm not sure if it's the best method), coincidentally, the reason for wanting named columns is because if I ever add in a column to the spreadsheet the data from the Userform will still go to its' respective column. I did originally have each UserForm TextBox coded to enter into a column, but found that if I added a column, it would all need rewriting.

Anyway, as mentioned, the column/range naming has gone to plan, the problem I am having now is getting the data from the Userform over and into the lastrow of its range. I am currently testing with a button on the sheet as opposed to using the UserForm, this following code goes in at the end of the sub above.

..... i + 1
Loop

ws.Range("Date").Cells(lastrow).Value = "Test Date"
End Sub

This places "Test Date" into the Range.("Date") column, but to far down the sheet, currently Row 17, clicking the button again places "Test Date" in row 25, these entries should be in rows 10 and 11. I think the error is in the "lastrow" code, specifically Cells(Rows.Count, "A"), which at first entry is counting 8 rows, this is then repeating itself every time an entry is made, but I'm not sure.

Any help on this issue would be hugely appreciated, I've thought about tweaking it in places but after the last few days of headache - this is the closest I've got and don't want to mess it up. Also, if there are any suggestions on tidying up what I currently have or even if someone a little more knowledgeable than me has 10 minutes to throw together a dozen or so lines of code that do the above but better, that would be fantastic.

Thanks all in advance, Wayne

 

[WayneAli]

Ok, so after a few more hours of trying every which way to add to a next row, I've tried "+ 1" in various places, then offsetting rows to give me the next blank row in the range, but the result is always the same - the next entry always appeared 8 rows below the last.

Subsequently, after a coffee and time with the kids, I thought if the next entry wants to place itself 8 rows below the last, why not throw a "- 7" into the mix.

So I've ended up with this:

Private Sub CommandButton1_Click()
Dim ws As Worksheet, i As Integer, lastrow As Long
Set ws = ThisWorkbook.Sheets("Accounts")
i = 1
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
    Do While ws.Cells(1, i).Value <> ""            'If statement has been removed (Not sure why I put it in there)
        Range(Cells(9, i), Cells(lastrow + 1, i)).Select
        ws.Names.Add Name:=ws.Cells(1, i).Value, RefersTo:=Selection
    i = i + 1
    Loop

    ws.Range("Date").Cells(lastrow - 7, 1).Select       'This Works with .Cells(lastrow - 7, 1)....!
        Selection = "TestDate"
    ws.Range("Stationary")(lastrow - 7).Value = "TestStationary"      'And so does this with (lastrow - 7)!

End Sub

The reason for throwing in "ws.Range("Date").Cells(lastrow - 7, 1).Select" and "Selection = "TestDate"" was simply to return the sheet back to column A. Because the Loop above uses Select to name each range the process was ending with the last column range in view (Column AQ).

Not perfect, but it gets me moving forward again.
If anyone wants to pick this to pieces and tidy it up, alternatively, throw it away and rewrite it for me I will be more than happy to listen to your help & advice - but for now I've had enough of trying to work out how to name a column and add a row.

Many thanks, I look forward to reading your thoughts and any advice on my somewhat novice VBA attempt.
W

 

[WayneAli]

OK, One more edit - a few more bits chopped, then I'm closing down excel for a while....

Private Sub CommandButton1_Click()
Dim ws As Worksheet, i As Integer, lastrow As Long
Set ws = ThisWorkbook.Sheets("Accounts")
i = 1
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
    Do While ws.Cells(1, i).Value <> ""
    ws.Names.Add Name:=ws.Cells(1, i).Value, RefersTo:=Range(Cells(9, i), Cells(lastrow + 1, i))
    i = i + 1
    Loop

    ws.Range("Date").Cells(lastrow - 7, 1).Value = "HelpDate1"   'This Works!
    ws.Range("Stationary")(lastrow - 7).Value = "PensTest1"  'And so does this!

End Sub