Adding userform data to two different sheets

[rickblunt]

Greetings, I have been using this userform and code for several months without issue, but today I added a couple of textboxes to my userform to pass data to a different sheet; and now I am having issues with the new textboxes. The original code passed the userform data to one sheet -"wafer counts"; but these new boxes are sending data to a different sheet - "Gas Delivery Data". The quirk is that on the first sheet everything was just going into the next blank row so it is very simple. But on the second sheet it is going into cells that are not on the first blank row, there will be data on many of the cells on that same row. So I need to pass the value to the first blank cell in that particular column. I am getting a runtime error on the new code that I added, not sure if I got my syntax wrong or if the way that I did it is totally wrong to begin with. Could anyone offer me any comments on where I am getting this wrong? Thanks - Rick

Private Sub EnterButton1_Click()
    Dim NextRow As Long
    Dim NextEmptyRow As Long
    
'   Make Sure Wafer Counts is Active
    Sheets("Wafer Counts").Activate
    
'   Determine the next empty row
    NextRow = Application.WorksheetFunction. _
        CountA(Range("C:C")) + 2
        
'   Transfer the Date
    Cells(NextRow, 1) = TextDate1.Text
    
'   Transfer the Name
    Cells(NextRow, 2) = TextName1.Text
    
' Transfer all of the userform data
 
    Cells(NextRow, 3) = TextBox1.Text
    Cells(NextRow, 4) = TextBox53.Text
    Cells(NextRow, 5) = TextBox54.Text
    Cells(NextRow, 6) = TextBox55.Text
    Cells(NextRow, 7) = TextBox57.Text
   'a bunch of other userform stuff....
    
   
' Need to add additional information to a different sheet so make sure Gas Delivery Data is active.
    Sheets("Gas Delivery Data").Activate
   
[I]'Here is where I am getting my runtime error.... [/I]
    NextEmptyRow = Sheets("Gas Delivery Data").Cells(Rows.Count, AO).End(xlUp).Offset(1).Row
        Sheets("Gas Delivery Data").Cells(NextEmptyRow, "AO").Value = TextBox105.Text

    NextEmptyRow = Sheets("Gas Delivery Data").Cells(Rows.Count, AQ).End(xlUp).Offset(1).Row
        Sheets("Gas Delivery Data").Cells(NextEmptyRow, "AQ").Value = TextBox106.Text
 
    Unload UserForm2
     
End Sub

 

[Ken Cowen]

Rick

I am not sure what you are trying to do with the AO and AQ, but you are inconsistent with the "". If you are getting the next empty row in columns AO or AQ you will probably be better off with 41 or 43.

NextEmptyRow = Sheets("Gas Delivery Data").Cells(Rows.Count, 41).End(xlUp).Offset(1).Row

I hope this helps.

Ken

 

[rickblunt]

Thanks Ken that got it - I had originally done the numbers, but that didn't work so I went to the letters; but it still didn't work - probably because of those "" that didn't need to be there = doh! Working fine now - thanks for the second set of eyes.