分类导航
wordpressDedecmsphpcmsDiscuz帝国CMS

Microsoft Excel 2010复制/粘贴编辑想法和帮助请求小细节

wordpress   发布时间：2022-04-02  发布网站：大佬教程  code.js-code.com
大佬教程收集整理的这篇文章主要介绍了Microsoft Excel 2010复制/粘贴编辑想法和帮助请求小细节，大佬教程大佬觉得挺不错的，现在分享给大家，也给大家做个参考。

概述

是的,我已经在其他人的帮助下为Microsoft Excel编写了这个宏(如果有帮助的话,那就是2010年).我想知道是否有人有办法缩短它,并使其更有效率.然而,它之前仍然得到了相同的结果？我可以在这里找到我需要使用的CSV格式的一个例子……是的,不幸的是,它们必须被放入那些列和单元格中. 我真正难以接受的唯一问题是： 以.Cell(2,3)为例……如果您注意到,在每个部分上它都会复制并粘贴,它有
是的,我已经在其他人的帮助下为Microsoft Excel编写了这个宏(如果有帮助的话,那就是2010年).我想知道是否有人有办法缩短它,并使其更有效率.然而,它之前仍然得到了相同的结果？我可以在这里找到我需要使用的CSV格式的一个例子……是的,不幸的是,它们必须被放入那些列和单元格中.

我真正难以接受的唯一问题是：
以.Cell(2,3)为例……如果您注意到,在每个部分上它都会复制并粘贴,它有一个新行..我希望它能够做到这一点..我是新手,并且不能找到一种方法让它只是粘贴每一个到下一个可用的行..所以我的解决方案是做2,3,4,5 ..等等..如果有人知道如何改变这个以及使这个..循环？每说,这将是很大的帮助.只需循环播放正在复制的数据量,而不是重复.

这是一个例子CSV：Media Fire很干净,我保证.感谢您的时间.

宏代码将某些列/行数据单​​元格从一个工作表复制到另一个工作表到特定单元格

Sub FormatData()
Dim col As Integer

For col = 1 To 1
    With Worksheets(2)
       .Cells(2,2) = Cells(1,col)
       .Cells(2,3) = Cells(2,col) & ". " & Cells(3,col) & ". " & Cells(4,col) & ". " & Cells(5,col) & "."
       .Cells(2,4) = Cells(7,5) = Cells(10,col)
    End With
Next col
    For col = 2 To 2
    With Worksheets(2)
       .Cells(3,col)
       .Cells(3,col) & "."
       .Cells(3,col)
    End With
Next col
    For col = 3 To 3
    With Worksheets(2)
       .Cells(4,col)
       .Cells(4,col) & "."
       .Cells(4,col)
    End With
Next col
    For col = 4 To 4
    With Worksheets(2)
       .Cells(5,col)
       .Cells(5,col) & "."
       .Cells(5,col)
    End With
Next col

    For col = 1 To 1
    With Worksheets(2)
       .Cells(6,2) = Cells(13,col)
       .Cells(6,3) = Cells(14,col) & ". " & Cells(15,col) & ". " & Cells(16,col) & ". " & Cells(17,col) & "."
       .Cells(6,4) = Cells(19,5) = Cells(22,col)
    End With
Next col
        For col = 2 To 2
    With Worksheets(2)
       .Cells(7,col)
       .Cells(7,col) & "."
       .Cells(7,col)
    End With
Next col
        For col = 3 To 3
    With Worksheets(2)
       .Cells(8,col)
       .Cells(8,col) & "."
       .Cells(8,col)
    End With
Next col
        For col = 4 To 4
    With Worksheets(2)
       .Cells(9,col)
       .Cells(9,col) & "."
       .Cells(9,col)
    End With
Next col
        For col = 1 To 1
    With Worksheets(2)
       .Cells(10,2) = Cells(25,col)
       .Cells(10,3) = Cells(26,col) & ". " & Cells(27,col) & ". " & Cells(28,col) & ". " & Cells(29,col) & "."
       .Cells(10,4) = Cells(31,5) = Cells(34,col)
    End With
Next col
        For col = 2 To 2
    With Worksheets(2)
       .Cells(11,col)
       .Cells(11,col) & "."
       .Cells(11,col)
    End With
Next col
        For col = 3 To 3
    With Worksheets(2)
       .Cells(12,col)
       .Cells(12,col) & "."
       .Cells(12,col)
    End With
Next col
        For col = 4 To 4
    With Worksheets(2)
       .Cells(13,col)
       .Cells(13,col) & "."
       .Cells(13,col)
    End With
Next col
        For col = 1 To 1
    With Worksheets(2)
       .Cells(14,2) = Cells(37,col)
       .Cells(14,3) = Cells(38,col) & ". " & Cells(39,col) & ". " & Cells(40,col) & ". " & Cells(41,col) & "."
       .Cells(14,4) = Cells(43,5) = Cells(46,col)
    End With
Next col
        For col = 2 To 2
    With Worksheets(2)
       .Cells(15,col)
       .Cells(15,col) & "."
       .Cells(15,col)
    End With
Next col
        For col = 3 To 3
    With Worksheets(2)
       .Cells(16,col)
       .Cells(16,col) & "."
       .Cells(16,col)
    End With
Next col
        For col = 4 To 4
    With Worksheets(2)
       .Cells(17,col)
       .Cells(17,col) & "."
       .Cells(17,col)
    End With
Next col
        For col = 1 To 1
    With Worksheets(2)
       .Cells(18,2) = Cells(49,col)
       .Cells(18,3) = Cells(50,col) & ". " & Cells(51,col) & ". " & Cells(52,col) & ". " & Cells(53,col) & "."
       .Cells(18,4) = Cells(55,5) = Cells(58,col)
    End With
Next col
        For col = 2 To 2
    With Worksheets(2)
       .Cells(19,col)
       .Cells(19,col) & "."
       .Cells(19,col)
    End With
Next col
        For col = 3 To 3
    With Worksheets(2)
       .Cells(20,col)
       .Cells(20,col) & "."
       .Cells(20,col)
    End With
Next col
        For col = 4 To 4
    With Worksheets(2)
       .Cells(21,col)
       .Cells(21,col) & "."
       .Cells(21,col)
    End With
Next col
        For col = 1 To 1
    With Worksheets(2)
       .Cells(22,2) = Cells(61,col)
       .Cells(22,3) = Cells(62,col) & ". " & Cells(63,col) & ". " & Cells(64,col) & ". " & Cells(65,col) & "."
       .Cells(22,4) = Cells(67,5) = Cells(70,col)
    End With
Next col
        For col = 2 To 2
    With Worksheets(2)
       .Cells(23,col)
       .Cells(23,col) & "."
       .Cells(23,col)
    End With
Next col
        For col = 3 To 3
    With Worksheets(2)
       .Cells(24,col)
       .Cells(24,col) & "."
       .Cells(24,col)
    End With
Next col
        For col = 4 To 4
    With Worksheets(2)
       .Cells(25,col)
       .Cells(25,col) & "."
       .Cells(25,col)
    End With
Next col
        For col = 1 To 1
    With Worksheets(2)
       .Cells(26,2) = Cells(73,col)
       .Cells(26,3) = Cells(74,col) & ". " & Cells(75,col) & ". " & Cells(76,col) & ". " & Cells(77,col) & "."
       .Cells(26,4) = Cells(79,5) = Cells(82,col)
    End With
Next col
        For col = 2 To 2
    With Worksheets(2)
       .Cells(27,col)
       .Cells(27,col) & "."
       .Cells(27,col)
    End With
Next col
        For col = 3 To 3
    With Worksheets(2)
       .Cells(28,col)
       .Cells(28,col) & "."
       .Cells(28,col)
    End With
Next col
        For col = 4 To 4
    With Worksheets(2)
       .Cells(29,col)
       .Cells(29,col) & "."
       .Cells(29,col)
    End With
Next col
        For col = 1 To 1
    With Worksheets(2)
       .Cells(30,2) = Cells(85,col)
       .Cells(30,3) = Cells(86,col) & ". " & Cells(87,col) & ". " & Cells(88,col) & ". " & Cells(89,col) & "."
       .Cells(30,4) = Cells(91,5) = Cells(94,col)
    End With
Next col
        For col = 2 To 2
    With Worksheets(2)
       .Cells(31,col)
       .Cells(31,col) & "."
       .Cells(31,col)
    End With
Next col
        For col = 3 To 3
    With Worksheets(2)
       .Cells(32,col)
       .Cells(32,col) & "."
       .Cells(32,col)
    End With
Next col
        For col = 4 To 4
    With Worksheets(2)
       .Cells(33,col)
       .Cells(33,col) & "."
       .Cells(33,col)
    End With
Next col
End Sub

解决方法

这是一个重构的Sub

注意事项：

>使用变量数组进行循环/数据处理,因为循环遍历单元格很慢
>您可以更改srcBlocks的值以控制要处理的块数,或从源数据中提取它
>如果移动,则按索引引用目标表可能会有问题.更安全地通过名称工作表(“SheetName”)引用它

.

Sub FormatData()
    Dim rw2 As Integer,rwA As Integer,colA As Integer
    Dim vDst() As Variant,vSrc As Variant
    Dim srcBlocks As Integer

    srcBlocks = 8 ' process 8 blocks of 12 rows

    vSrc = ActiveSheet.Range("A1:D" & srcBlocks * 12)
    ReDim vDst(1 To srcBlocks * 4 + 1,1 To 5)

    For rwA = 0 To srcBlocks * 12 - 1 Step 12  ' = 0,12,24,...
        For colA = 1 To 4                      ' 4 columns in Src
            rw2 = (rwA \ 12) * 4 + colA + 1    ' 4 Dst rws per block,= 2..5,6..9,...

            vDst(rw2,2) = vSrc(rwA + 1,colA)
            vDst(rw2,3) = vSrc(rwA + 2,colA) & ". " & _
                           vSrc(rwA + 3,colA) & ". " & _
                           vSrc(rwA + 4,colA) & ". " & _
                           vSrc(rwA + 5,colA) & "."
            vDst(rw2,4) = vSrc(rwA + 7,5) = vSrc(rwA + 10,colA)
        Next colA
    Next rwA

    Worksheets(2).Range("A1:E" & CStr(srcBlocks * 4 + 1)) = vDst
End Sub

要为Source数据中的可变数量的列添加灵活性

Sub FormatData()
    Dim rw2 As Integer,vSrc As Variant
    Dim srcBlocks As Integer,srcColumns As Integer

    srcBlocks = 8  ' process 8 blocks of 12 rows '
    srcColumns = 5 ' Columns in source data '

    vSrc = ActiveSheet.Range( _
             ActiveSheet.Cells(1,1),_
             ActiveSheet.Cells(srcBlocks * 12,srcColumns))
    ReDim vDst(1 To srcBlocks * srcColumns + 1,1 To 5)

    For rwA = 0 To srcBlocks * 12 - 1 Step 12           ' = 0,... '
        For colA = 1 To srcColumns                      ' srcColumns columns in Source '
            rw2 = (rwA \ 12) * srcColumns + colA + 1    ' srcColumns rows in Destination per Source block '

            vDst(rw2,colA) & ". " & _
                         vSrc(rwA + 3,colA) & ". " & _
                         vSrc(rwA + 4,colA) & ". " & _
                         vSrc(rwA + 5,colA)
        Next colA
    Next rwA

    Worksheets(2).Range("A1:E" & CStr(srcBlocks * 4 + 1)) = vDst
End Sub

大佬总结

以上是大佬教程为你收集整理的Microsoft Excel 2010复制/粘贴编辑想法和帮助请求小细节全部内容，希望文章能够帮你解决Microsoft Excel 2010复制/粘贴编辑想法和帮助请求小细节所遇到的程序开发问题。

如果觉得大佬教程网站内容还不错，欢迎将大佬教程推荐给程序员好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ：384754419，请注明来意。
标签：
猜你在找的wordpress相关文章
其他相关热搜词更多
JavaScript字符串对象运算符count异步uisqloraclejsonpromisehttphttpsaxios常量forwhile循环Length算法