17. august 2017 - 09:16Der er
28 kommentarer og 3 løsninger
VBA - Makroindspilning
Jeg vil gerne indspille en makro, der kan lave en konverteringsopgave, men ved ikke hvordan man gør:
Ark 1: Kolonne A: her indsættes manuelt en række konkurrentvarenumre Kolonne B: skal vise leverandørnavn Kolonne C: skal vise vores varenummer, alternativ A Kolonne D: skal vise vores varetekst, alternativ A Kolonne E: skal vise vores varenummer, alternativ B Kolonne F: skal vise vores varetekst, alternativ B Kolonne G: skal vise varegruppe
Ark 2 indeholder: Kolonne A: konkurrentvarenummer Kolonne B: konkurrentvarenummer uden mellemrum, bindestreger m.m. Kolonne C: konkurrentvaretekst Kolonne D: varegruppe Kolonne E: vores varenr., alt A Kolonne F: vores varetekst, alt. A Kolonne G: vores varenr., alt. B Kolonne H: vores varenr., alt B Kolonne I: navn på leverandør
Umiddelbart virker det - men kun hvis jeg indsætter ind nummer ad gangen. Jeg har brug for at kunne copy/paste en række numre på én gang.
Kan det lade sig gøre, at værdien, der bliver returneret tager udgangspunkt i både kolonne A (Konk varenr) og Kolonne B (Varenumre u/mellemrum) fra arket "Komplet konvertering"
I kolonne A står varenummeret helt originalt. I kolonne B vil mellemrum mm være fjernet. Det kan ikke ses i filen, jeg uploadede da varenumrene er ens i de to kolonner, men et ex. kan være:
Kolonne A 03425 005 50 Kolonne B 3425
Således at det ikke gør nogen forskel, om jeg taster 03425 005 50 eller 3425 i kolonne A på arket "konvertering".
Option Explicit Dim ws As Worksheet, wsData As Worksheet Dim Area As Range, wsArea As Range, Rcell As Range
Private Sub Worksheet_Change(ByVal Target As Range) Dim MyArray() Set ws = ActiveSheet Set wsArea = ws.Range("A3") If Not wsArea.Offset(1, 0).Value = "" Then Set wsArea = Range(wsArea, wsArea.End(xlDown)) Set wsData = Sheets("Komplet konvertering") Set Area = wsData.UsedRange Set Area = Area.Offset(2, 0) MyArray = Area
If Not Intersect(Target, Range("A:A")) Is Nothing Then For Each Rcell In wsArea Dim iRow As Integer For iRow = 1 To UBound(MyArray, 1) If MyArray(iRow, 2) = Rcell.Value Or MyArray(iRow, 1) = Rcell.Value Then ws.Cells(Rcell.Row, 5) = MyArray(iRow, 2) ws.Cells(Rcell.Row, 6) = MyArray(iRow, 4) ws.Cells(Rcell.Row, 7) = MyArray(iRow, 10) ws.Cells(Rcell.Row, 8) = MyArray(iRow, 5) ws.Cells(Rcell.Row, 9) = MyArray(iRow, 6) ws.Cells(Rcell.Row, 10) = MyArray(iRow, 8) ws.Cells(Rcell.Row, 11) = MyArray(iRow, 9) End If Next Next End If
End Sub
Kode til modul (Makro)
Sub SletData() Dim ws As Worksheet: Set ws = ActiveSheet Dim rCol As Range: Set rCol = ws.UsedRange Set rCol = rCol.Offset(2, 0) rCol.Value = "" End Sub
@Jan - Mit originale ark indeholder ca. 220.000 linjer. Umiddelbart meldes der fejl, hvis jeg forsøger at lave en konvertering, hvor alle linjer tages i brug. Er det for mange linjer?
If Not Intersect(Target, Range("A:A")) Is Nothing Then For Each Rcell In wsArea Dim iRow As Integer <<<<< kunne lyde som om Integer skal rettes til: Long
Option Explicit Dim ws As Worksheet, wsData As Worksheet Dim Area As Range, wsArea As Range, Rcell As Range
Private Sub Worksheet_Change(ByVal Target As Range) Dim MyArray() Set ws = ActiveSheet Set wsArea = ws.Range("A3") If Not wsArea.Offset(1, 0).Value = "" Then Set wsArea = Range(wsArea, wsArea.End(xlDown)) Set wsData = Sheets("Komplet konvertering") Set Area = wsData.UsedRange Set Area = Area.Offset(2, 0) MyArray = Area
If Not Intersect(Target, Range("A:A")) Is Nothing Then For Each Rcell In wsArea Dim iRow As Long For iRow = 1 To UBound(MyArray, 1) If MyArray(iRow, 2) = Rcell.Value Or MyArray(iRow, 1) = Rcell.Value Then ws.Cells(Rcell.Row, 5) = MyArray(iRow, 2) ws.Cells(Rcell.Row, 6) = MyArray(iRow, 3) ws.Cells(Rcell.Row, 7) = MyArray(iRow, 4) ws.Cells(Rcell.Row, 8) = MyArray(iRow, 11) ws.Cells(Rcell.Row, 9) = MyArray(iRow, 5) ws.Cells(Rcell.Row, 10) = MyArray(iRow, 6) ws.Cells(Rcell.Row, 11) = MyArray(iRow, 7) ws.Cells(Rcell.Row, 12) = MyArray(iRow, 8) ws.Cells(Rcell.Row, 13) = MyArray(iRow, 9) ws.Cells(Rcell.Row, 14) = MyArray(iRow, 10) End If Next Next End If
Det tager umiddelbart lige lang tid pr linje, uanset om jeg indsætter få eller mange varenumre.
Jeg har fjernet en del leverandører, således at listen er reduceret fra ca. 220.000 linjer til 55.000. Det har ikke ændret på hastigheden. Har lige lavet en lille test
Hvis jeg indsætter 20 varenumre, tager det 2 min.40 sek at køre disse igennem
Option Explicit Dim ws As Worksheet, wsData As Worksheet Dim Area As Range, wsArea As Range, Rcell As Range
Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False Dim MyArray(), wsArray(), lCount As Long, NewArray(1 To 100, 1 To 10), lRow As Long Set ws = ActiveSheet Set wsArea = ws.Range("A3") If Not wsArea.Offset(1, 0).Value = "" Then Set wsArea = Range(wsArea, wsArea.End(xlDown)) Set wsData = Sheets("Komplet konvertering") Set Area = wsData.UsedRange Set Area = Area.Offset(2, 0) MyArray = Area If ws.Range("A3") = "" Then Exit Sub wsArray = wsArea If Not Intersect(Target, Range("A:A")) Is Nothing Then For lCount = 1 To UBound(wsArray, 1) Dim iRow As Long For iRow = 1 To UBound(MyArray, 1) If MyArray(iRow, 2) = wsArray(lCount, 1) Or MyArray(iRow, 1) = wsArray(lCount, 1) Then NewArray(lCount, 1) = MyArray(iRow, 2) NewArray(lCount, 2) = MyArray(iRow, 3) NewArray(lCount, 3) = MyArray(iRow, 4) NewArray(lCount, 4) = MyArray(iRow, 11) NewArray(lCount, 5) = MyArray(iRow, 5) NewArray(lCount, 6) = MyArray(iRow, 6) NewArray(lCount, 7) = MyArray(iRow, 7) NewArray(lCount, 8) = MyArray(iRow, 8) NewArray(lCount, 9) = MyArray(iRow, 9) NewArray(lCount, 10) = MyArray(iRow, 10) lRow = lCount + 1 End If Next Next Dim newArea As Range Set newArea = ws.Range("E3", ws.Cells(UBound(NewArray, 1), 10)) newArea = NewArray End If Application.ScreenUpdating = True End Sub
Efter at min konverteringsfil har virket fint, melder den nu fejl, når jeg indsætter en stak varenumre: Run-time error '1004'
newArea = NewArray er markeret med gul når jeg går ind i VBA:
Next Dim newArea As Range Set newArea = ws.Range("E3", ws.Cells(UBound(NewArray, 1), 14)) newArea = NewArray End If Application.ScreenUpdating = True End Sub
Det er korrekt at 10 skulle rettes til 14, men fejlen kommer stadig. Kan dette være problemet:
NewArray(lCount, 10) = MyArray(iRow, 10)
Option Explicit Dim ws As Worksheet, wsData As Worksheet Dim Area As Range, wsArea As Range, Rcell As Range
Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False Dim MyArray(), wsArray(), lCount As Long, NewArray(1 To 2000, 1 To 14), lRow As Long Set ws = ActiveSheet Set wsArea = ws.Range("A3") If Not wsArea.Offset(1, 0).Value = "" Then Set wsArea = Range(wsArea, wsArea.End(xlDown)) Set wsData = Sheets("Komplet konvertering") Set Area = wsData.UsedRange Set Area = Area.Offset(2, 0) MyArray = Area If ws.Range("A3") = "" Then Exit Sub wsArray = wsArea If Not Intersect(Target, Range("A:A")) Is Nothing Then For lCount = 1 To UBound(wsArray, 1) Dim iRow As Long For iRow = 1 To UBound(MyArray, 1) If MyArray(iRow, 2) = wsArray(lCount, 1) Or MyArray(iRow, 1) = wsArray(lCount, 1) Then NewArray(lCount, 1) = MyArray(iRow, 2) NewArray(lCount, 2) = MyArray(iRow, 3) NewArray(lCount, 3) = MyArray(iRow, 4) NewArray(lCount, 4) = MyArray(iRow, 11) NewArray(lCount, 5) = MyArray(iRow, 5) NewArray(lCount, 6) = MyArray(iRow, 6) NewArray(lCount, 7) = MyArray(iRow, 7) NewArray(lCount, 8) = MyArray(iRow, 8) NewArray(lCount, 9) = MyArray(iRow, 9) NewArray(lCount, 10) = MyArray(iRow, 10) lRow = lCount + 1 End If Next Next Dim newArea As Range Set newArea = ws.Range("E3", ws.Cells(UBound(NewArray, 1), 14)) newArea = NewArray End If Application.ScreenUpdating = True End Sub
Synes godt om
Ny brugerNybegynder
Din løsning...
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.