CSV Import (Done) - Find Title, Compare and adjust Inventory

[Dennis88]

Dear All,

we are working on an inventory file and would like to compare daily sales, which come from a CSV import (thats alrady working just fine), to our inventory.


The CSV Import looks as follows (Sheetname "Afterbuy Einlesen"):
[TABLE="width: 933"]
<tbody>[TR]
[/TR]
[TR]
[TD][TABLE="width: 933"]
<tbody>[TR]
[TD]Rechnungsnummer[/TD]
[TD]Rechnungsdatum[/TD]
[TD]Amount
[/TD]
[TD]Titel
[/TD]
[TD]Artikelnummer[/TD]
[/TR]
[TR]
[TD]49592[/TD]
[TD]05.01.2016[/TD]
[TD="align: center"]1
[/TD]
[TD]5x MAXELL CR2032 LITHIUM BATTERIEN BATTERY KNOPFZELLE 3V --- OVP --- NEU[/TD]
[TD="align: right"]2,0146E+11[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]


These daily sells are saved into a help sheet, which subsequently shall be compared to our investory and the amount of daily sold items shall be substracted from the inventory.
The inventory data is stored on our "Lager" sheet, which looks as follows:


[TABLE="width: 1737"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Erstellt am[/TD]
[TD]Artikel Nr.[/TD]
[TD]Hersteller Nr.
[/TD]
[TD][/TD]
[TD]Titel
[/TD]
[TD]Hersteller[/TD]
[TD]Inventory
[/TD]
[TD]Status[/TD]
[TD]Bestand min.[/TD]
[TD]Melde-Bestand[/TD]
[TD]Einheit[/TD]
[TD]Lagerort[/TD]
[/TR]
[TR]
[TD]151[/TD]
[TD]05.01.2016[/TD]
[TD]151[/TD]
[TD][/TD]
[TD][/TD]
[TD]5x MAXELL CR2032 LITHIUM BATTERIEN BATTERY KNOPFZELLE 3V
[/TD]
[TD][/TD]
[TD]10[/TD]
[TD][/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD]Lager 1[/TD]
[/TR]
</tbody>[/TABLE]


In such a case the code shall first find the correct line item in our inventory sheet (5x MAXCELL CR2032) and then just the sold amount from our imported CSV Sheet explained above. In that case -1


Our current approach is to work via a VLOOKUP and row identifier, however we think it would be easier to save the String and identify the row directly.

Every idea or approach is highly appreciated.

Thanks!

 

[Dennis88]

This is the CSV Import Code that we are using at the moment (the for loop marks the framework for the code)

Sub Einlesen()

  Dim strFileName As String, arrDaten, arrTmp, lngR As Long, lngLast As Long
  Const cstrDelim As String = "," 'Trennzeichen
  
  With Application.FileDialog(msoFileDialogFilePicker)
    .AllowMultiSelect = False
    .Title = "Datei wählen"
    .InitialFileName = "d:\HBZ\Energieausweise\Berechnungsgrundlagen\*.csv"  'Pfad anpassen
    .Filters.Add "CSV-Dateien", "*.csv", 1
    If .Show = -1 Then
      strFileName = .SelectedItems(1)
    End If
  End With
  
  Sheets("Afterbuy Einlesen").Select

'Select Afterbuy File
    
If strFileName <> "" Then
    Application.ScreenUpdating = False
    Open strFileName For Input As #1
    arrDaten = Split(Input(LOF(1), 1), vbCrLf)
    Close #1
    For lngR = 0 To UBound(arrDaten)
      arrTmp = Split(arrDaten(lngR), cstrDelim)
      If UBound(arrTmp) > -1 Then
                With ActiveSheet
                        lngLast = .Cells(Rows.Count, 1).End(xlUp).Row + 1
                        lngLast = Application.Max(lngLast, 0)
                        .Cells(lngLast, 1).Resize(, UBound(arrTmp) + 1) _
                          = Application.Transpose(Application.Transpose(arrTmp))
                        .Cells(lngLast, 11) = Mid(strFileName, InStrRev(strFileName, "\") + 1)
               End With
        
      End If
    Next lngR
  End If
  
' Search and replace ("")
  
    Range("A1:AL2500").Select
    Selection.Replace What:="""", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False
    
' Sear for sold items and adjust value in warehouse
 
 For i = 0 To lngLast
 
 
 
 
 Next i
 
 
End Sub

The explained part above is missing and shall be added at the bottom.

Thanks!

 

[mole999]

Cross Posted Here > http://www.mrexcel.com/forum/questi...rt-done-abgleich-titel-menge-und-bestand.html

 

[Dennis88]

Would it be possible to solve this problem by using VLOOKUPs only?

Thanks!