Excel UserForm Error

Question

I'm trying to write a code to Insert Project Costs on a Worksheet. I am not an Excel Expert so I am using this tutorial to guide me: https://www.youtube.com/watch?v=NO10WZ2prDQ (it's in Portuguese, but you can see what the UserForm does within the first minute of the video)

What I want to do is insert costs in a worksheet and then filter them to generate a report of all project costs and their categories.

Here's my UserForm: https://i.sstatic.net/pIzwT.png

When coding for the Insert Button ('Inserir Custo'), here's what I typed:

Dim iRow As Long
Dim ws As Worksheet

Set ws = Worksheets("Plan4")

iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

ws.Cells(iRow, 1).Value = Me.ListBox1.Value

ws.Cells(iRow, 2).Value = Me.TextBox1.Value

Me.ListBox1.Value = ""
Me.TextBox1.Value = ""
Me.TextBox1.SetFocus

Excel shows an error on the iRow line of the code, but I dont know whats wrong. When clicking that button, I want the code to insert the results of the ListBox1 in a cell, and the TextBox1 in another cell, next to it.

Can someone help me? What did I write wrong?

Thanks!

Use this to define iRow instead: iRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1 — tigeravatar
– tigeravatar, Commented Sep 6, 2017 at 19:54
You will get that error if there's no content on the "Plan4" worksheet. — Tim Williams
– Tim Williams, Commented Sep 6, 2017 at 19:54
what is the error that you get? what is the name of your worksheet? — jsotola
– jsotola, Commented Sep 6, 2017 at 20:32
@tigeravatar, your method only works if column A always has data when the other cells have data — jsotola
– jsotola, Commented Sep 6, 2017 at 20:41

perfo · Accepted Answer · 2017-09-06 20:26:57Z

1

Private Sub CommandButton1_Click()
Dim iRow As Long
Dim ws As Worksheet
Dim FoundIt As Range


Set ws = Worksheets("Plan4")
Set FoundIt = ws.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues)

If FoundIt Is Nothing Then
  MsgBox ("nothing found")
Else

  iRow = FoundIt.Row + 1
  ws.Cells(iRow, 1).Value = Me.ListBox1.Value
  ws.Cells(iRow, 2).Value = Me.TextBox1.Value
  Me.ListBox1.Value = ""
  Me.TextBox1.Value = ""
  Me.TextBox1.SetFocus
End If

End Sub

The problem is, if the find function doesn't get a match it returns a nothing value and then you try to get the row number from nothing.. My code above simplifies it a bit and only tries to get the row number if it is a valid range.

answered Sep 6, 2017 at 20:26

perfo

4121 gold badge5 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Beatriz Gargaro Over a year ago

Hello, perfo. Thanks for answering. Your code works, but it's not inserting the investment category, only its value. I would like to insert both. If you wanna check my userform style again: i.sstatic.net/pIzwT.png Thanks!

perfo Over a year ago

The original question was about the error in your code. My code fixed that so you should accept my answer as correct. I didn't attempt to alter the functionality of your code , only fix the error. The question you have now should really be posted as a different question however the values in the listbox wont be transferred unless you click on one. Are you just scrolling down the list and not selecting one before pressing the insert button ?

perfo Over a year ago

Thanks. Did clicking on the list item also solve your other problem? If you prefer you can have a list auto select the top item unless someone changes it. YOu can't just scroll up and down and press the insert you need to click on a list item before insert...

jsotola · Accepted Answer · 2017-09-06 20:45:10Z

0

you need to start search at A1, because you are searching in UP direction from there, which rolls over to bottom of sheet and keeps going up until it hits data

iRow = ws.Cells.Find(What:="*", after:=ws.range("A1"), SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

answered Sep 6, 2017 at 20:45

jsotola

2,2691 gold badge15 silver badges29 bronze badges

Comments

Tim Williams · Accepted Answer · 2017-09-07 06:21:42Z

0

Dim rw As Range, f As Range
Dim ws As Worksheet

Set ws = Worksheets("Plan4")
Set f = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
                      SearchDirection:=xlPrevious, LookIn:=xlValues)

If Not f Is Nothing Then 
    Set rw = f.Offset(1,0).EntireRow
Else
    Set rw = ws.Rows(1)
End If

With rw
    .Cells(1).Value = Me.ListBox1.Value
    .Cells(2).Value = Me.TextBox1.Value
End With

Me.ListBox1.Value = ""
Me.TextBox1.Value = ""
Me.TextBox1.SetFocus

answered Sep 7, 2017 at 6:21

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Collectives™ on Stack Overflow

Excel UserForm Error

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related