एक्सेल वीबीए रेंज और सेल

VBA में रेंज और सेल

एक्सेल स्प्रेडशीट सेल में डेटा स्टोर करती है। कोशिकाओं को पंक्तियों और स्तंभों में व्यवस्थित किया जाता है। प्रत्येक सेल को उसकी पंक्ति और स्तंभ के प्रतिच्छेदन बिंदु (Exs। B3 या R3C2) द्वारा पहचाना जा सकता है।

एक एक्सेल रेंज एक या एक से अधिक सेल को संदर्भित करता है (उदा। A3:B4)

सेल का पता

A1 संकेतन

A1 नोटेशन में, एक सेल को उसके कॉलम लेटर (A से XFD तक) और उसके बाद उसकी पंक्ति संख्या (1 से 1,048,576 तक) द्वारा संदर्भित किया जाता है।

वीबीए में आप का उपयोग करके किसी भी सेल को संदर्भित कर सकते हैं रेंज ऑब्जेक्ट.

123456789

' वर्तमान में सक्रिय शीट पर सेल B4 का संदर्भ लेंMsgBox रेंज ("B4")'डेटा' नाम की शीट पर सेल B4 देखेंMsgBox वर्कशीट ("डेटा")। रेंज ("B4")' किसी अन्य ओपन वर्कबुक में 'डेटा' नाम की शीट पर सेल बी 4 देखें'माई डेटा' नाम दिया गयाMsgBox वर्कबुक ("माई डेटा")। वर्कशीट्स ("डेटा")। रेंज ("बी 4")

R1C1 संकेतन

R1C1 नोटेशन में एक सेल को R और उसके बाद रो नंबर और फिर अक्षर 'C' और उसके बाद कॉलम नंबर द्वारा संदर्भित किया जाता है। उदाहरण के लिए R1C1 अंकन में B4 को R4C2 द्वारा संदर्भित किया जाएगा। वीबीए में आप का उपयोग करते हैं सेल ऑब्जेक्ट R1C1 संकेतन का उपयोग करने के लिए:

12	' सेल R[6]C[4] यानी D6 . का संदर्भ लेंसेल(६, ४) = "डी६"

कोशिकाओं की रेंज

A1 संकेतन

एक से अधिक सेल को संदर्भित करने के लिए प्रारंभिक सेल पते और अंतिम सेल पते के बीच ":" का उपयोग करें। निम्नलिखित सभी कोशिकाओं को A1 से D10 तक संदर्भित करेगा:

1	रेंज ("ए 1: डी 10")

R1C1 संकेतन

एक से अधिक सेल को संदर्भित करने के लिए प्रारंभिक सेल पते और अंतिम सेल पते के बीच "," का उपयोग करें। निम्नलिखित सभी कोशिकाओं को A1 से D10 तक संदर्भित करेगा:

1	रेंज (सेल (1, 1), सेल (10, 4))

प्रकोष्ठों को लिखना

किसी सेल या कोशिकाओं के सन्निहित समूह के लिए मान लिखने के लिए, सरल श्रेणी का संदर्भ लें, एक = चिह्न लगाएं और फिर संग्रहीत करने के लिए मान लिखें:

12345678910

' पता F6 के साथ सेल में F5 स्टोर करेंरेंज ("F6") = "F6"E6 को एड्रेस R[6]C[5] यानी E6 के साथ सेल में स्टोर करेंसेल(६, ५) = "ई६"स्टोर A1:D10 रेंज A1:D10 . मेंरेंज ("ए 1: डी 10") = "ए 1: डी 10"' यारेंज (सेल (1, 1), सेल (10, 4)) = "ए 1: डी 10"

सेल से पढ़ना

कोशिकाओं से मूल्यों को पढ़ने के लिए, मूल्यों को संग्रहीत करने के लिए चर को सरल देखें, एक = चिह्न लगाएं और फिर पढ़ने के लिए सीमा देखें:

1234567891011

मंद वैल1मंद वैल2' सेल F6 से पढ़ेंवैल1 = रेंज ("F6")' सेल E6 से पढ़ेंवैल2 = सेल(6, 5)संदेशबॉक्स वैल1संदेशबॉक्स वैल2

नोट: सेल की एक श्रेणी से मूल्यों को संग्रहीत करने के लिए, आपको एक साधारण चर के बजाय एक ऐरे का उपयोग करने की आवश्यकता है।

गैर सन्निहित कोशिकाएं

गैर सन्निहित कक्षों को संदर्भित करने के लिए कक्ष पतों के बीच अल्पविराम का उपयोग करें:

123456

' 10 को सेल A1, A3 और A5 में स्टोर करेंरेंज ("ए 1, ए 3, ए 5") = 10सेल A1:A3 और D1:D3 में 10 स्टोर करें)रेंज ("ए 1: ए 3, डी 1: डी 3") = 10

कोशिकाओं का चौराहा

गैर सन्निहित कक्षों को संदर्भित करने के लिए कक्ष पतों के बीच रिक्त स्थान का उपयोग करें:

123	'कर्नल डी' को डी1:डी10 में स्टोर करें' जो A1:D10 और D1:F10 . के बीच सामान्य हैरेंज ("ए 1: डी 10 डी 1: जी 10") = "कर्नल डी"

सेल या रेंज से ऑफसेट

ऑफ़सेट फ़ंक्शन का उपयोग करके, आप निर्दिष्ट संख्या_ऑफ_रो, और संख्या_ऑफ_कॉलम द्वारा किसी दिए गए रेंज (सेल या सेल के समूह) से संदर्भ को स्थानांतरित कर सकते हैं।

ऑफसेट सिंटेक्स

रेंज। ऑफसेट (number_of_rows, number_of_columns)

सेल से ऑफ़सेट

12345678910111213141516

' सेल A1 से ऑफ़सेट'' सेल को ही देखें' 0 पंक्तियों और 0 स्तंभों को स्थानांतरित करेंरेंज ("ए 1")। ऑफसेट (0, 0) = "ए 1"' 1 पंक्तियों और 0 स्तंभों को स्थानांतरित करेंरेंज ("ए 1")। ऑफसेट (1, 0) = "ए 2"' 0 पंक्तियों और 1 कॉलम को स्थानांतरित करेंरेंज ("ए 1")। ऑफसेट (0, 1) = "बी 1"' 1 पंक्तियों और 1 कॉलम को स्थानांतरित करेंरेंज ("ए 1")। ऑफसेट (1, 1) = "बी 2"' १० पंक्तियों और ५ स्तंभों को ले जाएँरेंज ("ए 1")। ऑफसेट (10, 5) = "एफ 11"

एक सीमा से ऑफसेट

123	' संदर्भ को श्रेणी A1:D4 में 4 पंक्तियों और 4 स्तंभों द्वारा स्थानांतरित करें'नया संदर्भ है E5:H8रेंज ("ए 1: डी 4")। ऑफसेट (4,4) = "ई 5: एच 8"

एक सीमा के लिए संदर्भ सेट करना

एक श्रेणी चर के लिए एक श्रेणी निर्दिष्ट करने के लिए: एक प्रकार की श्रेणी घोषित करें, फिर इसे एक श्रेणी में सेट करने के लिए सेट कमांड का उपयोग करें। कृपया ध्यान दें कि आपको SET कमांड का उपयोग करना चाहिए क्योंकि RANGE एक ऑब्जेक्ट है:

12345678

' एक श्रेणी चर घोषित करेंमेरी रेंज को रेंज के रूप में मंद करेंचर को श्रेणी A1:D4 . पर सेट करेंmyRange = रेंज सेट करें ("ए 1: डी 4")' $A$1:$D$4 . प्रिंट करता हैMsgBox myRange.Address

एक सीमा का आकार बदलें

रेंज ऑब्जेक्ट की रीसाइज विधि संदर्भ रेंज के आयाम को बदल देती है:

1234567

मेरी रेंज को रेंज के रूप में मंद करें'आकार बदलने के लिए रेंजmyRange = रेंज सेट करें ("ए 1: एफ 4")' $A$1:$E$10 . प्रिंट करता हैडीबग करें। myRange प्रिंट करें। आकार बदलें (10, 5)। पता

रिसाइज़्ड रेंज का टॉप-लेफ्ट सेल मूल रेंज के टॉप-लेफ्ट सेल के समान है

सिंटैक्स का आकार बदलें

रेंज। आकार बदलें (number_of_rows, number_of_columns)

ऑफसेट बनाम आकार बदलें

ऑफसेट रेंज के आयामों को नहीं बदलता है, लेकिन पंक्तियों और स्तंभों की निर्दिष्ट संख्या से इसे स्थानांतरित करता है। आकार बदलें मूल श्रेणी की स्थिति नहीं बदलता है, लेकिन आयामों को पंक्तियों और स्तंभों की निर्दिष्ट संख्या में बदल देता है।

शीट में सभी सेल

सेल ऑब्जेक्ट शीट में सभी सेल (1048576 पंक्तियाँ और 16384 कॉलम) को संदर्भित करता है।

12	'कार्यपत्रकों में सभी कक्षों को साफ़ करेंकोशिकाएं। साफ़ करें

प्रयुक्त रेंज

यूज्डरेंज प्रॉपर्टी आपको एक्टिव शीट के ऊपर-बाएं सेल यूज्ड सेल से राइट-बॉटम यूज्ड सेल तक आयताकार रेंज देती है।

1234567

वर्कशीट के रूप में डिम WSसेट ws = एक्टिवशीट' $B$2:$L$14 अगर L2 किसी भी मान वाला पहला सेल है' और L14 किसी भी मान के साथ अंतिम सेल है' सक्रिय पत्रकडीबग.प्रिंट ws.UsedRange.Address

वर्तमान क्षेत्र

CurrentRegion प्रॉपर्टी आपको संदर्भित सेल/रेंज वाले टॉप-लेफ्ट सेल से राइट-बॉटम यूज्ड सेल तक सन्निहित आयताकार रेंज देती है।

1234567891011

मेरी रेंज को रेंज के रूप में मंद करेंmyRange = रेंज सेट करें ("डी 4: एफ 6")' प्रिंट $B$2:$L$14' अगर D4:F16 से B2 और L14 तक भरा हुआ रास्ता हैडिबग। प्रिंट myRange.CurrentRegion.Address' आप एकल आरंभिक सेल का भी उल्लेख कर सकते हैंmyRange = Range("D4") 'प्रिंट्स $B$2:$L$14 . सेट करें

रेंज गुण

आप एक सेल का पता, पंक्ति/स्तंभ संख्या, और पंक्तियों/स्तंभों की संख्या नीचे दिए गए अनुसार प्राप्त कर सकते हैं:

123456789101112131415161718192021

मेरी रेंज को रेंज के रूप में मंद करेंmyRange = रेंज सेट करें ("ए 1: एफ 10")' प्रिंट $A$1:$F$10डीबग करें। myRange.Address प्रिंट करेंmyRange = रेंज सेट करें ("F10")'पंक्ति 10 के लिए 10 प्रिंट करता है'डीबग करें। myRange.Row प्रिंट करें' कॉलम एफ के लिए 6 प्रिंट करता हैडीबग करें। myRange.Column प्रिंट करेंmyRange = रेंज सेट करें ("ई 1: एफ 5")' रेंज में पंक्तियों की संख्या के लिए 5 प्रिंट करता हैडीबग करें। myRange.Rows.Count प्रिंट करें' रेंज में कॉलम की संख्या के लिए 2 प्रिंट करता हैडीबग करें। myRange.Columns.Count प्रिंट करें

पत्रक में अंतिम सेल

आप उपयोग कर सकते हैं पंक्तियाँ।गणना तथा कॉलम।गणना के साथ गुण प्रकोष्ठों शीट पर अंतिम सेल प्राप्त करने के लिए आपत्ति:

1234567891011

' अंतिम पंक्ति संख्या प्रिंट करें' प्रिंट 1048576डीबग करें। प्रिंट करें "शीट में पंक्तियाँ:" और पंक्तियाँ। गणना' अंतिम कॉलम नंबर प्रिंट करें'मुद्रण 16384डीबग करें। प्रिंट करें "पत्रक में कॉलम:" और कॉलम। गणना' अंतिम सेल का पता प्रिंट करें$XFD$1048576 प्रिंट करता हैDebug.Print "शीट में अंतिम सेल का पता:" और सेल (पंक्तियाँ। गणना, कॉलम। गणना)

एक कॉलम में अंतिम बार प्रयुक्त पंक्ति संख्या

END प्रॉपर्टी आपको रेंज में आखिरी सेल में ले जाती है, और एंड (xlUp) आपको उस सेल से पहले इस्तेमाल किए गए सेल तक ले जाती है।

123	मंद अंतिम पंक्ति जितनी लंबी होlastRow = सेल (पंक्तियाँ। गणना, "ए")। अंत (xlUp)। पंक्ति

एक पंक्ति में अंतिम बार प्रयुक्त कॉलम संख्या

123	डिम लास्टकॉल अस लॉन्गlastCol = सेल (1, कॉलम। गणना)। अंत (xlToLeft)। कॉलम

END प्रॉपर्टी आपको रेंज में आखिरी सेल में ले जाती है, और End(xlToLeft) आपको उस सेल से पहले इस्तेमाल किए गए सेल में ले जाती है।

आप मौजूदा सेल के पहले बॉटम या राइट यूज्ड सेल में नेविगेट करने के लिए xlDown और xlToRight प्रॉपर्टीज का भी इस्तेमाल कर सकते हैं।

सेल गुण

सामान्य गुण

यहां आमतौर पर उपयोग किए जाने वाले सेल गुणों को प्रदर्शित करने के लिए कोड दिया गया है

12345678910111213141516171819202122

रेंज के रूप में मंद सेलसेल सेट करें = रेंज ("ए 1")सेल। सक्रिय करेंडीबग.प्रिंट सेल.पता'$A$1 . प्रिंट करेंडिबग.प्रिंट सेल.वैल्यू' प्रिंट 456' पताडिबग.प्रिंट सेल.फॉर्मूला'प्रिंट्स = एसयूएम (सी 2: सी 3)' टिप्पणीडिबग.प्रिंट सेल.टिप्पणी.पाठ' अंदाजडीबग.प्रिंट सेल.शैली'सेल प्रारूप'डिबग.प्रिंट सेल.डिस्प्लेफॉर्मेट.नंबरफॉर्मेट

सेल फ़ॉन्ट

Cell.Font ऑब्जेक्ट में Cell Font के गुण होते हैं:

1234567891011121314151617181920

रेंज के रूप में मंद सेलसेल सेट करें = रेंज ("ए 1")' नियमित, इटैलिक, बोल्ड और बोल्ड इटैलिकcell.Font.FontStyle = "बोल्ड इटैलिक"' के समानसेल। फ़ॉन्ट। बोल्ड = ट्रूसेल। फ़ॉन्ट। इटैलिक = ट्रूफ़ॉन्ट को कूरियर पर सेट करेंcell.Font.FontStyle = "कूरियर"फ़ॉन्ट रंग सेट करेंसेल। फ़ॉन्ट। रंग = वीबीब्लू' यासेल। फ़ॉन्ट। रंग = आरजीबी (255, 0, 0)'फ़ॉन्ट आकार सेट करें'सेल। फ़ॉन्ट। आकार = 20

कॉपी और पेस्ट

सभी चिपकाएं

रेंज/सेल को एक स्थान से दूसरे स्थान पर कॉपी और पेस्ट किया जा सकता है। निम्नलिखित कोड स्रोत श्रेणी के सभी गुणों को गंतव्य श्रेणी में कॉपी करता है (CTRL-C और CTRL-V के बराबर)

1234567

'साधारण प्रति'रेंज ("ए 1: डी 20")। कॉपी करेंवर्कशीट्स ("शीट 2")। रेंज ("बी 10")। पेस्ट करें'या'वर्तमान शीट से 'शीट 2' नाम की शीट में कॉपी करेंरेंज ("ए 1: डी 20")। कॉपी गंतव्य: = वर्कशीट्स ("शीट 2")। रेंज ("बी 10")

स्पेशल पेस्ट करो

PASTESPECIAL विकल्प का उपयोग करके स्रोत श्रेणी के चयनित गुणों को गंतव्य पर कॉपी किया जा सकता है:

123	' श्रेणी को केवल मान के रूप में चिपकाएँरेंज ("ए 1: डी 20")। कॉपी करेंवर्कशीट्स ("शीट 2")। रेंज ("बी 10")। पेस्ट स्पेशल पेस्ट: = xlPasteValues ​​​​

पेस्ट विकल्प के लिए यहां संभावित विकल्प दिए गए हैं:

12345678910111213

'विशेष प्रकार चिपकाएँxlपेस्टसभीxlPasteAllExceptBordersxlPasteAllMergingConditionalFormatsxlPasteAllUsingSourceThemexlपेस्टकॉलमचौड़ाईxlपेस्ट करेंटिप्पणियांxlपेस्टफॉर्मेटxlपेस्टसूत्रxlPasteFormulasAndNumberFormatsxl पेस्ट सत्यापनxlPasteValuesxlPasteValuesAndNumberFormats

स्वतः फ़िट सामग्री

नीचे दिए गए कोड का उपयोग करके सामग्री को फिट करने के लिए पंक्तियों और स्तंभों का आकार बदला जा सकता है:

12345

सामग्री फिट करने के लिए पंक्तियों 1 से 5 का आकार बदलेंपंक्तियाँ ("1:5")। स्वतः फ़िटसामग्री फिट करने के लिए कॉलम ए से बी का आकार बदलेंकॉलम ("ए: बी")। ऑटोफिट

अधिक रेंज उदाहरण

यह अनुशंसा की जाती है कि आप GUI के माध्यम से आवश्यक कार्रवाई करते समय मैक्रो रिकॉर्डर का उपयोग करें। यह आपको उपलब्ध विभिन्न विकल्पों और उनका उपयोग करने के तरीके को समझने में मदद करेगा।

प्रत्येक के लिए

रेंज का उपयोग करके लूप करना आसान है प्रत्येक के लिए नीचे दिखाए अनुसार निर्माण करें:

123	रेंज में प्रत्येक सेल के लिए ("A1:B100")' सेल के साथ कुछ करेंअगली सेल

लूप के प्रत्येक पुनरावृत्ति पर श्रेणी में एक सेल को वेरिएबल c को सौंपा गया है और उस सेल के लिए फॉर लूप में स्टेटमेंट निष्पादित किए जाते हैं। लूप बाहर निकलता है जब सभी कोशिकाओं को संसाधित किया जाता है।

तरह

सॉर्ट रेंज ऑब्जेक्ट की एक विधि है। आप श्रेणी को क्रमबद्ध करने के लिए विकल्प निर्दिष्ट करके श्रेणी को क्रमबद्ध कर सकते हैं। क्रमबद्ध करें। नीचे दिया गया कोड सेल C2 में कुंजी के आधार पर कॉलम A:C को सॉर्ट करेगा। क्रमबद्ध क्रम xl आरोही या xl अवरोही हो सकता है। शीर्षलेख:= xlYes का उपयोग किया जाना चाहिए यदि पहली पंक्ति शीर्षलेख पंक्ति है।

12	Columns("A:C").Sort key1:=Range("C2"), _आदेश1:=xlआरोही, शीर्षलेख:=xlहाँ

पाना

Find भी Range Object की एक विधि है। यह खोज मानदंड से मेल खाने वाली सामग्री वाला पहला सेल ढूंढता है और सेल को रेंज ऑब्जेक्ट के रूप में लौटाता है। यह वापसी कुछ नहीं अगर कोई मेल नहीं है।

उपयोग अगला तलाशें विधि (या FindPrevious) अगली (पिछली) घटना को खोजने के लिए।

निम्नलिखित कोड "जॉन" से शुरू होने वाली श्रेणी के सभी कक्षों के लिए फ़ॉन्ट को "एरियल ब्लैक" में बदल देगा:

12345

रेंज में प्रत्येक सी के लिए ("ए 1: ए 100")अगर c "जॉन*" पसंद है तोc.Font.Name = "एरियल ब्लैक"अगर अंतअगला ग

निम्नलिखित कोड निर्दिष्ट सीमा में "टू टेस्ट" की सभी घटनाओं को "उत्तीर्ण" में बदल देगा:

12345678910

रेंज के साथ ("ए 1: ए 500")सेट सी = .Find ("टेस्ट करने के लिए", लुकइन: = xlValues ​​​​)अगर नहीं c तो कुछ नहीं हैपहला पता = सी.पताकरनाc.Value = "उत्तीर्ण"सेट सी = .FindNext(c)लूप जबकि नॉट सी इज़ नथिंग और सी.पता पहला पताअगर अंतके साथ समाप्त करना

यह नोट करना महत्वपूर्ण है कि FindNext का उपयोग करने के लिए आपको एक श्रेणी निर्दिष्ट करनी होगी। इसके अलावा आपको एक स्टॉपिंग कंडीशन प्रदान करनी होगी अन्यथा लूप हमेशा के लिए निष्पादित हो जाएगा। आम तौर पर पहली सेल का पता जो पाया जाता है उसे एक वेरिएबल में स्टोर किया जाता है और जब आप उस सेल में दोबारा पहुंचते हैं तो लूप बंद हो जाता है। आपको उस मामले की भी जांच करनी चाहिए जब लूप को रोकने के लिए कुछ भी नहीं मिलता है।

रेंज का पता

A1 शैली में पता प्राप्त करने के लिए Range.Address का उपयोग करें

123	MsgBox रेंज ("A1: D10")। पता:' याडीबग.प्रिंट रेंज ("ए 1: डी 10")। पता:

R1C1 शैली में पते प्राप्त करने के लिए xlReferenceStyle (डिफ़ॉल्ट xlA1 है) का उपयोग करें

123	MsgBox Range("A1:D10").Address(ReferenceStyle:=xlR1C1)' याDebug.Print Range("A1:D10").Address(ReferenceStyle:=xlR1C1)

यह तब उपयोगी होता है जब आप चर में संग्रहीत श्रेणियों से निपटते हैं और केवल कुछ पतों के लिए प्रक्रिया करना चाहते हैं।

रेंज टू ऐरे

किसी श्रेणी को किसी सरणी में स्थानांतरित करना और फिर मानों को संसाधित करना तेज़ और आसान है। सरणी में श्रेणी को भरने के लिए आवश्यक आकार की गणना से बचने के लिए आपको सरणी को भिन्न के रूप में घोषित करना चाहिए। सरणी के आयाम श्रेणी में मानों की संख्या से मेल खाने के लिए सेट हैं।

123456789

डिम डिरारे वेरिएंट के रूप में' श्रेणी में मानों को ऐरे में संग्रहीत करेंडिरारे = रेंज ("ए 1: ए 5")। मान:'मानों को संसाधित करने के लिए लूपप्रत्येक सी के लिए DirArrayडिबग.प्रिंट cअगला

श्रेणी के लिए सरणी

प्रसंस्करण के बाद आप ऐरे को वापस एक श्रेणी में लिख सकते हैं। ऊपर के उदाहरण में ऐरे को रेंज में लिखने के लिए आपको एक रेंज निर्दिष्ट करनी होगी जिसका आकार ऐरे में तत्वों की संख्या से मेल खाता हो।

सरणी को D1:D5 की श्रेणी में लिखने के लिए नीचे दिए गए कोड का उपयोग करें:

123	रेंज ("डी 1: डी 5")। वैल्यू = डिरारेरेंज ("D1: H1")। मान = एप्लिकेशन। ट्रांसपोज़ (DirArray)

कृपया ध्यान दें कि यदि आप इसे एक पंक्ति में लिखते हैं तो आपको ऐरे को स्थानांतरित करना होगा।

योग सीमा

12	SumOfRange = Application.WorksheetFunction.Sum (रेंज ("A1: A10"))डिबग.प्रिंट SumOfRange

आप Application.WorkSheetFunction को निर्दिष्ट करके अपने VBA कोड में Excel में उपलब्ध कई फ़ंक्शन का उपयोग कर सकते हैं। फंक्शन नाम से पहले जैसा कि ऊपर दिए गए उदाहरण में है।

काउंट रेंज

1234567

' श्रेणी में संख्याओं के साथ कक्षों की संख्या गिनेंकाउंटऑफसेल्स = एप्लिकेशन। वर्कशीट फंक्शन। काउंट (रेंज ("ए 1: ए 10"))डिबग.प्रिंट काउंटऑफसेल्सरेंज में गैर-रिक्त कक्षों की संख्या की गणना करेंCountOfNonBlankCells = Application.WorksheetFunction.CountA (रेंज ("A1: A10"))Debug.Print CountOfNonBlankCells

द्वारा लिखित: विनमरा चंद्र