1

I have a vba form which inputs the data gathered into one my sheets in my workbook. It is designed (supposed to be) so it will find the last empty row and then input the new information. It worked for the first 60 rows but now it keeps overwriting the existing information in row 60 and will not go further. Any ideas will help because I can't see anything that would stop it at row 60.

    Sub UserForm_Initialize()
ComboBoxWellNameW1.List = Array("4-14-820", "5-56-820", "9-41-820", "10-30-820", "16-31-820", "16-12-820", "16-11-820", "10-42-820", "10-31-820", "10-32-820")
ComboBoxWellStatusW1.List = Array("Pumping", "Shut-in", "Flowing", "Work-over")
ComboBoxTagW1.List = Array("Yes", "No", "N/A")
ComboBoxStrokesW1.List = Array("3", "3.5", "4", "4.5", "5", "5.5", "6", "6.5", "7", "7.5", "8", "8.5", "9")
ComboBoxGauger.List = Array("Jim Burns", "Adam Miller")
TextBoxDate.Value = Date
ComboBoxTime.List = Array("0:00", "0:15", "0:30", "0:45", "1:00", "1:15", "1:30", "1:45", "2:00", "2:15", "2:30", "2:45", "3:00", "3:15", "3:30", "3:45", "4:00", "4:15", "4:30", "4:45", "5:00", "5:15", "5:30", "5:45", "6:00", "6:15", "6:30", "6:45", "7:00", "7:15", "7:30", "7:45", "8:00", "8:15", "8:30", "8:45", "9:00", "9:15", "9:30", "9:45", "10:00", "10:15", "10:30", "10:45", "11:00", "11:15", "11:30", "11:45", "12:00", "12:15", "12:30", "12:45", "13:00", "13:15", "13:30", "13:45", "14:00", "14:15", "14:30", "14:45", "15:00", "15:15", "15:30", "15:45", "16:00", "16:15", "16:30", "16:45", "17:00", "17:15", "17:30", "17:45", "18:00", "18:15", "18:30", "18:45", "19:00", "19:15", "19:30", "19:45", "20:00", "20:15", "20:30", "20:45", "21:00", "21:15", "21:30", "21:45", "22:00", "22:15", "22:30", "22:45", "23:00", "23:15", "23:30", "23:45")
DailyGaugeSheet.Show
End Sub

Sub commandButtonSubmit_Click()
With ThisWorkbook.Sheets("Results")
        .Range("C5000").Select
Selection.End(xlUp).Select
ActiveCell.Offset(0).Select
ActiveCell.Value = TextBoxDate
ActiveCell.Offset(0, 1).Value = ComboBoxTime
ActiveCell.Offset(0, 2).Value = ComboBoxGauger
ActiveCell.Offset(0, 3).Value = ComboBoxWellNameW1
ActiveCell.Offset(0, 4).Value = ComboBoxWellStatusW1
ActiveCell.Offset(0, 5).Value = TextBoxTK1FtW1
ActiveCell.Offset(0, 6).Value = TextBoxTK1InchW1
ActiveCell.Offset(0, 7).Value = TextBoxTK2FtW1
ActiveCell.Offset(0, 8).Value = TextBoxTK2InchW1
ActiveCell.Offset(0, 9).Value = TextBoxTK3FtW1
ActiveCell.Offset(0, 10).Value = TextBoxTK3InchW1
ActiveCell.Offset(0, 11).Value = TextBoxTK4FtW1
ActiveCell.Offset(0, 12).Value = TextBoxTK4InchW1
ActiveCell.Offset(0, 13).Value = TextBoxTK5FtW1
ActiveCell.Offset(0, 14).Value = TextBoxTK5InchW1
ActiveCell.Offset(0, 15).Value = TextBoxTK1WaterHauled
ActiveCell.Offset(0, 16).Value = TextBoxTK2WaterHauled
ActiveCell.Offset(0, 17).Value = TextBoxTK3WaterHauled
ActiveCell.Offset(0, 18).Value = TextBoxTK4WaterHauled
ActiveCell.Offset(0, 19).Value = TextBoxTK5WaterHauled
ActiveCell.Offset(0, 20).Value = TextBoxTK1OilHauled
ActiveCell.Offset(0, 21).Value = TextBoxTK2OilHauled
ActiveCell.Offset(0, 22).Value = TextBoxTK3OilHauled
ActiveCell.Offset(0, 23).Value = TextBoxTK4OilHauled
ActiveCell.Offset(0, 24).Value = TextBoxTK5OilHauled
ActiveCell.Offset(0, 30).Value = TextBoxTubingW1
ActiveCell.Offset(0, 31).Value = TextBoxCasingW1
ActiveCell.Offset(0, 32).Value = ComboBoxTagW1
ActiveCell.Offset(0, 33).Value = ComboBoxStrokesW1
ActiveCell.Offset(0, 34).Value = TextBoxChokeW1
ActiveCell.Offset(0, 37).Value = TextBoxTrTempW1
ActiveCell.Offset(0, 38).Value = TextBoxTrPressW1
ActiveCell.Offset(0, 39).Value = TextBoxStaticPr
ActiveCell.Offset(0, 40).Value = TextBoxDiffPr
ActiveCell.Offset(0, 41).Value = TextBoxYestGasVol
ActiveCell.Offset(0, 42).Value = TextBoxCommentsW1
ActiveCell.Offset(0, 43).Value = TextBoxPumpIntake
ActiveCell.Offset(0, 44).Value = TextBoxPumpTemp
ActiveCell.Offset(0, 45).Value = TextBoxPumpHZ
ActiveCell.Offset(0, 46).Value = TextBoxDownTime
ActiveCell.Offset(0, 47).Value = TextBoxFluidLevel

ComboBoxTime = ""
ComboBoxWellNameW1 = ""
ComboBoxWellStatusW1 = ""
TextBoxTK1FtW1 = ""
TextBoxTK1InchW1 = ""
TextBoxTK2FtW1 = ""
TextBoxTK2InchW1 = ""
TextBoxTK3FtW1 = ""
TextBoxTK3InchW1 = ""
TextBoxTK4FtW1 = ""
TextBoxTK4InchW1 = ""
TextBoxTK5FtW1 = ""
TextBoxTK5InchW1 = ""
TextBoxTK1WaterHauled = ""
TextBoxTK2WaterHauled = ""
TextBoxTK3WaterHauled = ""
TextBoxTK4WaterHauled = ""
TextBoxTK5WaterHauled = ""
TextBoxTK1OilHauled = ""
TextBoxTK2OilHauled = ""
TextBoxTK3OilHauled = ""
TextBoxTK4OilHauled = ""
TextBoxTK5OilHauled = ""
TextBoxTubingW1 = ""
TextBoxCasingW1 = ""
ComboBoxTagW1 = ""
ComboBoxStrokesW1 = ""
TextBoxChokeW1 = ""
TextBoxTrTempW1 = ""
TextBoxTrPressW1 = ""
TextBoxStaticPr = ""
TextBoxDiffPr = ""
TextBoxYestGasVol = ""
TextBoxCommentsW1 = ""
TextBoxPumpIntake = ""
TextBoxPumpTemp = ""
TextBoxPumpHZ = ""
TextBoxDownTime = ""
TextBoxFluidLevel = ""
End With
End Sub
user2043853
  • 13
  • 4
  • You said you're seeing nothing in row 60 that would stop it. Have you checked if by any chance the formatting in C60 is different? Possibly a border, a merged cell, etc and let us know? They are usually skipped by `End(xlUp)` but never hurts to check. Also, lots can be improved with your code, especially the next-empty-row method. – WGS Mar 17 '14 at 19:21

2 Answers2

1

Replace

        .Range("C5000").Select
         Selection.End(xlUp).Select
         ActiveCell.Offset(0).Select

with

        Y = ActiveSheet.UsedRange.Rows.Count
        lastRow = ActiveCell.SpecialCells(xlLastCell).Row
        .Cells(lastRow, "C").Select
Steven Martin
  • 3,150
  • 1
  • 20
  • 27
1

Try changing this:

.Range("C5000").Select
Selection.End(xlUp).Select
ActiveCell.Offset(0).Select

To just this:

.Range("C" & .Rows.Count).End(xlUp).Offset(1,0).Select

The Offset(1,0) is absolutely necessary to get the next empty row. Otherwise, you are just targeting your last row again and again.

Also, quit using ActiveCell and Select. They're honestly crappy methods. Check this post for some ideas: Can I make this macro more efficient or faster? Check #4. It should give you an idea on how to tackle this better.

Let us know if this helps.

Community
  • 1
  • 1
WGS
  • 13,969
  • 4
  • 48
  • 51
  • I will try this and let you know. Thanks. I am also getting an error "Object variable or with block not set". When I debug it shows this error is from my userform.open which is set when I initialize the workbook. – user2043853 Mar 17 '14 at 21:12
  • That seemed to solve it. I must have changed the offset when I was fighting the code to do what I needed. I also looked into your suggestion about not using `ActiveCell` and `Select` and to be honest, I have no idea how to apply it to my code because as a new beginner in VBA it seems over my head. Anyway, Thanks for the help. – user2043853 Mar 18 '14 at 13:32
  • Any thoughts about why I am getting this error "Object variable or With block not set" every time I close my userform "DailyGaugeSheet"? Here is my code for the workbook. `Sub Workbook_Open() DailyGaugeSheet.Show End Sub` – user2043853 Mar 18 '14 at 13:34
  • Kindly accept the answer if it helped you with your problem. As for the second one, it is the main practice here in SO to start a new issue and not hijack an old one, no matter if it was yours. :) Off the top of my head, though, you have some code that messes up the `DailyGaugeSheet`'s close event. – WGS Mar 18 '14 at 14:32
  • I guess I spoke too soon. This did not work. I am getting the following error when I submit this form. "Run-Time error 1004 Select method of Range class failed" `Sub commandButtonSubmit_Click() With ThisWorkbook.Sheets("Results") .Range("C" & .Rows.Count).End(xlUp).Offset(1, 0).Select ActiveCell.Value = TextBoxDate ActiveCell.Offset(0, 1).Value = ComboBoxTime ActiveCell.Offset(0, 2).Value = ComboBoxGauger` – user2043853 Mar 18 '14 at 20:57