03. april 2007 - 11:41Der er
12 kommentarer og 1 løsning
Kan VBA finde standardord i celler samt slette ikke standard.
Hej Eksperter.
Jeg vil lave et Pivot diagram hvor jeg kan vise salgs statistik. Min pivot tabel skal på sigt danne grundlag for grafer – altså antal solgte vare per tidsenhed – pr sælger – mest solgte varetype osv
Jeg får kildedata via en liste fra vores økonomisystem. Listen er opstillet i kolonneform med tilbudsnummer, kunde, pris, varebetegnelse osv. Listen vil altid være opstillet ens mht kolonne antal og benævnelser.
Mit problem er ganske enkelt at den enkelte varebetegnelse ikke er anført som et enkelt nummer men som en betegnelse som f.eks BR3 eller J400 eller SR1 og at der i samme celle ofte forekommer andre tal og ord. En varebetegnelse i en celle kunne typisk være: "BR3 jf. (TT65.684)", hvor BR3 er varetypen og tallet i parentes et variabel tegningsnummer vi bruger som reference overfor kunden. Der forkommer tilfælde hvor varetypen helt er omgivet af andre tal evt ord, dog er varetypen altid omgivet af mellemrum.
Spørgsmålet er om jeg f.eks via VBA, kan identificere hvilket produkt som findes i tekststrengen. Jeg skal, gerne automatisk, have fundet varetypen, dvs f.eks BR3 i tekststrengen og have slettet alle andre ord og tal i cellen inden mine data er klar til Pivot tabellen. Jeg har vel omkring 30 forsk. standardvaretyper med forskellige navne som ovenstående.
Kan Excel evt via VBA identificere disse forekomster og slette alt andet end typenummeret således jeg ikke hver gang skal gøre dette manuelt ?
Kan Excel gøre det selvom varetype betegnelsen er inde i en sætning altså der er andre ord eller tal før og efter varebetegnelsen ?
Vil Excel skelne mellem store og små bogstaver i en angivet varebetegnelse såfremt en sælger fejlagtig har skrevet med småt ?
Dim idTabel As Variant, antalID Sub redigerVareID() Dim antalRæk, vID As String, rID As String idTabel = Array("BR3", "J400", "SR1") 'tilpasses antalID = 3 'antal elementer i Array tilpasses
antalRæk = ActiveCell.SpecialCells(xlLastCell).Row For r = 2 To antalRæk vID = Cells(r, 4) rID = isolerID(vID) If rID <> "" Then Cells(r, 5) = rID Else MsgBox (vID + " kunne ej isolered") End If Next r
End Sub Private Function isolerID(id) Dim xId xId = UCase(id)
For f = 0 To antalID - 1 If InStr(xId, idTabel(f)) > 0 Then isolerID = idTabel(f) Exit Function End If Next f isolerID = "" End Function
Jeg har kopieret koden til Ark1 i VBA editoren. For at teste funktionen slettede jeg alle kolonner i Ark1 pånær kolonnen ”Betegnelse” hvori alle varebetegnelser fremstår.
Samtidigt har jeg slettet alle celler med varebetegnelser der afviger fra de tre jeg vil teste på, nemlig: LR4, Q030 og N35 hvilket også er de tre typer jeg har indskrevet i din VBA kode dvs i Array parenteserne.
Hvis jeg kører koderne kommer MsgBox melding “kunne ej isoleres” op hele tiden. Der sker dog ikke noget med data der står i cellerne. Det er som om at koden stopper ved End If i nedenstående udklip.
For r = 2 To antalRæk vID = Cells(r, 4) rID = isolerID(vID) If rID <> "" Then Cells(r, 5) = rID Else MsgBox (vID + " kunne ej isoleres") End If Next r
End Sub ´ Gør jeg noget forkert eller mangler jeg at gore noget ?
Bemærk følgende - idet jeg tror at sletning af kolonner har rykket forudsætningerne:
For r = 2 To antalRæk vID = Cells(r, 4) '<<<-VareID hentesfra kolonne 4 = D rID = isolerID(vID) If rID <> "" Then Cells(r, 5) = rID '<<<-Redigeret ID indsættes i kolonne 5 = E Else MsgBox (vID + " kunne ej isoleres") End If Next r
Perfekt - jeg har oprettet en CommandButton der kører makroen som den skal. Meldingen "kunne ej isoleres" kommer også frem når der i en celle står et ukendt partnummer. Her kunne det være helt i top hvis der i denne MsgBox istedet stod: "Kunne ej isoleres. Slet værdi ?" Ved tryk på "JA" vi hele den specifikke række i regnearket hvor cellen fremstår i simpelthen blive slettet. Ved tryk på "NEJ" kører makroen videre. Dette vil gøre at der i mit regneark til slut kun er kendte varenumre da makroen både sortere al unødig tekst fra - samtidigt med at ukendte varenumre bliver slettet i forbindelse med eksekveringen af makroen. Er dette muligt at programmere ?
Dim idTabel As Variant, antalID Sub redigerVareID() Dim antalRæk, vID As String, rID As String, svar idTabel = Array("BR3", "J400", "SR1") 'tilpasses antalID = 3 'antal elementer i Array tilpasses
antalRæk = ActiveCell.SpecialCells(xlLastCell).Row For r = 2 To antalRæk vID = Cells(r, 4) rID = isolerID(vID) If rID <> "" Then Cells(r, 5) = rID Else Rem Spørg ikke på evt. "blanke" If vID <> "" Then svar = MsgBox(vID + " kunne ej isoleres. Slet værdi?", vbYesNo) If svar = 6 Then Rows(CStr(r)).Select Selection.Delete Shift:=xlUp Cells(r, 1).Select End If End If End If Next r
End Sub Private Function isolerID(id) Dim xId xId = UCase(id)
For f = 0 To antalID - 1 If InStr(xId, idTabel(f)) > 0 Then isolerID = idTabel(f) Exit Function End If Next f isolerID = "" End Function
Hmm - jeg har 53 rækker i Arket. 7 rækker indeholder varetyper som ikke er oprettet i idTabel Array og som derfor burde blive slettet. Koden finder kun 4 af de 7 rækker som den dog sletter som den skal. De resterende 3 rækker står tilbage. Mystisk. Jeg tror at det bliver meget kompliceret at få denne nød knækket og må acceptere at jeg alligevel manuelt skal slette nogle rækker. Stadigvæk er det en stor hjælp jeg har fået idet jobbet er lettet betydeligt med kun få rækker der kræver opmærksomhed. Point til dig Supertekst - smider du et svar?
Yderligere tilføjelse - efter at der nu kan slettes rækker:
If vID <> "" Then svar = MsgBox(vID + " kunne ej isoleres. Slet værdi?", vbYesNo) If svar = 6 Then Rows(CStr(r)).Select Selection.Delete Shift:=xlUp r = r - 1 '<------------------ tilføj denne linie Cells(r, 1).Select End If End If
Fint - det kom jeg i tanke om til eftermiddagsteen.. Tak for det - til det sidste: træning...
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.
