Feem ntau, qhov xwm txheej tshwm sim thaum peb xav tau los sau (sau) ntau qhov txiaj ntsig tau nkag mus rau hauv ib lub xov tooj ntawm tes:
Cov. Yog tias, piv txwv li, koj nkag mus rau tus lej 1 hauv xov tooj A5, ces tus lej 1 yuav tsum tshwm sim hauv B15. Yog tias koj nkag mus rau tus lej 1 hauv A7, ces 1 yuav tsum tshwm sim hauv cell B22, thiab lwm yam. Feem ntau, dab tsi accountants (thiab tsis yog lawv xwb) hu ua tag nrho cov cumulative.
Koj tuaj yeem siv xws li lub cell-accumulator siv lub macro yooj yim. Txoj cai-nias ntawm daim ntawv tab uas cov hlwb A1 thiab B1 nyob thiab xaiv los ntawm cov ntawv qhia zaub mov Cov ntawv nyeem (Code). Hauv Visual Basic editor qhov rai uas qhib, luam tawm thiab muab tso rau qhov yooj yim macro code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range) Nrog Lub Hom Phiaj Yog .Address(False, False) = "A1" Then if IsNumeric(Value) Then Application.EnableEvents = False Range("A2").Value = Range(" A2").Value + .Value Application.EnableEvents = True End If End Yog End With End Sub Qhov chaw nyob ntawm cov hlwb A1 thiab A2, tau kawg, tuaj yeem hloov nrog koj tus kheej.
Yog tias koj xav tau taug qab cov ntaub ntawv nkag thiab sau cov ntsiab lus tsis yog tib lub hlwb, tab sis tag nrho cov kab, ces cov macro yuav tsum tau hloov me ntsis:
Private Sub Worksheet_Change(ByVal Target As Excel.Range) Yog Tsis Sib Tsoo(Target, Range("A1:A10")) Yog Tsis muaj dab tsi ces yogNumeric(Target.Value) Ces Application.EnableEvents = False Target.Offset(0, 1) .Value = Target.Offset(0, 1).Value + Target.Value Application.EnableEvents = True End Yog kawg Yog Xaus Sub Nws yog assumed tias cov ntaub ntawv yog nkag mus rau hauv lub hlwb ntawm qhov ntau A1: A10, thiab cov zauv nkag tau summed nyob rau hauv lub nyob ib sab kem ntawm sab xis. Yog tias nyob rau hauv koj cov ntaub ntawv nws tsis nyob ib sab, ces nce mus rau sab xis nyob rau hauv tus neeg teb xov tooj Offset - hloov 1 nrog ib tug loj dua.
- Dab tsi yog macro, qhov twg los ntxig macro code hauv VBA, siv lawv li cas?