Bu öğretici, Excel VBA'da Bul ve Değiştir yöntemlerinin nasıl kullanılacağını gösterecektir.
VBA Bul
Excel'de mükemmel yerleşik Bulmak ve Bul ve Değiştir aletler.
Kısayollarla etkinleştirilebilirler CTRL + F (Bul) veya CTRL + H (Değiştir) veya Şerit aracılığıyla: Ana Sayfa > Düzenleme > Bul ve Seç.
Tıklayarak Seçenekler, gelişmiş arama seçeneklerini görebilirsiniz:
VBA kullanarak hem Bul hem de Değiştir yöntemlerine kolayca erişebilirsiniz. Bu yerleşik yöntemler, VBA'da kendiniz yazabileceğiniz her şeyden çok daha hızlıdır.
VBA Örneği Bul
Bul işlevini göstermek için, Sayfa1'de aşağıdaki veri setini oluşturduk.
Takip etmek isterseniz, verileri kendi çalışma kitabınıza girin.
<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>
İsteğe Bağlı Parametreler Olmadan VBA Bul
VBA Find yöntemini kullanırken, ayarlayabileceğiniz birçok isteğe bağlı parametre vardır.
Bul Yöntemini kullanırken tüm parametreleri tanımlamanızı şiddetle tavsiye ederiz!
İsteğe bağlı parametreleri tanımlamazsanız, VBA, Excel'in Bul penceresinde şu anda seçili olan parametreleri kullanır. Bu, kod çalıştırıldığında hangi arama parametrelerinin kullanıldığını bilemeyebileceğiniz anlamına gelir. Bul, çalışma kitabının tamamında veya bir sayfada çalıştırılabilir. Formüller veya değerler arayabilir. Excel'in Bul Penceresinde seçili olanı manuel olarak kontrol etmediğiniz sürece bilmenin bir yolu yoktur.
Basit olması için, isteğe bağlı parametre tanımlanmamış bir örnekle başlayacağız.
Basit Bul Örneği
Basit bir Bul örneğine bakalım:
| 123456789 | Alt TestBul()Aralık Olarak MyRange DimMyRange = Sheets("Sayfa1").UsedRange.Find("çalışan") olarak ayarlayınMsgBox MyRange.AddressMsgBox MyRange.ColumnMsgBox MyRange.RowAlt Bitiş |
Bu kod, Kullanılan Sayfa1 Aralığında "çalışan"ı arar. “Çalışan” bulursa, ilk bulunan aralığı MyRange değişkenine atar.
Ardından, bulunan metnin adresi, sütunu ve satırı ile mesaj kutuları görüntülenecektir.
Bu örnekte, varsayılan Bul ayarları kullanılmıştır (Excel'in Bul Penceresinde değiştirilmediği varsayılarak):
- Arama metni kısmen hücre değeriyle eşleştirildi (tam hücre eşleşmesi gerekli değildir)
- Arama büyük/küçük harf duyarlı değildir.
- Bul yalnızca tek bir çalışma sayfasında arama yapar
Bu ayarlar çeşitli isteğe bağlı parametrelerle değiştirilebilir (aşağıda tartışılmıştır).
Yöntem Notlarını Bul
- Bul, metnin bulunduğu hücreyi seçmez. Yalnızca, kodunuzda değiştirebileceğiniz bulunan aralığı tanımlar.
- Find yöntemi yalnızca bulunan ilk örneği bulur.
- Joker karakterler (*) kullanabilirsiniz, ör. 'E*' için arama yapın
Hiçbirşey Bulunamadı
Arama metni yoksa, aralık nesnesi boş kalacaktır. Bu, kodunuz konum değerlerini görüntülemeye çalıştığında, var olmadıkları için büyük bir soruna neden olur. Bu, istemediğiniz bir hata mesajıyla sonuçlanacaktır.
Neyse ki, Is Operator'u kullanarak VBA içinde boş bir aralık nesnesini test edebilirsiniz:
| 1 | MyRange Değilse O Zaman Hiçbir Şey Değilse |
Kodu önceki örneğimize ekleyerek:
| 12345678910111213 | Alt TestBul()Aralık Olarak MyRange DimMyRange = Sheets("Sayfa1").UsedRange.Find("çalışan") olarak ayarlayınMyRange Değilse O Zaman Hiçbir Şey DeğilseMsgBox MyRange.AddressMsgBox MyRange.ColumnMsgBox MyRange.RowBaşkaMsgBox "Bulunamadı"BitirAlt Bitiş |
Parametreleri Bul
Şimdiye kadar, Find yöntemini kullanmanın yalnızca temel bir örneğine baktık. Ancak, aramanızı hassaslaştırmanıza yardımcı olacak bir dizi isteğe bağlı parametre vardır.
| Parametre | Tip | Açıklama | değerler |
| Ne | Gerekli | Aranacak değer | Dize veya sayısal gibi herhangi bir veri türü |
| Sonrasında | İsteğe bağlı | Aramanıza başlamak için tek hücre referansı | hücre adresi |
| İçine bakmak | İsteğe bağlı | Arama için Formüller, Değerler, Yorumlar kullanın | xlDeğerler, xlFormüller, xlYorumlar |
| Bakmak | İsteğe bağlı | Bir hücrenin bir kısmını veya tamamını eşleştirme | xlBütün, xlPart |
| AramaSiparişi | İsteğe bağlı | Satırlarda veya sütunlarda arama sırası | xlByRows, xlByColummns |
| Arama Yönü | İsteğe bağlı | Arama yapmak için yön - ileri veya geri | xlSonraki, xlÖnceki |
| kibrit kutusu | İsteğe bağlı | Arama büyük/küçük harf duyarlıdır veya değildir | Doğru ya da yanlış |
| MatchByte | İsteğe bağlı | Yalnızca çift baytlık dil desteği yüklediyseniz kullanılır, örn. Çin Dili | Doğru ya da yanlış |
| Arama Biçimi | İsteğe bağlı | Hücre biçimine göre aramaya izin ver | Doğru ya da yanlış |
Parametreden Sonra ve Birden Çok Değer Bul
sen kullan parametreden sonra Aramanız için başlangıç hücresini belirtmek için Bu, aradığınız değerin birden fazla örneğinin olduğu durumlarda kullanışlıdır.
Bir arama zaten bir değer bulduysa ve daha fazla değer bulunacağını biliyorsanız, ilk örneği kaydetmek için Find yöntemini 'Sonra' parametresiyle kullanır ve ardından bu hücreyi sonraki arama için başlangıç noktası olarak kullanırsınız.
Arama metninizin birden çok örneğini bulmak için bunu kullanabilirsiniz:
| 123456789101112131415161718192021222324252627282930313233343536 | Alt TestMultipleFinds()MyRange As Range, OldRange As Range, FindStr As String'Işık ve Isı'nın ilk örneğini arayınMyRange = Sheets("Sayfa1").UsedRange.Find("Işık ve Isı") olarak ayarlayın'Bulumazsan çıkMyRange Hiçbir Şey Değilse Sub'dan Çıkın' Bulunan ilk adresi gösterMsgBox MyRange.Address'Aralık nesnesinin bir kopyasını oluşturOldRange = MyRange olarak ayarlayın'Adresi "|" ile sınırlayarak dizeye ekleyin karakterFindStr = FindStr & "|" & MyRange.Adres'Diğer örnekleri arayan aralığı yineleyinYapmakSonra parametresi olarak önceki bulunan adresi kullanarak 'Işık ve Isı' arayınMyRange = Sheets("Sheet1").UsedRange.Find("Light & Heat", After:=Range(OldRange.Address)) olarak ayarlayın'Adres zaten bulunduysa do döngüsünden çıkın - bu sürekli döngüyü durdururInStr(FindStr, MyRange.Address) ise Çıkış Yap'En son bulunan adresi gösterMsgBox MyRange.Address'En son adresi adres dizisine ekleFindStr = FindStr & "|" & MyRange.Adres'geçerli aralığın bir kopyasını oluşturOldRange = MyRange olarak ayarlayınDöngüAlt Bitiş |
Bu kod, kullanılan aralık boyunca yinelenir ve bir "Işık ve Isı" örneği bulduğunda adresi görüntüler.
FindStr'de yinelenen bir adres bulunana kadar kodun döngüye devam edeceğini ve bu durumda Do döngüsünden çıkacağını unutmayın.
LookIn Parametresi
kullanabilirsiniz LookIn parametresi hücrenin hangi bileşeninde arama yapmak istediğinizi belirtmek için. Bir hücrede değerler, formüller veya açıklamalar belirtebilirsiniz.
- xlDeğerler - Hücre değerlerini arar (hesaplandıktan sonra bir hücrenin son değeri)
- xlFormüller - Hücre formülü içinde arama yapar (hücreye ne girilirse girilir)
- xlYorumlar - Hücre notları içinde arama yapar
- xlYorumlarDişli - Hücre yorumları içinde arama yapar
Çalışma sayfasına bir formül girildiğini varsayarak, herhangi bir formülün ilk konumunu bulmak için bu örnek kodu kullanabilirsiniz:
| 12345678910 | Alt TestLookIn()Aralık Olarak MyRange DimMyRange = Sheets("Sheet1").UsedRange.Find("=", LookIn:=xlFormulas) olarak ayarlayınMyRange Değilse O Zaman Hiçbir Şey DeğilseMsgBox MyRange.AddressBaşkaMsgBox "Bulunamadı"BitirAlt Bitiş |
"LookIn" parametresi xlValues olarak ayarlandıysa, kodda "Bulunamadı" mesajı görüntülenir. Bu örnekte B10 döndürecektir.
LookAt Parametresini Kullanma
NS LookAt parametresi find tam hücre eşleşmesini mi arayacağını yoksa arama değerini içeren herhangi bir hücreyi mi arayacağını belirler.
- xlBütün - Tüm hücrenin arama değeriyle eşleşmesini gerektirir
- xlParça - Arama dizesi için bir hücre içinde arama yapar
Bu kod örneği, "ışık" metnini içeren ilk hücreyi bulacaktır. İle birlikte Bak:=xlPart, “Işık ve Isı” için bir eşleşme döndürür.
| 123456789 | Alt TestLookAt()Aralık Olarak MyRange DimMyRange = Sheets("Sheet1").UsedRange.Find("light", Lookat:=xlPart) olarak ayarlayınMyRange Değilse O Zaman Hiçbir Şey DeğilseMsgBox MyRange.AddressBaşkaMsgBox "Bulunamadı"BitirAlt Bitiş |
Eğer xlBütün ayarlandığında, bir eşleşme yalnızca hücre değeri "hafif" olduğunda geri dönecektir.
Arama Sırası Parametresi
NS Arama Sırası parametresi aramanın aralık boyunca nasıl gerçekleştirileceğini belirler.
- xlSatırlar - Arama satır satır yapılır
- xlXolumns - Arama sütun sütun yapılır
| 123456789 | Alt TestAramaSiparişi()Aralık Olarak MyRange DimMyRange = Sheets("Sheet1").UsedRange.Find("employee", SearchOrder:=xlColumns) olarak ayarlayınMyRange Değilse O Zaman Hiçbir Şey DeğilseMsgBox MyRange.AddressBaşkaMsgBox "Bulunamadı"BitirAlt Bitiş |
Bu, hangi eşleşmenin önce bulunacağını etkiler.
Daha önce çalışma sayfasına girilen test verileri kullanılarak, arama sırası sütun olduğunda, bulunan hücre A5'tir. Arama sırası parametresi xlRows olarak değiştirildiğinde, bulunan hücre C4'tür.
Arama aralığında yinelenen değerleriniz varsa ve belirli bir sütun adı altında ilk örneği bulmak istiyorsanız bu önemlidir.
Arama Yönü Parametresi
NS Arama Yönü parametresi aramanın hangi yöne gideceğini belirler - etkili bir şekilde ileri veya geri.
- xlSonraki - Aralıktaki bir sonraki eşleşen değeri ara
- xlÖnceki - Aralıkta önceki eşleşen değeri arayın
Yine, arama aralığında yinelenen değerler varsa, hangisinin önce bulunduğuna etkisi olabilir.
| 12345678910 | Alt TestSearchDirection()Aralık Olarak MyRange DimMyRange = Sheets("Sheet1").UsedRange.Find("heat", SearchDirection:=xlPrevious) olarak ayarlayın.MyRange Değilse O Zaman Hiçbir Şey DeğilseMsgBox MyRange.AddressBaşkaMsgBox "Bulunamadı"BitirAlt Bitiş |
Bu kodu test verilerinde kullanarak, xlPrevious arama yönü C9 konumunu döndürür. xlNext parametresinin kullanılması A4 konumunu döndürür.
Sonraki parametre, aramanın arama aralığının sol üst köşesinde başlayacağı ve aşağı doğru çalışacağı anlamına gelir. Önceki parametre, aramanın, arama aralığının sağ alt köşesinde başlayacağı ve yukarı doğru çalışacağı anlamına gelir.
MatchByte Parametresi
NS MatchBye parametresi yalnızca Çince, Rusça ve Japonca gibi her bir karakteri temsil etmek için çift bayt kullanan diller için kullanılır.
Bu parametre 'True' olarak ayarlanırsa Bul, yalnızca çift baytlık karakterlerle çift baytlık karakterlerle eşleşir. Parametre 'Yanlış' olarak ayarlanırsa, çift baytlık bir karakter, tek veya çift baytlık karakterlerle eşleşir.
Arama Biçimi Parametresi
NS Arama Biçimi parametresi eşleşen hücre biçimlerini aramanızı sağlar. Bu, kullanılan belirli bir yazı tipi, kalın bir yazı tipi veya metin rengi olabilir. Bu parametreyi kullanmadan önce, Application.FindFormat özelliğini kullanarak arama için gereken formatı ayarlamalısınız.
İşte nasıl kullanılacağına bir örnek:
| 12345678910111213 | Alt TestSearchFormat()Aralık Olarak MyRange DimApplication.FindFormat.ClearApplication.FindFormat.Font.Bold = DoğruMyRange = Sheets("Sheet1").UsedRange.Find("heat", Searchformat:=True) olarak ayarlayınMyRange Değilse O Zaman Hiçbir Şey DeğilseMsgBox MyRange.AddressBaşkaMsgBox "Bulunamadı"BitirApplication.FindFormat.ClearAlt Bitiş |
Bu örnekte, BulFormat özellik, kalın bir yazı tipi arayacak şekilde ayarlanmıştır. Find ifadesi daha sonra SearchFormat parametresini True olarak ayarlayarak 'ısı' kelimesini arar, böylece yazı tipi kalınsa bu metnin yalnızca bir örneğini döndürür.
Daha önce gösterilen örnek çalışma sayfası verilerinde bu, kalın yazı tipinde 'ısı' kelimesini içeren tek hücre olan A9'u döndürecektir.
Kodun sonunda FindFormat özelliğinin temizlendiğinden emin olun. Bunu yapmazsanız, bir sonraki aramanız yine de bunu hesaba katacak ve yanlış sonuçlar verecektir.
SearchFormat parametresini kullandığınız yerde, arama değeri olarak bir joker karakter (*) de kullanabilirsiniz. Bu durumda, kalın yazı tipiyle herhangi bir değeri arayacaktır:
| 1 | MyRange = Sheets("Sheet1").UsedRange.Find("*", Searchformat:=True) olarak ayarlayın |
Birden Fazla Parametre Kullanma
Burada tartışılan tüm arama parametreleri, gerekirse birbirleriyle kombinasyon halinde kullanılabilir.
Örneğin, hücre metninin tamamına bakmak için "LookIn" parametresini "MatchCase" parametresiyle birleştirebilirsiniz, ancak bu büyük/küçük harf duyarlıdır.
| 123456789 | Alt TestMultipleParameters()Aralık Olarak MyRange DimMyRange = Sheets("Sheet1").UsedRange.Find("Light & Heat", LookAt:=xlWhole, MatchCase:=True) olarak ayarlayınMyRange Değilse O Zaman Hiçbir Şey DeğilseMsgBox MyRange.AddressBaşkaMsgBox "Bulunamadı"BitirAlt Bitiş |
Bu örnekte, kod A4 döndürecektir, ancak metnin yalnızca bir bölümünü kullandıysak, ör. 'ısı', hiçbir şey bulunmaz çünkü hücre değerinin tamamında eşleşiriz. Ayrıca, dava eşleşmediği için başarısız olur.
| 1 | MyRange = Sheets("Sheet1").UsedRange.Find("heat", LookAt:=xlWhole, MatchCase:=True) olarak ayarlayın |
Excel VBA'da değiştirin
Tahmin edebileceğiniz gibi, Excel VBA'da 'Bul'a çok benzer bir şekilde çalışan ancak bulunan hücre konumundaki değerleri yeni bir değerle değiştiren bir Değiştir işlevi vardır.
Bunlar, Replace method deyiminde kullanabileceğiniz parametrelerdir. Bunlar, Find yöntemi deyimiyle tamamen aynı şekilde çalışır. "Bul"un tek farkı, bir Değiştirme parametresi belirtmeniz gerekmesidir.
| İsim | Tip | Açıklama | değerler |
| Ne | Gerekli | Aranacak değer | Dize veya sayısal gibi herhangi bir veri türü |
| Yenisiyle değiştirme | Gerekli | Yedek dize. | Dize veya sayısal gibi herhangi bir veri türü |
| Bakmak | İsteğe bağlı | Hücrenin bir kısmını veya tamamını eşleştirme | xlPart veya xlBütün |
| AramaSiparişi | İsteğe bağlı | Arama sırası - Satırlar veya Sütunlar | xlByRows veya xlByColumns |
| kibrit kutusu | İsteğe bağlı | Arama büyük/küçük harf duyarlıdır veya değildir | Doğru ya da yanlış |
| MatchByte | İsteğe bağlı | Yalnızca çift bayt dil desteği yüklediyseniz kullanılır | Doğru ya da yanlış |
| Arama Biçimi | İsteğe bağlı | Hücre biçimine göre aramaya izin ver | Doğru ya da yanlış |
| Biçim Değiştir | İsteğe bağlı | Yöntemin değiştirme biçimi. | Doğru ya da yanlış |
Biçim Değiştir parametresi, belirli bir biçime sahip bir hücreyi arar; Kalın, SearchFormat parametresinin Find yönteminde çalıştığı gibi. Daha önce gösterilen Bul örnek kodunda gösterildiği gibi, önce Application.FindFormat özelliğini ayarlamanız gerekir.
İsteğe Bağlı Parametreler Olmadan Değiştir
En basit haliyle, aradığınızı ve yerine ne koymak istediğinizi belirtmeniz yeterlidir.
| 123 | Alt TestDeğiştir()Sheets("Sayfa1").UsedRange.Replace What:="Light & Heat", Replace:="L & H"Alt Bitiş |
Find yönteminin yalnızca eşleşen değerin ilk örneğini döndüreceğini, Replace yönteminin ise belirtilen tüm aralıkta çalıştığını ve eşleşme bulduğu her şeyi değiştirdiğini unutmayın.
Burada gösterilen değiştirme kodu, UseRange nesnesi tarafından tanımlanan tüm hücre aralığı boyunca her "Light & Heat" örneğini "L & H" ile değiştirecektir.
VBA Metin Dizesindeki Metni Bulmak veya Değiştirmek için VBA'yı Kullanma
Yukarıdaki örnekler, Excel verileriyle etkileşim kurmak için VBA kullanırken harika çalışır. Ancak, VBA dizeleriyle etkileşim kurmak için INSTR ve REPLACE gibi yerleşik VBA İşlevlerini kullanabilirsiniz.
kullanabilirsiniz INSTR İşlevi daha uzun bir dize içindeki bir metin dizesini bulmak için.
| 123 | Alt TestInstr()MsgBox InStr("Bu MyText dizesidir", "MyText")Alt Bitiş |
Bu örnek kod, aranacak dizede 'MyText'in bulunduğu sayı konumu olan 9 değerini döndürür.
Büyük/küçük harfe duyarlı olduğunu unutmayın. Eğer 'MyText' tamamen küçük harf ise, o zaman 0 değeri döndürülür, bu da arama dizesinin bulunamadığını gösterir. Aşağıda, büyük/küçük harf duyarlılığının nasıl devre dışı bırakılacağını tartışacağız.
INSTR - Başlat
Kullanılabilir iki isteğe bağlı parametre daha vardır. Arama için başlangıç noktasını belirleyebilirsiniz:
| 1 | MsgBox InStr(9, "Bu MyText dizesidir", "MyText") |
Başlangıç noktası 9 olarak belirtilir, bu nedenle yine 9 döndürür. Başlangıç noktası 10 ise, başlangıç noktası çok ileride olacağından 0 (eşleşme yok) döndürür.
INSTR - Büyük/küçük harf duyarlılığı
Ayrıca bir Karşılaştırma parametresi de ayarlayabilirsiniz. vbBinaryCompare veya vbTextCompare. Bu parametreyi ayarlarsanız, deyimin bir başlangıç parametresi değerine sahip olması gerekir.
- vbBinaryCompare - Büyük/küçük harfe duyarlı (Varsayılan)
- vbTextCompare - Büyük küçük harf duyarlı değil
| 1 | MsgBox InStr(1, "Bu MyText dizesidir", "mytext", vbTextCompare) |
Arama metni küçük harfle yazılmış olsa bile, bu ifade yine 9 döndürür.
Büyük/küçük harf duyarlılığını devre dışı bırakmak için, kod modülünüzün üst kısmında Metni Karşılaştır Seçeneği'ni de belirtebilirsiniz.
VBA Değiştirme İşlevi
Bir dizedeki karakterleri kodunuzdaki farklı metinlerle değiştirmek istiyorsanız, bunun için Replace yöntemi idealdir:
| 123 | Alt TestDeğiştir()MsgBox Replace("Bu MyText dizesidir", "MyText", "My Text")Alt Bitiş |
Bu kod, 'MyText'i' 'My Text' ile değiştirir. İkili karşılaştırma varsayılan olduğundan, arama dizesinin büyük/küçük harfe duyarlı olduğunu unutmayın.
Diğer isteğe bağlı parametreleri de ekleyebilirsiniz:
- Başlangıç - değiştirmenin başlaması gereken ilk dizideki konumu tanımlar. Find yönteminden farklı olarak, Start parametresi tarafından tanımlanan karakter numarasından başlayarak kesilmiş bir dize döndürür.
- Saymak - yapılacak değiştirme sayısını tanımlar. Varsayılan olarak Değiştir, bulunan arama metninin her örneğini değiştirir, ancak Count parametresini 1 olarak ayarlayarak bunu tek bir değiştirmeyle sınırlayabilirsiniz.
- Karşılaştırmak - Find yönteminde olduğu gibi, kullanarak ikili arama veya metin araması belirtebilirsiniz. vbBinaryCompare veya vbTextCompare. İkili dosya büyük/küçük harf duyarlıdır ve metin büyük/küçük harfe duyarlı değildir
| 1 | MsgBox Replace("Bu MyText dizesidir (mytext)", "MyText", "My Text", 9, 1, vbTextCompare) |
Bu kod, 'Metin dizem (mytext)' değerini döndürür. Bunun nedeni, verilen başlangıç noktasının 9 olması, dolayısıyla yeni döndürülen dizenin 9 karakterinden başlamasıdır. Count parametresi 1 olarak ayarlandığından sadece ilk 'MyText' değiştirildi.
Değiştir yöntemi, kesme işareti içeren kişilerin adları gibi sorunları çözmek için idealdir; O'Flynn. Bir dize değeri tanımlamak için tek tırnak kullanıyorsanız ve bir kesme işareti varsa, kod kesme işaretini dizenin sonu olarak yorumlayacağından ve dizenin geri kalanını tanımayacağından bu bir hataya neden olur.
Kesme işaretini hiçbir şey olmadan değiştirmek ve tamamen kaldırmak için Replace yöntemini kullanabilirsiniz.
