אתמול קיבלתי מייל מאחד הקוראים שלי - יוני.
היא רצתה לדעת כיצד להחיל פורמט גופן מודגש על חלק מסוים של מחרוזת בתוך תא. לדוגמה, החילו את הפורמט הנועז על המילה 'שלום' בלבד מ'שלום עולם '.
והיא רצתה לעשות זאת עבור מאות תאים בבת אחת.
מכיוון שאין פונקציונליות מובנית ב- Excel שיכולה לעשות זאת, יצרתי מאקרו פשוט המשתמש ב- פונקציית VBA InStr של Excel (תראה כיצד לעשות זאת בדוגמה 4 במדריך זה).
אך ראשית, בואו נראה כיצד פועלת פונקציית ה- VBA InStr של Excel!
פונקציית InStr VBA של Excel
במדריך זה אסביר את השימוש בפונקציית InStr ב- Excel VBA ואראה כמה דוגמאות מעשיות בהן ניתן להשתמש בה.
פונקציית InStr VBA של Excel - מבוא
הפונקציה InStr מוצאת את המיקום של מחרוזת משנה מסוימת בתוך המחרוזת ומחזירה את המיקום הראשון של הופעתה.
לדוגמה, אם ברצונך למצוא את המיקום של 'x' ב- 'Excel', השימוש בפונקציה Excel VBA InStr יחזיר 2.
תחביר של פונקציית InStr
InStr ([התחל], מחרוזת 1, מחרוזת 2, [השווה])
- [הַתחָלָה] - (ארגומנט אופציונלי) זהו ערך שלם המספר לפונקציית InStr את מיקום ההתחלה שממנו עליו להתחיל לחפש. לדוגמה, אם אני רוצה שהחיפוש יתחיל מההתחלה, אזין את הערך כ- 1. אם אני רוצה שהוא יתחיל עם התו השלישי ואילך, אשתמש 3. אם ישמיט אותו, ערך ברירת המחדל של 1 נלקח.
- מחרוזת 1 - זו המחרוזת הראשית (או מחרוזת האב) שבה אתה רוצה לחפש. לדוגמה, אם אתה מחפש את המיקום של x ב- Excel, מחרוזת 1 תהיה "Excel".
- מחרוזת 2 - זוהי מחרוזת המשנה שאתה מחפש. לדוגמה, אם אתה מחפש את המיקום של x ב- Excel, מחרוזת 2 תהיה x.
- [לְהַשְׁווֹת] - (ארגומנט אופציונלי) תוכל לציין אחד משלושת הערכים הבאים לארגומנט [השווה]:
- vbBinaryCompare - זה יעשה השוואה בין תווים לאופי. לדוגמה, אם אתה מחפש 'x' ב- 'Excel', הוא יחזיר 2, אך אם אתה מחפש 'X' ב- 'Excel', הוא יחזיר 0 כמו X באותיות גדולות. אתה יכול גם להשתמש ב 0 במקום vbBinaryCompare. אם הארגומנט [השווה] מושמט, זהו נתון כברירת מחדל.
- vbTextCompare - זה יעשה השוואה טקסטואלית. לדוגמה, אם תחפש 'x' או 'X' ב- Excel, הוא יחזיר 2 בשני המקרים. טענה זו מתעלמת מכתב האותיות. תוכל גם להשתמש ב- 1 במקום ב- vbTextCompare.
- vbDatabaseCompare - זה משמש עבור Microsoft Access בלבד. הוא משתמש במידע שבמסד הנתונים לביצוע ההשוואה. תוכל גם להשתמש ב -2 במקום vbDatabaseCompare.
הערות נוספות על פונקציית InStr VBA של Excel:
- InStr היא פונקציית VBA ולא פונקציה של גליון עבודה. המשמעות היא שאינך יכול להשתמש בו בתוך גליון העבודה.
- אם מחרוזת 2 (שהיא מחרוזת המשנה שאת מיקומה אתה מחפש) ריקה, הפונקציה תחזיר את הערך של הארגומנט [התחל].
- אם הפונקציה InStr לא יכולה למצוא את מחרוזת המשנה בתוך המחרוזת הראשית, היא תחזיר 0.
כעת נסתכל על דוגמה מסוימת לשימוש בפונקציית InStr VBA של Excel
דוגמא 1 - מציאת המיקום מההתחלה
בדוגמה זו, אשתמש בפונקציית InStr כדי למצוא את המיקום של 'V' ב- 'Excel VBA' מההתחלה.
הקוד לכך יהיה:
תת FindFromBeginning () עמדה עמומה כמיקום שלם = InStr (1, "Excel VBA", "V", vbBinaryCompare) מיקום MsgBox End Sub
בעת הפעלת קוד זה, הוא יציג תיבת הודעות עם הערך 7, שהוא המיקום של 'V' במחרוזת 'Excel VBA'.
דוגמה 2 - מציאת המיקום מתחילת המילה השנייה
נניח, אני רוצה למצוא את המיקום של 'ה' במשפט - 'השועל החום המהיר קופץ מעל הכלב העצל'
עם זאת, אני רוצה שהחיפוש יתחיל במילה השנייה ואילך.
במקרה זה, עלינו לשנות את הארגומנט [התחל] כדי לוודא שהוא מציין את המיקום שממנו מתחילה המילה השנייה.
הנה הקוד שיעשה זאת:
Sub FindFromSecondWord () עמדה עמומה כמיקום שלם = InStr (4, "השועל החום המהיר קופץ מעל הכלב העצל", "ה", vbBinaryCompare) מיקום MsgBox End Sub
קוד זה יציג את תיבת ההודעות עם הערך 32 כפי שציינו את עמדת ההתחלה כ- 4. מכאן שהוא מתעלם מה- 'הראשון' ומוצא את ה' -'השני במשפט.
אם אתה רוצה להפוך אותו לדינאמי יותר, תוכל לשפר את הקוד כך שיתעלם אוטומטית מהמילה הראשונה.
להלן הקוד המשופר שיעשה זאת:
Sub FindFromSecondWord () Dim StartPosition כשלם עמדה עמומה כמספר שלם StartPosition = InStr (1, "השועל החום המהיר קופץ מעל הכלב העצל", "", vbBinaryCompare) Position = InStr (StartPosition, "השועל החום המהיר קופץ על העצלנים dog "," the ", vbBinaryCompare) MsgBox Position End Sub
קוד זה מוצא תחילה את המיקום של תו רווח ושומר אותו במשתנה StartingPosition.
לאחר מכן הוא משתמש במשתנה זה כעמדת ההתחלה כדי לחפש את המילה 'ה'.
מכאן שהוא מחזיר 32 (שהיא עמדת ההתחלה של 'ה' אחרי המילה הראשונה).
דוגמה 3 - מציאת המיקום של @ בכתובת דוא"ל
באפשרותך ליצור פונקציה מותאמת אישית כדי למצוא את המיקום של @ בכתובת דוא"ל באמצעות הפונקציה Excel VBA InStr.
להלן הקוד ליצירת הפונקציה המותאמת אישית:
פונקציה FindPosition (Ref כטווח) כמספר שלם עמום כמיקום שלם = InStr (1, Ref, "@") FindPosition = מיקום סיום פונקציה
כעת תוכל להשתמש בפונקציה מותאמת אישית זו כמו כל פונקציה אחרת של גליון עבודה. זה ייקח הפניה לתא כקלט וייתן לך את המיקום של @ בו.
באופן דומה תוכל ליצור פונקציה מותאמת אישית כדי למצוא את המיקום של כל מחרוזת משנה בתוך המחרוזת הראשית.
דוגמה 4 - הדגשת חלק מהמחרוזת בתוך תאים
זוהי השאילתה שנשאלה על ידי יוני (הקורא שלי שגם נתן לי השראה לכתוב הדרכה זו).
להלן נתונים לדוגמה בפורמט שיוני שלח לי:
שאלתה הייתה להפוך את המספרים מחוץ לסוגר מודגשים.
הנה הקוד שיצרתי שעושה זאת:
תת מודגש () Dim rCell כטווח Dim Char כמו שלם עבור כל rCell בבחירה CharCount = Len (rCell) Char = InStr (1, rCell, "(") rCell.Characters (1, Char - 1). Font.Bold = True Next rCell End Sub
הקוד לעיל משתמש בלולאה For Every כדי לעבור בין כל אחד מהתאים שבבחירה. הוא מזהה את המיקום של תו סוגר הפתיחה באמצעות הפונקציה InStr. לאחר מכן הוא משנה את גופן הטקסט לפני הסוגר.
כדי להשתמש בקוד זה, עליך להעתיק ולהדביק מודול בעורך VB.
לאחר שהעתקת הדבקת הקוד, בחר את התאים שבהם ברצונך לבצע עיצוב זה והפעל את המאקרו (כפי שמוצג להלן).
אתה עשוי גם לאהוב את מדריכי ה- VBA הבאים של Excel:
- פונקציית Excel VBA SPLIT.
- פונקציית VBA TRIM.
- המדריך האולטימטיבי ללולאות VBA של Excel.
- מדריך למתחילים לשימוש בלולאה הבאה ב- Excel VBA.
- כיצד ליצור ולהשתמש בתוסף Excel.
- כיצד לשלב חוברות עבודה מרובות לחוברת עבודה אחת של Excel.
- כיצד לספור תאים צבעוניים ב- Excel.
- דוגמאות שימושיות למאקרו VBA של Excel למתחילים.
- כיצד למיין נתונים ב- Excel באמצעות VBA (מדריך שלב אחר שלב)
