Excel VBA Jämföra texter på olika blad

[Tommy H]

Hej!

Jag försöker få till ett enkelt makro (behöver vara VBA) där jag vill kontrollera om en textsträng i kolumn L på "Blad 1" finns med i kolumn A eller D på Blad 2.

Finns strängen med så kör vi vidare till nästa osv., men om värdet saknas på blad 2, så ska något hända t.ex värdet rödmarkeras på blad 1.

Värdena finns i nämnda kolumner men det finns en del tomma rader mellan dem.

För att förstå bättre så kan man tänka sig att det på blad 1 finns ett antal artikelnummer som ska kollas mot blad 2 som skulle kunna vara ett lager eller så.

Klart som korvspad?

/TH

 

[Monshi]

Varför VBA-krav? Detta går med största sannolikhet att lösa med formler.

Några PASSA-satser räcker:

=OMFEL(PASSA(L4;Blad2!A:A;0);PASSA(L4;Blad2!D:D;0))

och lägg på en villkorsstyrd formatering baserat på värdet i cellen där formeln finns. Om felvärde, gör inget, annars färglägg.

 

Med kod

Väldigt generella ord. Inte en specifik cell/område man ska leta efter värden i? Hela L? Hela A och D kolumnen?

Okej, detta fungerar

Sub MyFunction()
    Dim rnSource As Range
    Set rnSource = Blad1.Range("L:l").SpecialCells(xlCellTypeConstants)
    Dim myAr As Range
    Dim myCell As Range
    Dim rnTarget As Range
    Dim c As Range
    For Each myAr In rnSource.Areas
        For Each myCell In myAr
            Set c = Blad2.Range("A:A").Find(myCell)
            If c Is Nothing Then
                Set c = Blad2.Range("D:D").Find(myCell)
            End If
            If Not c Is Nothing Then
                myCell.Interior.ColorIndex = 23
            End If
        Next myCell
    Next myAr

End Sub

[Tommy H]

 

Varför VBA-krav? Detta går med största sannolikhet att lösa med formler.

Några PASSA-satser räcker:

=OMFEL(PASSA(L4;Blad2!A:A;0);PASSA(L4;Blad2!D:D;0))

och lägg på en villkorsstyrd formatering baserat på värdet i cellen där formeln finns. Om felvärde, gör inget, annars färglägg.

 

Med kod

Väldigt generella ord. Inte en specifik cell/område man ska leta efter värden i? Hela L? Hela A och D kolumnen?

Okej, detta fungerar

Sub MyFunction()
    Dim rnSource As Range
    Set rnSource = Blad1.Range("L:l").SpecialCells(xlCellTypeConstants)
    Dim myAr As Range
    Dim myCell As Range
    Dim rnTarget As Range
    Dim c As Range
    For Each myAr In rnSource.Areas
        For Each myCell In myAr
            Set c = Blad2.Range("A:A").Find(myCell)
            If c Is Nothing Then
                Set c = Blad2.Range("D:D").Find(myCell)
            End If
            If Not c Is Nothing Then
                myCell.Interior.ColorIndex = 23
            End If
        Next myCell
    Next myAr

End Sub

Ja, snabba svar som vanligt!

Det behöver vara VBA eftersom det ska snurra tillsammans med annan kod!

 

Tackar för hjälpen, jag körde fast själv och hittade inget bra på nätet!

 

/TH

[Monshi]

Notera bara att koden "hittar" källcellerna genom att leta efter konstanter i kolumnen. Om det är formler som håller värdena får man ta till annat grepp

[Tommy H]

Hmmm...

Ja, tack och lov så är det bara vanliga "dumma" konstanter...

 

//TH

[Tommy H]

Jag har försökt att få till det här och det fungerar så där...

Tanken är att om det inte matchar mellan områdena så ska jag få upp Userform9 där jag då kan skriva in ett nytt värde i cellen med det felaktiga värdet.

Det fungerar bra under dom 4-5 första träffarna, där det då går att skriva in ett nytt värde i den enskilda cellen.

Sedan så sker det något skumt för nästa gång som jag skriver in ett värde i en "felaktig" cell så kommer det värdet att hamna i flera celler eftervarandra på målbladet.

Det är kanske svårt att se funktinen, och jag har säkert rört till det någonstans.

Tanken är som sagt att jag ska få en möjlighet att via Userform9 att skriva in ett nytt värde där det var fel tidigare unikt i den cellen, och sedan loopa vidare till nästa felaktiga cell och där mata in ett nytt värde osv.

Klart som korvspad?

Vad gör jag för fel?

Private Sub Symbolkoll()
    Dim PrenadRng As Range: Set PrenadRng = Worksheets("Prenad").Range("L2:L5000") _
    .SpecialCells(xlCellTypeConstantss)
    Dim aSymbRng As Range: Set aSymbRng = Worksheets("Symboler").Range("A3:A200")
    Dim bSymbRng As Range: Set bSymbRng = Worksheets("Symboler").Range("G3:G200")
    Dim uSymbRng As Range: Set uSymbRng = Union(aSymbRng, bSymbRng)
    Dim mycell As Range
    Dim MyVal As Boolean
    Dim c As Range
   
   
    For Each mycell In PrenadRng
    Set c = uSymbRng.Find(mycell)
   
                If c Is Nothing Then
                    mycell.Interior.ColorIndex = 3 'rödmarkerar
                    Application.Goto reference:=mycell, Scroll:=True
                        
       
        With UserForm9
            .Show
            On Error GoTo errHandle
            Dim BlVal As Boolean
            BlVal = .MyVal
                Dim mystring As String
                mystring = .MyString1
          
        End With
            If BlVal = False Then
            Worksheets("RULES").Range("Z13").Value = "Symbolkontrollen avbröts"
            Exit Sub
            End If
   
   
                    End If
                        If mystring = "" Then
                        GoTo 10
                        Else
                            mycell.Value = mystring
                            mycell.Interior.ColorIndex = xlNone
                        End If
10        Next mycell
errHandle:
Worksheets("RULES").Range("Z13").Value = "Symbolkontrollen avbröts"
Exit Sub
 

Och så här ser Userform koden ut

Option Explicit
Dim MyValue As Boolean
Dim mystring As String
'
Private Sub CommandButton3_Click()
'Välj¨
   With Me.TextBox1
      .SetFocus
   End With
mystring = Me.TextBox1.Text
    MyValue = True
TextBox1.Value = ""
    Me.Hide
End Sub
'
'
Private Sub CommandButton4_Click()
'Avbryt
    MyValue = False
    Me.Hide
End Sub
'
'
Private Sub UserForm_Click()
    MyValue = False
    Me.Hide
End Sub
'
'
Public Property Get MyVal() As Boolean
    MyVal = MyValue
End Property
Public Property Get MyString1() As String
    MyString1 = mystring
End Property

[Monshi]

Två noteringar

1: Du tar upp ett UserForm vid varje träff?

2: Goto är nono. Nej nej nej, man ska INTE använda Goto, det är en ful avart i språket som man kan och ska undvika!

 

sådär, du ska vi se vad som blir fel då..

Vi börjar med

1: variabeldeklarering. Du deklarerar några variabler på fel ställe, djupt inne i koden. I teorin ska en variabel deklarerad inom en With-sats inte leva utanför denna.

2: Om du tar bort ditt Userform ur koden, kommentera bort det, blir rätt celler markerade?

3: Din Goto. Bort med den:

If mystring <> "" Then
  mycell.Value = mystring
  mycell.Interior.ColorIndex = xlNone
End If

[Tommy H]

 

Två noteringar

1: Du tar upp ett UserForm vid varje träff?

2: Goto är nono. Nej nej nej, man ska INTE använda Goto, det är en ful avart i språket som man kan och ska undvika!

 

sådär, du ska vi se vad som blir fel då..

Vi börjar med

1: variabeldeklarering. Du deklarerar några variabler på fel ställe, djupt inne i koden. I teorin ska en variabel deklarerad inom en With-sats inte leva utanför denna.

2: Om du tar bort ditt Userform ur koden, kommentera bort det, blir rätt celler markerade?

3: Din Goto. Bort med den:

If mystring <> "" Then
  mycell.Value = mystring
  mycell.Interior.ColorIndex = xlNone
End If

Hmmm...

Ja, tar jag bort Userformen så verkar det som jag får ett stopp på varje "felaktig" cell i tur och ordning och den markeras röd.

Så långt verkar det ok!

Då har jag uppenbarligen rört till det i Userformkoden i stället....

Men hur?

Och snälla, HUR gör jag istället för mitt "GoTo 10"?

Du har tjatat på mig ett antal gånger om detta men jag får inte till det.

Det är ju när jag vill hoppa till ett annat ställe i koden som jag använder det...

 

/TH

[Monshi]

Du ersätter din if-sats med min så ska det fungera på samma vis.

 

En annan modell att lösa det via är att dela upp det i tre rutiner.

 

1: En moderrutin

2: En rutin som returnerar alla celler med felaktigt värde i

3: En rutin, din Userform, som tar dessa celler som inargument och stegar igenom.

 

Nu ska din kod fungera, jag ser inte vad som gör att den går fel vid en titt på koden. 

 

Kan du ladda upp ett ark med ett exempel jag kan provköra, ett där det blir fel?

Notera att du måste zippa kodaktiverade filformat för att eforum ska acceptera filen.

[Tommy H]

Det var som...

Ja, det ser ut att snurra på hur fint som helst!

 

Som vanligt så bugar jag och tackar för experthjälpen!

 

/TH

[Monshi]

Var det Goto-satsen som ställde till det?

Inte förvånande om...

[Tommy H]

^Mja..

^{Jag vet inte riktigt.}

^{Jag blev så nöjd att det fungerade och att det "inte var mer" som behövde fixas, så jag har inte tagit mig tid att kolla det närmare!}

^{Jag vet också din inställning till GoTo-satserna, men vad rtycker du jag ska ha i stället?}

^{Jag får ofta sådana problem att jag vill fara fram och åter i koden och då fungerar vju GoTo satserna...}

^{(kanske lite utanför ämnet, men dock)}

^/TH

[Monshi]

Goto är av ondo och behöver man nyttja den har man normalt tänkt fel. De allra flesta programspråk finns inte goto i och man klarar sig galant utan.

 

En enkel regel vid programmering är att en funktion ska vara atomär, den ska göra en sak, följa en rak linje.

Då brukar man slippa problem som goto. Det stora problemet med goto är att det i praktiken blir omöjligt att följa vad koden gör då man plötsligt kan flyga till helt annan del av koden.

 

Enda goto-satsen man ska använda i VBA är den som använd vid felhantering då det är den enda felhanteringen som finns.

Men vi kan ju ta din IF-sats ovan, där behövdes inte goto alls. Du hade kommit till samma punkt i koden ändå. Jag skrev bara om den så att slapp en tom rad vid positivt svar i If-satsen.