Sub Prisliste_Overfør_Varer_Klik()
Application.ScreenUpdating = False
SetVar
Dim col As New Collection
Dim Varelinje As New ClVarelinjer
Dim vElement
Dim Cell As Range, iCell As Range
For Each Cell In rPris
If Cell.Offset(0, 2) <> "" Then
With Varelinje
.Vare_nr = Cell.Value
.Navn = Cell.Offset(0, 1).Value
.Antal = Cell.Offset(0, 2).Value
.Enhed = Cell.Offset(0, 4).Value
.Pris = Cell.Offset(0, 5).Value
.Bemærkning = Cell.Offset(0, 10).Value
End With
Else
GoTo Videre
End If
For Each iCell In rBestil
With Varelinje
If iCell.Value = .Vare_nr Then
iCell.Value = .Vare_nr
iCell.Offset(0, 1).Value = .Navn
iCell.Offset(0, 2).Value = .Antal
iCell.Offset(0, 4).Value = .Enhed
iCell.Offset(0, 5).Value = .Pris
iCell.Offset(0, 5).NumberFormat = "$ #,##0.00"
iCell.Offset(0, 6).Value = .Bemærkning
iCell.Offset(0, 7).FormulaR1C1 = "=IFERROR(RC[-5]*RC[-2],"""")"
iCell.Offset(0, 7).NumberFormat = "$ #,##0.00"
GoTo Videre
ElseIf iCell.Value = "" Then
iCell.Value = .Vare_nr
iCell.Offset(0, 1).Value = .Navn
iCell.Offset(0, 2).Value = .Antal
iCell.Offset(0, 4).Value = .Enhed
iCell.Offset(0, 5).Value = .Pris
iCell.Offset(0, 5).NumberFormat = "$ #,##0.00"
iCell.Offset(0, 6).Value = .Bemærkning
iCell.Offset(0, 7).FormulaR1C1 = "=IFERROR(RC[-5]*RC[-2],"""")"
iCell.Offset(0, 7).NumberFormat = "$ #,##0.00"
GoTo Videre
End If
End With
Next
Videre:
Set Varelinje = New ClVarelinjer
Next Cell
Cbox
'renser antal og bemærkning i prislisten
ClearOmråde WsPris.Range("C9", WsPris.Range("C6000").End(xlUp))
ClearOmråde WsPris.Range("K9", WsPris.Range("K6000").End(xlUp))
'
Slet_række
' sorterer
Sorter WsBestil.Range("A9", WsBestil.Range("H6000").End(xlUp)), WsBestil.Range("B9", WsBestil.Range("B6000").End(xlUp))
WsPris.Range("a1").Value = Now()
' sætter kanter
IngenKanter WsBestil, WsBestil.Range("a9", WsBestil.Range("H6000"))
Kanter WsBestil, WsBestil.Range("a9", WsBestil.Range("H6000").End(xlUp))
WsPris.Activate
Application.ScreenUpdating = True
[COLOR=rgb(184, 49, 47)] ComboBox1.SetFocus[/COLOR]
End Sub
Private Sub Cbox()
Dim fCbox As ComboBox
Set fCbox = ComboBox1
fCbox.Value = ""
End Sub