En fråga

[jansor]

Ex Om jag får ett excelark och vill söka ex efter alla celler innehållandes texten äpplen och färga dom cellerna gula går det?.

Alltså En sökfunktion som söker igenom en excelfil och som färgar cellerna där texten äpple finns med gult

[Monshi]

Båda ja och nej.

 

Med nej menar jag det finns ingen enkel funktion i en cell som ordnar det.

 

Med jag menar jag två sätt det går med

1: Använd villkorsstyrd formatering med en formel som returnerar sant om sökordet finns i cellen. Formatera efter det.

2: VBA-kod.

Sub MySearch()
Dim sSök As String
Dim myColor As Long
Dim c As Range
Dim firstAdress As String
Dim rnSök As Range

'uppdatera vad som ska genomsökas
Set rnSök = Blad1.Cells

'gul färg.
myColor = 65535

sSök = InputBox("Vad ska det sökas efter", "Sökord")
If sSök = "" Then Exit Sub

With rnSök
   	Set c = .Find(sSök, lookat:=xlPart)
   	If Not c Is Nothing Then
       	firstAdress = c.Address
       	Do
           	c.Interior.Color = myColor
           	Set c = .FindNext(c)
           	If Not c Is Nothing Then
               	If c.Address = firstAdress Then Set c = Nothing
           	End If
       	Loop While Not c Is Nothing
   	End If
End With
End Sub

Vill du söka i en hel bok får man uppdatera koden lite, exempelvis ändra om funktionen ovan att ta som inargument sökord och området och ha en funktion som stegar igenom bladen i en bok.

[jansor]

Ok följdfråga på villkorsstyrd formatering först då. Om jag nu vill använda mej av villkorsstyrd formatering och har flera blad i arbetsboken hur skriver jag så att formateringen gäller hela arbetsboken?

[Monshi]

Det är handpåläggning som gäller. Dvs att du manuellt skapar denna formateringsregel. Och ärligt, om stora mängder data kanske inte en bra metod, då bättre med lite VBA-kod i en bok kan jag tro.

 

Se koden ovan som en proof-of-concept, den kan förbättras, förenklas en del.

[jansor]

Ok Vi får ta en sak i taget :-) 1. Så här ser den villkorstyrda formeln ut (Jag har office 2011 mac) I rutan Gäller för står det 'v 36'!$A$2:$J$42. Boken innehåller veckor och varje blad är en vecka. Hur skriver jag då om den skall gälla alla veckor alla veckor?

 

2 Nu till VBA koden Om jag ska söka efter texten äpple och boken innehåller 7 blad skriver jag då så här?

 

Dim firstAdress As String

Dim rnSök As Range

 

'uppdatera vad som ska genomsökas

Set rnSök = Blad1-Blad7.Cells???????????

 

'gul färg.

myColor = 65535

 

sSök = InputBox("Äpple")???????

If sSök = "" Then Exit Sub

 

With rnSök

Set c = .Find(sSök, lookat:=xlPart)

If Not c Is Nothing Then

firstAdress = c.Address

Do

c.Interior.Color = myColor

Set c = .FindNext©

If Not c Is Nothing Then

If c.Address = firstAdress Then Set c = Nothing

End If

Loop While Not c Is Nothing

End If

End With

End Sub

[Monshi]

Villkorsstyrd formel i stil med

=ICKE(ÄRFEL(HITTA("äpple";A1)))

givet att det område den appliceras på börjar i A1, annars uppdatera referensen så att A1 refererar till den första cellen.

 

Koden, nej sådär kan man icke göra.

 

Skriver om lite och lägger till en funktion.

Nu kör du MyStarter och denna stegar igenom bladen i boken som startat koden, söker efter "äpple" och varje träff får gul färg.

 

Det går, inte alltför svårt, att få koden att arbeta med böcker du öppnar eller liknande.

Sub MySearchAndColor(rnSök As Range, sSök As String)
Dim c As Range
Dim firstAdress As String
Dim myColor As Long

'gul färg.
myColor = 65535

If sSök = "" Then Exit Sub

With rnSök
Set c = .Find(sSök, lookat:=xlPart, lookin:=xlvalues)
If Not c Is Nothing Then
firstAdress = c.Address
Do
c.Interior.Color = myColor
Set c = .FindNext(c)
If Not c Is Nothing Then
If c.Address = firstAdress Then Set c = Nothing
End If
Loop While Not c Is Nothing
End If
End With
End Sub


Sub MyStarter()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
   	MySearchAndColor ws.Cells, "äpple"
Next ws

End Sub

nu går den, som sagt, genom alla blad i boken den är skriven i . Kopiera in koden i den bok du vill testa på och kör den.

[jansor]

Hej Igen . Det verkar inte funka. Jag vet inte om jag gör rätt. Jag har lagt in den i redigeraren i vba sen har jag öppnat

makron och valt kör och även stega men inget händer. Jag skickar med filen så kan du se om jag gjort rätt.

[jansor]

Vet inte om filen kom med. Hur som helst jag kan inte ladda upp en makroaktiverad fil

[Monshi]

nej, xlsm och xlsb kan du inte ladda upp, lägg in dem i ett zip-arkiv och ladda upp detta eller spara som xls.

 

men men, här får du en fil.

Klicka på knappen och koden körs i denna bok.

 

Okej, du ska få en manuell lösning också som kanske är lättare.

 

1: Klicka upp Sök-rutan i Excel.

2: Mata in "äpple" som sökord.

3: Klicka Sök alla

4: Formatera alla träffar, precis om du formatera en cell.

5: Upprepa på alla blad i boken.

FärgKod.xls

[jansor]

Hej Igen

 

Det funkar inte alls med den filen du gav mej. Det går i och för sig att göra en villkorsstyrd formatering som färgar alla celler med äpple gult och sedan kopiera den till alla blad men jag tycker det hade varit så smidigt med ett makro som man kör och det är klart.

[Monshi]

Oops, ta upp VBA-editor och ändra inkommande variabeln color till myColor

 

My bad.

Men å andra sidan, ganska enkelt manuellt förfarande som jag beskrev ovan.

 

En till liten ändring du kan göra. Om du vill kunna köra koden på annan öppen bok än den koden finns i kan du ändra ThisWorkbook till ActiveWorkbook.

Dock måste du, för att den ska köra på annan bok, göra enligt följande

1: Aktivera boken du vill söka igenom

2: Gå till VBA-editorn, starta koden manuellt.

 

Alternativt kan man iofs spara boken som ett tillägg, koppla snabbknappar till den osv.

 

Eller så kan jag skriva om den till att poppa upp en dialog som ber dig välja en fil som öppnas för att sökas igenom.

 

Vad önskas?

[jansor]

Jag har provat och Jag fattar inte var. Kan du skriva in så jag ser?

 

 

Option Explicit

 

Sub MySearchAndColor(rnSök As Range, sSök As String, color As Long)

Dim c As Range

Dim firstAdress As String

If sSök = "" Then Exit Sub

 

With rnSök

Set c = .Find(sSök, lookat:=xlPart, LookIn:=xlValues)

If Not c Is Nothing Then

firstAdress = c.Address

Do

c.Interior.color = myColor

Set c = .FindNext©

If Not c Is Nothing Then

If c.Address = firstAdress Then Set c = Nothing

End If

Loop While Not c Is Nothing

End If

End With

End Sub

 

 

Sub MyStarter()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

MySearchAndColor ws.Cells, "äpple", 65535

Next ws

 

End Sub

 

Private Sub CommandButton1_Click()

MyStarter

End Sub

[Monshi]

Sub MySearchAndColor(rnSök As Range, sSök As String, color As Long)

till

Sub MySearchAndColor(rnSök As Range, sSök As String, myColor As Long)

 

Får en känsla av att VBA är en ny erfarenhet för dig, kanske egentligen bättre om du tog den manuella vägen?

Hur många filer, hur stor mängd data handlar det om?

[jansor]

Nu funkar det. Jag är helt grön på vba. Jag provade sen att ändra sökordet och kopiera koden till en annan bok. Men då händer inget. Varför då?

[Monshi]

Borde fungera... för du kör väl koden?

Jag lade in knappen bara för enkelhetens skull, du kan öppna VBA-editorn, ställa markören i MyStarter och trycka på F5 så körs koden,

[jansor]

Du kanske ruttnar på detta. Ja jag kör koden och den funkar utmärkt på den filen du skickade sist. Men sen när jag kopierar koden.

in i den bok som jag ska ha koden i så sår det kompleringsfel metoden eller datamedlemmarna hittades inte. ?? fattar inte varför det inte går.

[Monshi]

Ingen fara, det här ska vi reda ut.

 

Det är inte som så att du kopierat koden från ditt inlägg ovan? Du kopierar väl koden inom Excel?

Eforum har ändrat lite i koden. När du skriver ( c ) tätt ihop blir det ©

 

Äh, nu får du en ny fil. Klicka på knappen och kör.

FärgKod2.xls

[jansor]

Hej igen. Det går inte trycka på knappen alls. Kör jag koden i från programmet säger den kompileringsfel variabeln har inte definerats. Är det någon inställning i excel som jag missat (Office 2011 mac) När det gäller att kopiera så öppnade jag den första färgkodfilen jag fick av dej och så gjorde jag som så.

 

Jag öpnnade redigeraren,

 

Kopierade koden

 

Stängde excelfilen

 

Öppnade den fil jag ville köra koden i och klistrade in koden i redigeraren.

 

Öppnade sedan makron och körde koden, provade alla val

 

funkade inte.

[Monshi]

Jaha, du kör på Mac...

 

Kan vara därför senaste filen inte fungerar, tycks vara fildialogen som inte fungerar på Mac. Lägger in den mer generella dialogen

Application.GetOpenFilename()

istället.

 

Testa denna fil istället och låt oss hoppas att det fungerar denna gång. Jag ger inte upp.

Om du får ett fel, ta reda på vilken rad det blir fel på.

FärgKod3.xls

[jansor]

Kanon ! Nu funkar det när man kör makrot men inte klicka mej rutan och det är ju inte hela världen. Tack!

Om jag nu ska skruva till det ytterligare. Går det i rutan där jag skriver äpplen skriva mer än äpplen tex jag vill söka efter äpplen gula, röda, blå. Kort sagt kan man göra så att den söker efter allt som står i den rutan?

[Monshi]

Oops, en miss av mig. Den där typen av knapp fungerar ju inte heller på Mac. Det vet jag ju.

Men högerklicka på knappen, koppla makro till den och välj den rutin som startar det hela. Gör det så fungerar knappen.

 

Mer avancerad sökning?

Du kan skriva in en sträng men inte rent generella sökord.

 

Om du vill ha flera söktermer får man göra om koden och bladet en aning.

1: Skriva varje sökterm i varsin cell

2: Göra sökningar inom resultatet för att ta ut de som stämmer på även sekundära sökordet.

 

Det går om det är det du önskar.

[jansor]

Nu funkar knappen. Det fanns ett knappval i office 2011 under utvecklare. Ja om jag kunde få 4 sökceller så vore det kanon.

[Monshi]

Här får du en ny version. Med fyra sökceller. Du kan fylla på fler om du vill. Koden startar med cell G7 och tittar sedan om nästa cell är tom eller ej. Om tom är den klar, annars söker den vidare med nästa term.

 

Finns säkert en gräns för det hela iochmed att koden jag skrivit är rekursiv.

FärgKod4.xls

[jansor]

Hahaha ! Helst sanslöst. Jo jag får upp en sak det står objects variabeln eller within blockvariabeln har inte angetts

[Monshi]

mmh, vilken rad? Sätt en brytpunkt och stega.

 

fast förresten, det går att lösa på icke rekursivt vis. Nog bättre.

Och om någon är bra på regexp går säkert även det att använda på smart sätt.

 

I filen i detta inlägg söker jag i cellen som hittats istället.

 

testa.

FärgKod5.xls