Cookies tgħinna jagħti servizzi tagħna. Bl-użu tas-servizzi tagħna, inti taqbel li l-użu tagħna ta 'cookies.
Tip: Lingwi oħra huma tradotti minn Google. Tista 'żżur il - English verżjoni ta 'din ir-rabta.
log in
x
or
x
x
reġistru
x

or

Kif tiġbor ringieli bbażati fuq il-valur taċ-ċellola f'kolonna?

Per eżempju, għandi firxa ta 'data li fiha lista ta' numri fil-kolonna D, u issa, nixtieq induċi r-ringieli kollha numru ta 'drabi bbażati fuq il-valuri numeriċi fil-kolonna D biex jinkiseb ir-riżultat li ġej. Kif nista 'nikkontrolla r-ringieli bosta drabi bbażati fuq il-valuri taċ-ċelluli f'Excel?

doc ringieli duplikati miċ-ċellula 1

Ringieli duplikati ħafna drabi bbażati fuq valuri taċ-ċelluli bil-kodiċi VBA


vleġġa bużżieqa tal-lemin blu Ringieli duplikati ħafna drabi bbażati fuq valuri taċ-ċelluli bil-kodiċi VBA

Biex tikkopja u tiddupplika r-ringieli kollha diversi darbiet ibbażati fuq il-valuri taċ-ċelluli, il-kodiċi VBA li ġej jista 'jgħinek, jekk jogħġbok għamel hekk:

1. Żomm il-buttuna ALT + F11 ċwievet biex tiftaħ il - Microsoft Visual Basic għall-Applikazzjonijiet tieqa.

2. Ikklikkja Daħħal > Moduli, u paste tal - kodiċi li ġej fil - Moduli Tieqa.

Kodiċi VBA: ringieli duplikati bosta drabi bbażati fuq il-valur taċ-ċellola:

Sub CopyData()
'Updateby Extendoffice 20160922
    Dim xRow As Long
    Dim VInSertNum As Variant
    xRow = 1
    Application.ScreenUpdating = False
    Do While (Cells(xRow, "A") <> "")
        VInSertNum = Cells(xRow, "D")
        If ((VInSertNum > 1) And IsNumeric(VInSertNum)) Then
           Range(Cells(xRow, "A"), Cells(xRow, "D")).Copy
           Range(Cells(xRow + 1, "A"), Cells(xRow + VInSertNum - 1, "D")).Select
           Selection.Insert Shift:=xlDown
           xRow = xRow + VInSertNum - 1
        End If
        xRow = xRow + 1
    Loop
    Application.ScreenUpdating = False
End Sub

3. Imbagħad agħfas F5 Ċavetta biex tmexxi dan il-kodiċi, ir-ringieli kollha ġew duplikati bosta drabi bbażati fuq il-valur taċ-ċellola fil-kolonna D kif għandek bżonn.

Nota: Fil-kodiċi t'hawn fuq, l-ittra A tindika l-kolonna tal-bidu tal-firxa tad-dejta tiegħek, u l-ittra D hija l-ittra tal-kolonna li tixtieq tiddupplika r-ringieli bbażati fuq. Jekk jogħġbok ibiddluhom għall-ħtieġa tiegħek.



Għodda tal-Produttività Rakkomandata

Uffiċċju Tab

star tad-deheb 1 Ġib tabs handy għal Excel u softwer ieħor tal-Uffiċċju, bħal Chrome, Firefox u Internet Explorer ġdid.

Kutools għal Excel

star tad-deheb 1 Amazing! Żid il-produttività tiegħek f'minuti 5. M'għandekx bżonn xi ħiliet speċjali, ħlief sagħtejn kuljum!

star tad-deheb 1 300 Karatteristiċi Ġodda għall-Excel, Għamla Excel Ħafna Faċli u Qawwija:

  • Merge Cell / Ringieli / Kolonni mingħajr ma titlef id-Data.
  • Għaqqad u Tikkonsolida Folji Multipli u Kotba tax-Xogħol.
  • Qabbel ir-Ranges, Kopja Multipla Ranges, Ikkonverti Test sa Data, Unità u l-Konverżjoni Munita.
  • Għadd bil-Kuluri, Subtotali tal-Pejġing, Sort Avvanzat u Iffiltra Super,
  • Iktar Agħżel / Daħħal / Ħassar / Test / Format / Rabta / Kumment / Kotba tax-Xogħol / Worksheets Għodda ...

Screenshot ta &#39;Kutools għal Excel

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    KAS · 1 months ago
    Is there a way to update the module to only duplicate new data? I'm working on an ongoing document and do not want the code to duplicate data that has been previously duplicated.
  • To post as a guest, your comment is unpublished.
    Gerardo Enrique Heras Araujo · 2 months ago
    hi, for me is no working, I want to remove letters and number duplicate is possible?
  • To post as a guest, your comment is unpublished.
    Naomi · 2 months ago
    This script seems to be exactly what I need, however, when I run it I am getting an error on the line Selection.Insert Shift:=x1Down

    Any suggestions on how I fix this?
  • To post as a guest, your comment is unpublished.
    Manuel F · 2 months ago
    Thanks! it has been a great solution for all my troubles!
  • To post as a guest, your comment is unpublished.
    Leah · 3 months ago
    Hello, this worked great. However, I have a report with 1000 entries and the code stopped duplicating around entry 480. Is there something that I can add so that it completes the action on the entire report?
    • To post as a guest, your comment is unpublished.
      skyyang · 3 months ago
      Hello, Leah,
      I have tested the code in 2000 rows, and it works well.
      Could you send your worksheet to me for testing the code?
      My email address is skyyang@extendoffice.com
      Look forward to your reply!
      • To post as a guest, your comment is unpublished.
        Leah · 3 months ago
        Hello! I got it to work. It was an error on my side, the report had a few blank rows that were hidden that were causing the script to stop looping. It worked for my report with 8,000 rows! Thank youQ