Userform Problem

[atmospheric]

I am trying to create a simple Order Inputting Userform and have two problems that I can't seem to get my head around:

1. I have created a button on the Userform named "Show Uninvoiced". The Invoice Number appears in Column F of the worksheet. Assuming Column A has a date (therefore an order exists in that row), but no Invoice Number, can I produce summary report showing all entries that have not been invoiced?

2. TextBox9 is YTD (Year To Date) Totals. Can I get this box to refer to the spreadsheet and sum and show all the values in Column G?

As usual, your help is greatly appreciated. ray:

 

[Marc]

2. TextBox9 is YTD (Year To Date) Totals. Can I get this box to refer to the spreadsheet and sum and show all the values in Column G?

Here's one way to get the YTD.

Private Sub UserForm_Initialize()

    TextBox9.Value = Application.Sum(Range("G:G"))
    
End Sub

 

[atmospheric]

Excellent, thanks Marc, had to do some minor tweaking to get it into my code, but that works fine.

Any offers on the first part?

 

[Marc]

My idea is to move the data to another sheet and do a sort on the G column producing a list as desired. Here's the code I recorded:

Sub NoInvoiceNumber()

    Cells.Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("A1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.AutoFilter
    Selection.AutoFilter Field:=6, Criteria1:="="
End Sub

 

[TryingToLearn]

Try this (change sheet names to what works for you):

Sub listuninvoiced()
    Dim rw, c
    rw = Range("a65536").End(xlUp).Row
    For Each c In ActiveSheet.Range("f2:f" & rw).Cells
        If Len(c) < 1 Then
            Worksheets("Sheet5").Range("A" & rw & ":e" & rw).Copy _
            Destination:=Sheets("sheet8").Range("a65536").End(xlUp).Offset(1, 0)
        End If
    Next
End Sub

 

[atmospheric]

Thanks guys, I'll try them both and get back to you.

 

[atmospheric]

Tryingtolearn:

I've adapted your code to look like:

Private Sub BtnView_Click()
Dim rw, c
rw = Range("a65536").End(xlUp).Row
For Each c In ActiveSheet.Range("f7:f" & rw).Cells
If Len(c) < 1 Then
Worksheets("Orders").Range("A" & rw & ":e" & rw).Copy _
Destination:=Sheets("Uninvoiced").Range("a65536").End(xlUp).Offset(1, 0)
End If
Next
Sheets("Uninvoiced").Select
Unload Me
End Sub


But it's only picking up the last entry (Which IS invoiced) and copying it 3 times.

(BTW, Invoice No is in my column F)

Any suggestions as to what's wrong?

 

[TryingToLearn]

Sorry, was pulling the row of the final range for each "find". Corrected code is:

Private Sub BtnView_Click()
    Dim rw, c
    rw = Range("a65536").End(xlUp).Row
    For Each c In ActiveSheet.Range("f7:f" & rw).Cells
        If Len(c) < 1 Then
            Worksheets("Orders").Range("A" & c.Row & ":e" & c.Row).Copy _
            Destination:=Sheets("Uninvoiced").Range("a65536").End(xlUp).Offset(1, 0)
        End If
    Next
    Sheets("Uninvoiced").Select
    Unload Me
End Sub

 

[atmospheric]

Thanks TTL, I'll get back to you