Invoice data also copying empty rows while saving the invoic

July 24, 2017 at 04:27:55
Specs: Windows 10

I AM USING THE FOLLOWING CODE FOR GETTEING MY INVOICE DATA COPY TO AN OTHER SHEET OF EXCEL ON CLICK ON SAVE BUTTON BUT IT IS TAKING EMPTY CELLS AS WELL AND WHEN I PRESS THE VIEW BUTTON AND SELECT ANY PRIVIOUS INVOICE NO. THAN I GET WRONG DATA IN INVOICE FIELDS PLEASE HELP

Private Sub CommandButton1_Click()

Unload UserForm1

End Sub

Private Sub CommandButton2_Click()

Dim RowStart As Long
Dim RowEnd As Long

'Find rows

RowStart = Sheets("Invoice data").Columns("A").Find(ListBox1.Value, _
SearchOrder:=xlRows, LookAt:=xlWhole, SearchDirection:=xlNext, _
LookIn:=xlValues).Row

RowEnd = Sheets("Invoice data").Columns("A").Find(ListBox1.Value, _
SearchOrder:=xlRows, LookAt:=xlWhole, SearchDirection:=xlPrevious, _
LookIn:=xlValues).Row + 1

'Clear range
Sheets("Invoice").Range("B15:L45").ClearContents

'Copy values

Sheets("Invoice").Range("B15").Resize(RowEnd - RowStart, 4).Value = _
Sheets("Invoice data").Range("D" & RowStart & ":L" & RowEnd).Value


Sheets("Invoice").Range("J5").Value = Sheets("Invoice data").Range("B" _
& RowStart).Value
Sheets("Invoice").Range("L3").Value = Sheets("Invoice data").Range("A" _
& RowStart)
Sheets("Invoice").Range("B8").Value = Sheets("Invoice data").Range("C" _
& RowStart)

Sheets("Invoice").Range("C10").Value = Sheets("Invoice data").Range("J" _
& RowStart)
Sheets("Invoice").Range("E10").Value = Sheets("Invoice data").Range("H" _
& RowStart)
Sheets("Invoice").Range("k15").Value = Sheets("Invoice data").Range("K" _
& RowStart)


Unload UserForm1

End Sub

Private Sub CommandButton2_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

End Sub

Private Sub UserForm_Click()

End Sub

Private Sub UserForm_Initialize()

'Populate listbox with unique invoice numbers from sheet "Invoice data"

Dim Test As New Collection
Dim rng As Variant
Dim Value As Variant

'Identify range
rng = Sheets("Invoice data").Range("A2:A" & _
Sheets("Invoice data").Columns("A").Find("*", _
SearchOrder:=xlRows, SearchDirection:=xlPrevious, _
LookIn:=xlValues).Row)

'Filter unique values
On Error Resume Next
For Each Value In rng
If Len(Value) > 0 Then Test.Add Value, CStr(Value)
Next Value
On Error GoTo 0

For Each Value In Test
ListBox1.AddItem Value
Next Value

ListBox1.ListIndex = 0

End Sub


See More: Invoice data also copying empty rows while saving the invoic

Reply ↓  Report •

#1
July 25, 2017 at 08:53:13
First, a couple of posting tips:

1 - Please do not post in All Caps. All Caps is the internet equivalent of yelling and nobody likes to be yelled at.

2 - Please click on the blue line at the end of this post and read the instructions on how to post VBA code in this forum so that it is easier for us to read. Then edit/repost your code so that it looks similar to the example found via that link. Thanks!

As far as your question...

It's a little difficult for us to explain why you are not getting the result that you want because we don't have access to your workbook. If you are not getting any actual VBA errors, then the code is not having any problem processing the instructions as written. In other words, it is doing exactly what you told it to do, even if that isn't what you want it to do.

I don't know what troubling-shooting steps you have tried, but I find that Single Stepping, Break Points and Watches often help me figure out what the VBA is doing since I can watch what happens as each individual instruction is executed.

Perhaps some of the debugging techniques found here would be useful:

https://www.computing.net/howtos/sh...


message edited by DerbyDad03


Reply ↓  Report •
Related Solutions


Ask Question