Mndandanda wotsitsa wokhala ndi zosankha zambiri

Mndandanda wotsikira pansi pa pepala la Excel ndi wabwino, koma umangokulolani kusankha njira imodzi kuchokera pa seti yomwe yawonetsedwa. Nthawi zina izi ndi zomwe mukufuna, koma nthawi zina wosuta ayenera kusankha ena zinthu zochokera pamndandanda.

Tiyeni tiwone njira zingapo zoyendetsera mndandanda wamasankhidwe ambiri.

Njira 1. Yopingasa

Wogwiritsa amasankha zinthu kuchokera pamndandanda wotsikirapo chimodzi ndi chimodzi, ndipo amawoneka kumanja kwa seloyo akusinthidwa, akungolembedwa mopingasa:

Mindandanda yotsikira m'maselo C2:C5 mu chitsanzo ichi amapangidwa mwanjira yokhazikika, mwachitsanzo

  1. sankhani ma cell C2:C5
  2. tabu kapena menyu Deta sankhani gulu Kutsimikizika Kwazidziwitso
  3. pa zenera limene limatsegula, sankhani njira List ndi kufotokoza ngati osiyanasiyana gwero ma cell omwe ali ndi data yochokera pamndandanda A1:A8

Kenako muyenera kuwonjezera macro ku gawo la pepala, lomwe lidzachita ntchito zonse zazikulu, mwachitsanzo, onjezani zosankhidwa kumanja kwa ma cell obiriwira. Kuti muchite izi, dinani kumanja pa tabu yamasamba ndi mindandanda yotsitsa ndikusankha lamulo Chikho chachinsinsi. Matani kachidindo zotsatirazi pawindo la Visual Basic editor lomwe limatsegula:

Private Sub Worksheet_Change(ByVal Target As Range) Pa Cholakwika Bwezeraninso Next If not Intersect(Target, Range("C2:C5")) Palibe Ndipo Target.Cells.Count = 1 Ndiye Application.EnableEvents = False If Len(Target.Offset) (0, 1)) = 0 Kenako Target.Offset(0, 1) = Target Else Target.End(xlToRight).Offset(0, 1) = Target End If Target.ClearContents Application.EnableEvents = True End If End Sub

Ngati n'koyenera, sinthani mndandanda wazovuta za mindandanda yotsikira pansi C2:C5 pamzere wachiwiri wa code iyi ndi yanu.

Njira 2. Yoyima

Zofanana ndi zomwe zidachitika m'mbuyomu, koma zatsopano zosankhidwa siziwonjezedwa kumanja, koma pansi:

Zimachitika chimodzimodzi, koma ma macro code amasintha pang'ono:

Private Sub Worksheet_Change(ByVal Target As Range) Pa Vuto Yambitsaninso Next If not Intersect(Target, Range("C2:F2")) Si Kanthu Ndipo Target.Cells.Count = 1 Ndiye Application.EnableEvents = False If Len(Target.Offset) (1, 0)) = 0 Kenako Target.Offset(1, 0) = Target Else Target.End(xlDown).Offset(1, 0) = Target End If Target.ClearContents Application.EnableEvents = True End If End Sub

Apanso, ngati kuli kofunikira, sinthani mindandanda yotsitsa ya C2:F2 ndi yanu pamzere wachiwiri wa code iyi.

Njira 3. Ndi kudzikundikira mu selo lomwelo

Mwanjira iyi, kudzikundikira kumachitika mu selo lomwelo pomwe mndandanda wotsitsa uli. Zinthu zosankhidwa zimasiyanitsidwa ndi munthu aliyense (mwachitsanzo, koma):

Mindandanda yotsika m'maselo obiriwira amapangidwa mwanjira yokhazikika, monga momwe zidalili kale. Ntchito yonse yachitika, kachiwiri, ndi macro mu gawo la pepala:

Private Sub Worksheet_Change(ByVal Target As Range) Pa Vuto Liyambirenso Next If Not Intersect(Target, Range("C2:C5")) Is Nothing And Target.Cells.Count = 1 Kenako Application.EnableEvents = False newVal = Target Application.Bwezerani oldval = Target Ngati Len(kale) <> 0 Ndipo oldval <> newVal Ndiye Target = Target & "," & newVal Else Target = newVal End Ngati Len(newVal) = 0 Ndiye Target.ClearContents Application.EnableEvents = Mapeto Oona Ngati Mapeto a Sub

Ngati mungafune, mutha kusintha mawonekedwe olekanitsa (koma) pamzere wa 9 wa code ndi yanu (mwachitsanzo, danga kapena semicolon).

  • Momwe mungapangire mndandanda wosavuta wotsitsa mu cell sheet ya Excel
  • Mndandanda wotsitsa wokhala ndi zomwe zili
  • Mndandanda wotsitsa wokhala ndi zosankha zomwe zikusowa zowonjezedwa
  • Kodi macros ndi chiyani, momwe angagwiritsire ntchito, momwe mungayikitsire macro code mu Visual Basic

Siyani Mumakonda