跳过非空单元格以粘贴特殊数据

发布于 2024-11-03 05:40:54 字数 454 浏览 3 评论 0原文

仅当范围 (a3:M3) 为空时，我才想将工作表“SL”中的范围 (a3:M3) 中的数据复制到工作表“EL”中的范围 (a3:m3) 中。否则将所选数据复制到下一行 (a4:m4)。

下面是我尝试解决的代码..但它不起作用...请帮助

Range("C9:G10").Select
Selection.Copy
Sheets("EL").Select

For n = 1 To n = 100
    If Cells(n, 2).Value <> "" Then 
        Cells(n + 1, 2).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    End If
Next n

原文

I want to copy data from range (a3:M3) in worksheet "SL" to range (a3:m3) in worksheet "EL" only if range (a3:M3) is empty. Else to copy the selected data to the next row (a4:m4).

Below is the code i tried to work out ..but its not working...plz help

Range("C9:G10").Select
Selection.Copy
Sheets("EL").Select

For n = 1 To n = 100
    If Cells(n, 2).Value <> "" Then 
        Cells(n + 1, 2).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    End If
Next n

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

合久必婚 2024-11-10 05:40:54

您的代码中有一些点我不明白：

为什么它引用 C9:G10？
为什么会出现从n=1到100的循环？
语法 For n = 1 To n = 100 并不像您期望的那样工作 ->将其替换为 For n = 1 To 100。）

这是我对您的问题的解决方案：

Sub copyRange()

    ' Look if destination cells are empty

    Dim isempty As Boolean    
    isempty = True

    For Each cell In Sheets("EL").Range("a3:m3").Cells
        If cell.Value! = "" Then isempty = False
    Next

    ' Copy content from SL to EL into the correct line
    Sheets("SL").Range("a3:m3").Select
    Selection.Copy
    If isempty Then
        Sheets("EL").Range("a3:m3").PasteSpecial Paste:=xlPasteValues
    Else
        Sheets("EL").Range("a4:m4").PasteSpecial Paste:=xlPasteValues
    End If
End Sub

There are some points in your code I do not understand:

why does it refer to C9:G10?
Why is there a loop from n=1 to 100?
The syntax For n = 1 To n = 100 does not work as you might expect -> replace it with For n = 1 To 100.)

Here is my solution to your problem:

Sub copyRange()

    ' Look if destination cells are empty

    Dim isempty As Boolean    
    isempty = True

    For Each cell In Sheets("EL").Range("a3:m3").Cells
        If cell.Value! = "" Then isempty = False
    Next

    ' Copy content from SL to EL into the correct line
    Sheets("SL").Range("a3:m3").Select
    Selection.Copy
    If isempty Then
        Sheets("EL").Range("a3:m3").PasteSpecial Paste:=xlPasteValues
    Else
        Sheets("EL").Range("a4:m4").PasteSpecial Paste:=xlPasteValues
    End If
End Sub

回复收藏 0 原文

~没有更多了~