עבודה עם תאים וטווחים ב- Excel VBA (בחירה, העתקה, העברה, עריכה)

בעת עבודה עם Excel, רוב זמנך מתבזבז באזור גליון העבודה - התמודדות עם תאים וטווחים.

ואם אתה רוצה להפוך את העבודה שלך ב- Excel לאוטומטית באמצעות VBA, עליך לדעת כיצד לעבוד עם תאים וטווחים באמצעות VBA.

יש הרבה דברים שונים שאתה יכול לעשות עם טווחים ב- VBA (כגון בחירה, העתקה, העברה, עריכה וכו ').

אז כדי לכסות את הנושא הזה, אפרק את ההדרכה הזו למקטעים ואראה לכם כיצד לעבוד עם תאים וטווחים ב- Excel VBA באמצעות דוגמאות.

בואו נתחיל.

כל הקודים שאני מזכיר במדריך זה צריכים להיות ממוקמים בעורך VB. עבור לסעיף 'היכן לשים את קוד VBA' כדי לדעת כיצד הוא פועל.

אם אתה מעוניין ללמוד VBA בדרך הקלה, בדוק את שלי הדרכה מקוונת ב- Excel VBA.

בחירת תא / טווח ב- Excel באמצעות VBA

כדי לעבוד עם תאים וטווחים ב- Excel באמצעות VBA, אינך צריך לבחור אותו.

ברוב המקרים עדיף שלא תבחרו תאים או טווחים (כפי שנראה).

למרות זאת, חשוב שתעבור על סעיף זה ותבין כיצד הוא פועל. זה יהיה מכריע בלימוד ה- VBA שלך והרבה מושגים שיכוסו כאן ישמשו לאורך כל הדרכה זו.

אז נתחיל בדוגמא מאוד פשוטה.

בחירת תא בודד באמצעות VBA

אם ברצונך לבחור תא בודד בגיליון הפעיל (נניח A1), תוכל להשתמש בקוד שלהלן:

Sub SelectCell () טווח ("A1"). בחר Sub Sub

הקוד לעיל כולל את החלק 'חובה' ו'סיום תת 'וחובה שורת קוד שבוחרת את התא A1.

טווח ("A1") אומר ל- VBA את כתובת התא שאליו ברצוננו להתייחס.

בחר היא שיטה של ​​אובייקט הטווח ובוחרת את התאים/הטווח שצוין באובייקט טווח. יש לכלול את הפניות לתאים במרכאות כפולות.

קוד זה יציג שגיאה במקרה שגיליון תרשים הוא גיליון פעיל. דף תרשים מכיל תרשימים ואינו נמצא בשימוש נרחב. מכיוון שאין בו תאים/טווחים, הקוד לעיל אינו יכול לבחור אותו ויציג בסופו של דבר שגיאה.

שים לב שמכיוון שברצונך לבחור את התא בגיליון הפעיל, עליך רק לציין את כתובת התא.

אבל אם אתה רוצה לבחור את התא בגיליון אחר (נניח גיליון 2), עליך להפעיל תחילה את גיליון 2 ולאחר מכן לבחור את התא שבו.

Sub SelectCell () גליונות עבודה ("גיליון 2"). הפעל טווח ("A1"). בחר סיום תת

באופן דומה, תוכל גם להפעיל חוברת עבודה, ולאחר מכן להפעיל גליון עבודה ספציפי בו, ולאחר מכן לבחור תא.

תת SelectCell () חוברות עבודה ("Book2.xlsx"). גליונות עבודה ("Sheet2"). הפעל טווח ("A1"). בחר End Sub Sub 

שים לב שכאשר אתה מתייחס לחוברות עבודה, עליך להשתמש בשם המלא יחד עם סיומת הקובץ (.xlsx בקוד לעיל). במקרה שחוברת העבודה מעולם לא נשמרה, אינך צריך להשתמש בסיומת הקובץ.

כעת, דוגמאות אלו אינן שימושיות במיוחד, אך בהמשך תראה במדריך זה כיצד נוכל להשתמש באותם מושגים להעתיק ולהדביק תאים ב- Excel (באמצעות VBA).

בדיוק כפי שאנו בוחרים תא, אנו יכולים גם לבחור טווח.

במקרה של טווח, זה יכול להיות טווח גודל קבוע או טווח גודל משתנה.

בטווח גודל קבוע, היית יודע עד כמה הטווח גדול ותוכל להשתמש בגודל המדויק בקוד ה- VBA שלך. אבל עם טווח בגודל משתנה, אין לך מושג כמה הטווח גדול ואתה צריך להשתמש במעט קסם של VBA.

בוא נראה כיצד לעשות זאת.

בחירת טווח מידות תיקון

להלן הקוד שיבחר את הטווח A1: D20.

טווח Sub SelectRange () ("A1: D20"). בחר End Sub 

דרך נוספת לעשות זאת היא באמצעות הקוד שלהלן:

טווח Sub SelectRange () ("A1", "D20"). בחר End Sub

הקוד לעיל לוקח את כתובת התא השמאלית העליונה (A1) ואת כתובת התא הימנית התחתונה (D20) ובוחר את כל הטווח. טכניקה זו הופכת שימושית כאשר אתה עובד עם טווחים בגודל משתנה (כפי שנראה כאשר נכס הקצה יכוסה בהמשך הדרכה זו).

אם אתה רוצה שהבחירה תתקיים בחוברת עבודה אחרת או בגליון עבודה אחר, עליך לספר ל- VBA את השמות המדויקים של האובייקטים האלה.

לדוגמה, הקוד שלהלן יבחר את הטווח A1: D20 בגליון העבודה Sheet2 בחוברת העבודה Book2.

Sub SelectRange () חוברות עבודה ("Book2.xlsx"). גליונות עבודה ("Sheet1"). הפעל טווח ("A1: D20"). בחר End Sub Sub

עכשיו, מה אם אתה לא יודע כמה שורות יש. מה אם אתה רוצה לבחור את כל התאים שיש בהם ערך.

במקרים אלה, עליך להשתמש בשיטות המוצגות בסעיף הבא (על בחירת טווח בגודל משתנה).

בחירת טווח בגודל משתנה

ישנן דרכים שונות בהן ניתן לבחור מגוון תאים. השיטה שתבחר תלויה באופן שבו הנתונים בנויים.

בחלק זה אעסוק בכמה טכניקות שימושיות שהן שימושיות מאוד כאשר אתה עובד עם טווחים ב- VBA.

בחר שימוש במאפיין CurrentRange

במקרים שבהם אינך יודע כמה שורות/עמודות מכילות את הנתונים, תוכל להשתמש במאפיין CurrentRange של האובייקט Range.

המאפיין CurrentRange מכסה את כל התאים המלאים הרציפים בטווח נתונים.

להלן הקוד שיבחר את האזור הנוכחי המכיל את התא A1.

Sub SelectCurrentRegion () טווח ("A1"). CurrentRegion. בחר End Sub

השיטה לעיל טובה כאשר יש לך את כל הנתונים כטבלה ללא שורות/עמודות ריקות.

אבל במקרה שיש לך שורות/עמודות ריקות בנתונים שלך, הוא לא יבחר את אלה שאחרי השורות/העמודות הריקות. בתמונה למטה, קוד CurrentRegion בוחר נתונים עד שורה 10 מכיוון ששורה 11 ריקה.

במקרים כאלה, ייתכן שתרצה להשתמש במאפיין UsedRange של אובייקט גליון העבודה.

בחר Using UsedRange Property

UsedRange מאפשר לך להתייחס לכל התאים שהשתנו.

אז הקוד שלהלן יבחר את כל התאים המשומשים בגיליון הפעיל.

Sub SelectUsedRegion () ActiveSheet.UsedRange.Select Sub Sub

שים לב שאם יש לך תא רחוק שהיה בשימוש, הוא ייחשב לפי הקוד לעיל וכל התאים עד שנבחר התא המשומש הזה.

בחר שימוש במאפיין הקצה

עכשיו, החלק הזה באמת שימושי.

המאפיין End מאפשר לך לבחור את התא שהתמלא לאחרונה. זה מאפשר לך לחקות את האפקט של מקש חץ למטה/למעלה או למקשי שליטה ימינה/שמאלה.

בואו ננסה להבין זאת באמצעות דוגמה.

נניח שיש לך מערך נתונים כפי שמוצג להלן וברצונך לבחור במהירות את התאים שהתמלאו לאחרונה בעמודה A.

הבעיה כאן היא שהנתונים יכולים להשתנות ואתה לא יודע כמה תאים מלאים. אם עליך לעשות זאת באמצעות מקלדת, תוכל לבחור בתא A1 ולאחר מכן השתמש במקש + מקש חץ למטה, והוא יבחר את התא המלא האחרון בעמודה.

כעת נראה כיצד לעשות זאת באמצעות VBA. טכניקה זו שימושית כאשר אתה רוצה לקפוץ במהירות לתא המלא האחרון בטור בגודל משתנה

Sub GoToLastFilledCell () טווח ("A1"). סוף (xlDown). בחר End Sub

הקוד לעיל יקפוץ לתא המלא האחרון בעמודה A.

באופן דומה, אתה יכול להשתמש בסוף (xlToRight) כדי לקפוץ לתא המלא האחרון ברציפות.

Sub GoToLastFilledCell () טווח ("A1"). סוף (xlToRight). בחר End Sub

עכשיו, מה אם אתה רוצה לבחור את העמודה כולה במקום לקפוץ לתא המלא האחרון.

אתה יכול לעשות זאת באמצעות הקוד שלהלן:

Sub SelectFilledCells () טווח ("A1", טווח ("A1"). סוף (xlDown)). בחר End Sub Sub

בקוד לעיל, השתמשנו בהפניה הראשונה והאחרונה של התא שעלינו לבחור. לא משנה כמה תאים מלאים יש, הקוד לעיל יבחר הכל.

זכור את הדוגמה למעלה שבה בחרנו את הטווח A1: D20 באמצעות שורת הקוד הבאה:

טווח ("A1 ″," D20 ")

כאן A1 היה התא השמאלי העליון ו- D20 היה התא הימני התחתון בטווח. אנו יכולים להשתמש באותה היגיון בבחירת טווחים בגודל משתנה. אך מכיוון שאיננו יודעים את הכתובת המדויקת של התא הימני התחתון, השתמשנו במאפיין End כדי לקבל אותו.

בטווח ("A1", טווח ("A1"). סוף (xlDown)), "A1" מתייחס לתא הראשון וטווח ("A1"). End (xlDown) מתייחס לתא האחרון. מכיוון שסיפקנו את שני ההפניות, שיטת Select בוחרת את כל התאים בין שתי ההפניות הללו.

באופן דומה, תוכל גם לבחור מערך נתונים שלם הכולל מספר שורות ועמודות.

הקוד שלהלן יבחר את כל השורות/העמודות המלאות החל מתא A1.

Sub SelectFilledCells () טווח ("A1", טווח ("A1"). סוף (xlDown). סיום (xlToRight)). בחר End Sub Sub

בקוד לעיל, השתמשנו בטווח ("A1"). End (xlDown) .End (xlToRight) כדי לקבל את ההפניה לתא המלא הימני התחתון של מערך הנתונים.

ההבדל בין שימוש ב- CurrentRegion ו- End

אם אתה תוהה מדוע להשתמש במאפיין End כדי לבחור את הטווח המלא כאשר יש לנו את המאפיין CurrentRegion, הרשה לי לספר לך את ההבדל.

עם המאפיין End, תוכל לציין את תא ההתחלה. לדוגמה, אם הנתונים שלך נמצאים ב- A1: D20, אך השורה הראשונה היא כותרות, תוכל להשתמש במאפיין End כדי לבחור את הנתונים ללא הכותרות (באמצעות הקוד להלן).

Sub SelectFilledCells () טווח ("A2", טווח ("A2"). סוף (xlDown). סיום (xlToRight)). בחר End Sub Sub

אבל ה- CurrentRegion יבחר באופן אוטומטי את מערך הנתונים כולו, כולל הכותרות.

עד כה במדריך זה, ראינו כיצד להתייחס למגוון תאים בשיטות שונות.

כעת בואו נראה כמה דרכים שבהן אנו יכולים למעשה להשתמש בטכניקות אלה כדי לבצע קצת עבודה.

העתק תאים / טווחים באמצעות VBA

כפי שציינתי בתחילת הדרכה זו, אין צורך בבחירת תא לביצוע פעולות עליו. תוכלו לראות בחלק זה כיצד להעתיק תאים וטווחים מבלי לבחור אותם אפילו.

נתחיל בדוגמא פשוטה.

העתקת תא בודד

אם אתה רוצה להעתיק את התא A1 ולהדביק אותו בתא D1, הקוד שלהלן יעשה זאת.

Sub CopyCell () טווח ("A1"). טווח העתקה ("D1") סוף Sub

שים לב ששיטת ההעתקה של אובייקט הטווח מעתיקה את התא (ממש כמו Control +C) ומדביקה אותו ביעד שצוין.

בקוד הדוגמא לעיל, היעד מצוין באותה שורה שבה אתה משתמש בשיטת העתקה. אם אתה רוצה להפוך את הקוד שלך לקריא עוד יותר, תוכל להשתמש בקוד שלהלן:

Sub CopyCell () טווח ("A1"). יעד העתקה: = טווח ("D1") סיום תת

הקודים לעיל יעתיקו וידביקו בו את הערך כמו גם עיצוב/נוסחאות.

כפי שאולי כבר שמתם לב, הקוד לעיל מעתיק את התא מבלי לבחור בו. לא משנה היכן אתה נמצא בגליון העבודה, הקוד יעתיק את התא A1 וידביק אותו ב- D1.

כמו כן, שים לב שהקוד לעיל יחליף כל קוד קיים בתא D2. אם אתה רוצה ש- Excel יודיע לך אם כבר יש משהו בתא D1 מבלי להחליף אותו, תוכל להשתמש בקוד שלהלן.

Sub CopyCell () If Range ("D1") "" Then Response = MsgBox ("האם ברצונך להחליף את הנתונים הקיימים", vbYesNo) End If If Response = vbYes Then Range ("A1"). העתק טווח ("D1 ") סוף אם סיום תת

העתקת טווח בגודל תיקון

אם ברצונך להעתיק את A1: D20 ב- J1: M20, תוכל להשתמש בקוד שלהלן:

טווח Sub CopyRange () ("A1: D20"). טווח העתקות ("J1") סוף Sub

בתא היעד, אתה רק צריך לציין את הכתובת של התא השמאלי העליון. הקוד יעתיק אוטומטית את הטווח המועתק ליעד.

אתה יכול להשתמש באותו מבנה כדי להעתיק נתונים מדף אחד לשני.

הקוד שלהלן יעתיק את A1: D20 מהגיליון הפעיל לגיליון 2.

טווח תת העתק () ("A1: D20"). העתק גליונות עבודה ("גיליון 2"). טווח ("A1") סיום תת

האמור לעיל מעתיק את הנתונים מהגיליון הפעיל. לכן וודא שהגיליון המכיל את הנתונים הוא הגיליון הפעיל לפני הפעלת הקוד. ליתר ביטחון, תוכל גם לציין את שם גליון העבודה בעת העתקת הנתונים.

Sub CopyRange () גליונות עבודה ("גיליון 1"). טווח ("A1: D20"). העתק גליונות עבודה ("גיליון 2"). טווח ("A1") סיום תת

הדבר הטוב בקוד לעיל הוא שלא משנה איזה גיליון פעיל, הוא תמיד יעתיק את הנתונים מ- Sheet1 וידביק אותו ב- Sheet2.

תוכל גם להעתיק טווח עם שם באמצעות השם שלו במקום ההפניה.

לדוגמה, אם יש לך טווח בשם "SalesData", תוכל להשתמש בקוד שלהלן כדי להעתיק נתונים אלה ל- Sheet2.

טווח Sub CopyRange () ("SalesData"). העתק גליונות עבודה ("גיליון 2"). טווח ("A1") סיום תת

אם היקף הטווח בשם הוא חוברת העבודה כולה, אינך צריך להופיע בגיליון בעל הטווח בשם לשם הפעלת קוד זה. מכיוון שהטווח הנקוב מיועד לחוברת העבודה, תוכל לגשת אליו מכל גיליון באמצעות קוד זה.

אם יש לך טבלה בשם טבלה 1, תוכל להשתמש בקוד שלהלן כדי להעתיק אותה לגיליון 2.

טווח תת העתק () טווח ("טבלה 1 [#כל]"). העתק גליונות עבודה ("גיליון 2"). טווח ("A1") סיום תת

תוכל גם להעתיק טווח לחוברת עבודה אחרת.

בדוגמה הבאה, אני מעתיק את טבלת Excel (טבלה 1), לתוך חוברת העבודה Book2.

טווח Sub CopyCurrentRegion () ("טבלה 1 [#כל]"). העתק חוברות עבודה ("Book2.xlsx"). גליונות עבודה ("גיליון 1"). טווח ("A1") סיום תת

קוד זה יפעל רק אם חוברת העבודה כבר פתוחה.

העתקת טווח בגודל משתנה

אחת הדרכים להעתיק טווחים בגודל משתנה היא להמיר אותם לטווחים בשם או טבלת Excel ולהשתמש בקודים כפי שמוצג בסעיף הקודם.

אך אם אינך יכול לעשות זאת, תוכל להשתמש במאפיין CurrentRegion או במאפיין End של אובייקט הטווח.

הקוד שלהלן יעתיק את האזור הנוכחי בגיליון הפעיל וידביק אותו בגיליון 2.

Sub CopyCurrentRegion () טווח ("A1"). CurrentRegion.Copy גליונות עבודה ("גיליון 2"). טווח ("A1") סיום תת

אם ברצונך להעתיק את העמודה הראשונה של מערך הנתונים שלך עד לתא המלא האחרון ולהדביק אותו ב- Sheet2, תוכל להשתמש בקוד שלהלן:

Sub CopyCurrentRegion () טווח ("A1", טווח ("A1"). סוף (xlDown)). העתק גליונות עבודה ("גיליון 2"). טווח ("A1") קצה תת

אם אתה רוצה להעתיק את השורות כמו גם את העמודות, אתה יכול להשתמש בקוד שלהלן:

Sub CopyCurrentRegion () טווח ("A1", טווח ("A1"). סוף (xlDown). סוף (xlToRight)). העתק גליונות עבודה ("גיליון 2"). טווח ("A1") סיום תת

שים לב שכל הקודים הללו אינם בוחרים את התאים בעת ביצועם. באופן כללי, תמצא רק קומץ מקרים שבהם אתה צריך לבחור תא/טווח לפני שאתה עובד עליו.

הקצאת טווחים למשתני אובייקט

עד כה השתמשנו בכתובת המלאה של התאים (כגון חוברות עבודה ("Book2.xlsx"). גליונות עבודה ("גיליון 1"). טווח ("A1")).

כדי להפוך את הקוד לניהול יותר שלך, תוכל להקצות טווחים אלה למשתני אובייקטים ולאחר מכן להשתמש במשתנים אלה.

לדוגמה, בקוד שלהלן, הקצתי את המקור וטווח היעד למשתני אובייקטים ולאחר מכן השתמשתי במשתנים אלה כדי להעתיק נתונים מטווח אחד למשנהו.

Sub CopyRange () Dim SourceRange כטווח Dim DestinationRange As Range הגדר SourceRange = גליונות עבודה ("גיליון 1"). טווח ("A1: D20") הגדר DestinationRange = גליונות עבודה ("גיליון 2"). טווח ("A1") SourceRange.Copy DestinationRange סיום תת

אנו מתחילים בהכרזה על המשתנים כאובייקטים של טווח. לאחר מכן אנו מקצים את הטווח למשתנים אלה באמצעות משפט Set. לאחר שהטווח הוקצה למשתנה, תוכל פשוט להשתמש במשתנה.

הזן נתונים בתא הריק הבא (באמצעות תיבת קלט)

תוכל להשתמש בתיבות הקלט כדי לאפשר למשתמש להזין את הנתונים.

לדוגמה, נניח שיש לך את הנתונים המפורטים להלן וברצונך להזין את רשומת המכירות, תוכל להשתמש בתיבת הקלט ב- VBA. באמצעות קוד, אנו יכולים לוודא שהוא ממלא את הנתונים בשורה הריקה הבאה.

תת EnterData () Dim RefRange כטווח Set RefRange = טווח ("A1"). סוף (xlDown) .Offset (1, 0) הגדר ProductCategory = RefRange.Offset (0, 1) הגדר כמות = RefRange.Offset (0, 2 ) הגדר סכום = RefRange.Offset (0, 3) RefRange.Value = RefRange.Offset (-1, 0) .Value + 1 ProductCategory.Value = InputBox ("קטגוריית מוצר") Quantity.Value = InputBox ("כמות") Amount.Value = InputBox ("סכום") Sub Sub

הקוד לעיל משתמש בתיבת קלט VBA כדי לקבל את התשומות מהמשתמש ולאחר מכן מזין את התשומות לתאים שצוין.

שים לב שלא השתמשנו בהפניות תא מדויקות. במקום זאת, השתמשנו במאפיין סיום וקיזוז כדי למצוא את התא הריק האחרון ולמלא את הנתונים בו.

הקוד הזה רחוק מלהיות שמיש. לדוגמה, אם תזין מחרוזת טקסט כאשר תיבת הקלט מבקשת כמות או כמות, תבחין כי Excel מאפשר זאת. תוכל להשתמש בתנאי If כדי לבדוק אם הערך מספרי או לא ולאחר מכן לאפשר זאת בהתאם.

לולאה בין תאים / טווחים

עד כה יכולנו לראות כיצד לבחור, להעתיק ולהזין את הנתונים בתאים ובטווחים.

בחלק זה נראה כיצד לעבור בין קבוצת תאים/שורות/עמודות בטווח. זה יכול להיות שימושי כאשר אתה רוצה לנתח כל תא ולבצע פעולה כלשהי על סמך זה.

לדוגמה, אם אתה רוצה להדגיש כל שורה שלישית בבחירה, עליך לדפדף ולבדוק את מספר השורה. באופן דומה, אם אתה רוצה להדגיש את כל התאים השליליים על ידי שינוי צבע הגופן לאדום, עליך לעבור ולנתח את ערך כל תא.

להלן הקוד שיחלף בין השורות בתאים שנבחרו ויבליט שורות חלופיות.

Sub HighlightAlternateRows () Dim Myrange כטווח Dim Myrow כטווח Set Myrange = בחירה לכל Myrow ב- Myrange.Rows אם Myrow.Row Mod 2 = 0 ואז Myrow.Interior.Color = vbCyan End If הבא Myrow End Sub

הקוד לעיל משתמש בפונקציה MOD כדי לבדוק את מספר השורות בבחירה. אם מספר השורות שווה, הוא מודגש בצבע ציאן.

הנה דוגמה נוספת שבה הקוד עובר בכל תא ומדגיש את התאים שיש בהם ערך שלילי.

Sub HighlightAlternateRows () Dim Myrange כטווח Dim Mycell as Range Set Myrange = בחירה עבור כל Mycell ב- Myrange If Mycell <0 ואז Mycell.Interior.Color = vbRed End If הבא Mycell Sub Sub

שים לב שאתה יכול לעשות את אותו הדבר באמצעות עיצוב מותנה (שהוא דינאמי ודרך טובה יותר לעשות זאת). דוגמה זו נועדה רק להראות לך כיצד לולאה עובדת עם תאים וטווחים ב- VBA.

היכן לשים את קוד ה- VBA

תוהה לאן קוד VBA הולך בחוברת העבודה שלך ב- Excel?

ל- Excel יש תומך תומך ב- VBA שנקרא עורך VBA. עליך להעתיק ולהדביק את הקוד בחלון קוד המודול של עורך VB.

להלן השלבים לביצוע פעולה זו:

1. עבור לכרטיסייה מפתחים.
2. לחץ על האפשרות Visual Basic. פעולה זו תפתח את עורך ה- VB בקצה האחורי.
3. בחלונית סייר הפרויקט בעורך VB, לחץ באמצעות לחצן העכבר הימני על כל אובייקט עבור חוברת העבודה שבה ברצונך להכניס את הקוד.אם אינך רואה את סייר הפרויקטים, עבור לכרטיסייה תצוגה ולחץ על סייר הפרוייקטים.
4. עבור אל הוספה ולחץ על מודול. זה יכניס אובייקט מודול לחוברת העבודה שלך.
5. העתק והדבק את הקוד בחלון המודול.