By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,775 Members | 2,658 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,775 IT Pros & Developers. It's quick & easy.

Excel VBA

Sigma7
P: 5
Good afternoon, I am trying to transfer information from my Excel User Form into the database table. After searching vigourously on the internet, I came across this formula to find the next empty row:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdAdd_Click()
  2. Dim iRow As Long
  3. Dim ws As Worksheet
  4. Set ws = Worksheets("DocumentFiled")
  5.  
  6. 'Find First Empty Row in Database
  7. iRow = ws.Cells(Rows.Count, 1).End(x1Up).Offset(1, 0).Row
I have the formula for transfering the information into the table:
Expand|Select|Wrap|Line Numbers
  1. 'Copy the data to the database
  2. ws.Cells(iRow, 1).Value = frmDocument.DateCompleted.Value
  3. ws.Cells(iRow, 2).Value = frmDocument.CaseNumber.Value
  4. ws.Cells(iRow, 3).Value = frmDocument.coboDocTypes.Value
  5. ws.Cells(iRow, 4).Value = frmDocument.DateFiled.Value
  6. ws.Cells(iRow, 5).Value = frmDocument.coboNames.Value
And for emptying the User Form:
Expand|Select|Wrap|Line Numbers
  1. 'Clear the data for next entry
  2. frmDocument.DateCompleted.Value = ""
  3. frmDocument.CaseNumber.Value = ""
  4. frmDocument.coboDocTypes.Value = ""
  5. frmDocument.DateFiled.Value = ""
  6. frmDocument.coboNames.Value = ""
  7.  
  8. End Sub
I cut and paste this formula into my VBA program; however I receive the following error:

Run-Time error '1004'
Application-defined or object-defined error

My question is what am I not seeing in this formula?
Mar 28 '12 #1

✓ answered by NeoPa

First and foremost Sigma, please review Before Posting (VBA or SQL) Code. It seems clear that there are a few problems here that should never be needed to be posted. You'll understand that better after reading the linked article. Don't worry. This is as much for your benefit as ours. There are tips in there that can help you avoid even the need to ask.

If you find that you still have problems after fixing all the items I see and point out, remember to include the line number of where the error occurs. Without this, we obviously will struggle to help you.

First Batch :
  1. Line #4 appears to have a misspelled reference. Probably should say :
    Expand|Select|Wrap|Line Numbers
    1. Set ws = Worksheets("DocumentField")
  2. Line #7 has either a typo or a misread reference. x1Up <> xlUp.

Second Batch :
Nothing drastically or obviously wrong here, but could be abbreviated to :
Expand|Select|Wrap|Line Numbers
  1. 'Copy the data to the database
  2. With frmDocument
  3.     ws.Cells(iRow, 1) = .DateCompleted
  4.     ws.Cells(iRow, 2) = .CaseNumber
  5.     ws.Cells(iRow, 3) = .coboDocTypes
  6.     ws.Cells(iRow, 4) = .DateFiled
  7.     ws.Cells(iRow, 5) = .coboNames
  8. End With
Third Batch :
Not sure where this code came from, but it's not too reliable (because some controls cannot take a string value). Try instead :
Expand|Select|Wrap|Line Numbers
  1. 'Clear the data for next entry
  2. With frmDocument
  3.     .DateCompleted = Null
  4.     .CaseNumber = Null
  5.     .coboDocTypes = Null
  6.     .DateFiled = Null
  7.     .coboNames = Null
  8. End With
  9. End Sub

Share this Question
Share on Google+
4 Replies


NeoPa
Expert Mod 15k+
P: 31,417
First and foremost Sigma, please review Before Posting (VBA or SQL) Code. It seems clear that there are a few problems here that should never be needed to be posted. You'll understand that better after reading the linked article. Don't worry. This is as much for your benefit as ours. There are tips in there that can help you avoid even the need to ask.

If you find that you still have problems after fixing all the items I see and point out, remember to include the line number of where the error occurs. Without this, we obviously will struggle to help you.

First Batch :
  1. Line #4 appears to have a misspelled reference. Probably should say :
    Expand|Select|Wrap|Line Numbers
    1. Set ws = Worksheets("DocumentField")
  2. Line #7 has either a typo or a misread reference. x1Up <> xlUp.

Second Batch :
Nothing drastically or obviously wrong here, but could be abbreviated to :
Expand|Select|Wrap|Line Numbers
  1. 'Copy the data to the database
  2. With frmDocument
  3.     ws.Cells(iRow, 1) = .DateCompleted
  4.     ws.Cells(iRow, 2) = .CaseNumber
  5.     ws.Cells(iRow, 3) = .coboDocTypes
  6.     ws.Cells(iRow, 4) = .DateFiled
  7.     ws.Cells(iRow, 5) = .coboNames
  8. End With
Third Batch :
Not sure where this code came from, but it's not too reliable (because some controls cannot take a string value). Try instead :
Expand|Select|Wrap|Line Numbers
  1. 'Clear the data for next entry
  2. With frmDocument
  3.     .DateCompleted = Null
  4.     .CaseNumber = Null
  5.     .coboDocTypes = Null
  6.     .DateFiled = Null
  7.     .coboNames = Null
  8. End With
  9. End Sub
Mar 28 '12 #2

Sigma7
P: 5
Thank you very much for the quick reply to my question. I will definitely read the link provided. However, line 7 is the culprit. I placed the <> in front of the code and it return a Syntax error. I literally copied this code from a youtube video "create a userform in excel part 3 of 3." Thanks for the other code abbreviations (awesome).
Mar 28 '12 #3

NeoPa
Expert Mod 15k+
P: 31,417
Sigma7:
However, line 7 is the culprit. I placed the <> in front of the code and it return a Syntax error.
I'm not sure I was clear enough. What I was saying is that you used a character '1' (one) in place of the 'l' (ell) which would have been the correct spelling. Maybe you did get it, but I didn't understand your response in that case. FYI: Such typos are easier to spot when the CODE tags are used. Never use a word processor to edit code as this sort of thing (and other similar problems) happens frequently in that situation.

Either way, I'm pleased to have helped :-)
Mar 28 '12 #4

100+
P: 759
Try to understand the following code.
I think that is what you are looking for.
Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2. Dim ws As Worksheet
  3. Dim iRow As Long
  4.  
  5. Private Sub UserForm_Initialize()
  6.     Set ws = Worksheets("DocumentFiled")
  7. 'Find First Empty Row in Database
  8.     iRow = FirstEmptyRow
  9. 'in order to avoid recalculate it each time is pressed "cmdAdd" button
  10. End Sub
  11.  
  12. Private Sub cmdAdd_Click()
  13. 'Copy the data to the database
  14.     With ws
  15.         .Cells(iRow, 1) = CDate(Me.txtDateCompleted)
  16.         .Cells(iRow, 2) = CLng(Me.txtCaseNumber)
  17.         .Cells(iRow, 3) = CStr(Me.cmbDocTypes)
  18.         .Cells(iRow, 4) = CDate(Me.txtDateField)
  19.         .Cells(iRow, 5) = CStr(Me.cmbNames)
  20.     End With
  21.  
  22. 'Prepare for the next record
  23.     'next empty row
  24.     iRow = iRow + 1
  25. 'Clear controls
  26.     With Me
  27.         .txtDateCompleted = Null
  28.         .txtCaseNumber = Null
  29.         .cmbDocTypes = Null
  30.         .txtDateField = Null
  31.         .cmbNames = Null
  32.     End With
  33. End Sub
  34.  
  35. Private Function FirstEmptyRow() As Long
  36. 'Find the last column in database (assuming that columns has headers)
  37. Dim C As Long, Cmax As Long '
  38.     Cmax = 1
  39.     Do Until IsEmpty(ws.Cells(1, Cmax + 1))
  40.         Cmax = Cmax + 1
  41.     Loop
  42. 'Find the really empty row in database (where ALL fields have empty values)
  43.     FirstEmptyRow = 1
  44.     Do
  45.         For C = 1 To Cmax
  46.             If Not IsEmpty(ws.Cells(FirstEmptyRow, C)) Then
  47.     GoTo NextRow
  48.             End If
  49.         Next C
  50.     Exit Do 'First empty row is found
  51. NextRow:
  52.         FirstEmptyRow = FirstEmptyRow + 1
  53.     Loop
  54. End Function

A more elegant solution (and a lot easier too) can be based on .ControlSource property for a control.
Mar 29 '12 #5

Post your reply

Sign in to post your reply or Sign up for a free account.