Finding data and exporting to another file/worksheet from Excel

[merlin63]

Hi all,

I was wondering if there was a way to possibly do the following items in Excel with VBA:

1) With two rows of data in excel (i.e. Column A being an item number and Column B being a quantity), is there a way to say if Column B (the quantity) is equal to zero, then either copy/export etc. the contents to the left of that quantity (i.e. if the quantity in column B3 was zero, then it would refer to A3) would be copied to a text file or possibly another excel sheet?

2) Another thing I worked on was how to do a comparison of items that I have. Let's say I have an item list from today, and one from yesterday. Yesterday's list has some new and missing items compared to today's list. To compare the two, I do this:

My spreadsheet has two columns, one that I put yesterday's item number into (A) and one that I put today's numbers into (B). The third column (C) has (for 3500 rows) the fomula "=VLOOKUP(B2,C:C,1,FALSE)" which will cause the spreadsheet to look in column C for the value located in that particular cell. If it finds the value, it will display that value in the cell. If it does not find that value, it displays "#N/A" in the cell.

I have one or two other formulas to make a virtual "marker" that the number is missing from the other sheet. This also makes it difficult since I have to scroll through the list to see which items no longer exist. Is there an easier way of doing all of this? I'm assuming with VBA, but I cannot find the resources I'm looking for anywhere I look.

If you anyone needs more detail, please let me know.

[sym]

This addresses question #1. As currently written, it needs to be run while the sheet you want to investigate is active.


Public Sub IfQntyZero()

Dim i As Integer
Dim j As Integer
Dim newSheet As Worksheet
Dim invSheet As Worksheet

Set invSheet = ActiveSheet

Set newSheet = ActiveWorkbook.Sheets.Add
newSheet.Name = "Zero Quantitys"

j = 1
invSheet.Activate
Range("A1").Select
For i = 0 To ActiveSheet.UsedRange.Rows.Count

If ActiveCell.Offset(i, 1).Value = 0 Then
newSheet.Range("A" & j).Value = ActiveCell.Offset(i, 0).Value
j = j + 1
End If
Next i

End Sub


[sym]

This addresses Question #2. THis is not great code, but it does work.


Public Sub CompareColumns()
' this sub will compare a list in column A with a list in column B
' it will output any unmatched items to a sheet named "Unmatched Items"

Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim newSheet As Worksheet
Dim curSheet As Worksheet
Dim found As Boolean

Application.ScreenUpdating = False

Set curSheet = ActiveSheet
Set newSheet = ActiveWorkbook.Sheets.Add
newSheet.Name = "Unmatched Items"
newSheet.Range("A1").Value = "Items only in list A"
newSheet.Range("B1").Value = "Items only in list B"

curSheet.Activate
Range("A1").Select

' first, see what is in only the "A" list
k = 2
For i = 0 To curSheet.UsedRange.Rows.Count
found = False
For j = 0 To curSheet.UsedRange.Rows.Count
If ActiveCell.Offset(i, 0).Value = ActiveCell.Offset(j, 1).Value Then
'they matched
found = True
j = curSheet.UsedRange.Rows.Count
End If
Next j
If Not found Then
' if we get here and found is false, it was not matched
newSheet.Range("A" & k).Value = ActiveCell.Offset(i, 0).Value
k = k + 1
End If
Next i

' now, see what is in only the "B" list
k = 2
For i = 0 To curSheet.UsedRange.Rows.Count
found = False
For j = 0 To curSheet.UsedRange.Rows.Count
If ActiveCell.Offset(i, 1).Value = ActiveCell.Offset(j, 0).Value Then
'they matched
found = True
j = curSheet.UsedRange.Rows.Count
End If
Next j
If Not found Then
' if we get here and found is false, it was not matched
newSheet.Range("B" & k).Value = ActiveCell.Offset(i, 1).Value
k = k + 1
End If
Next i

Application.ScreenUpdating = True
End Sub