Excel VBA在工作表中查找文本，复制范围，粘贴到其他工作表 [英] Excel vba find text in sheet, copy range, paste to other sheet

问题描述

我对excel宏还比较陌生，无法弄清楚如何修复所需的内容.基本上，我正在编写一个代码，以在工作表中查找文本，当前工作表位于B63(今日")，从找到的值中选择该行，然后从该值中将其停在的下一个值B83("Tomorrow")这个案例.问题在于，每次下载新数据时，今天"和明天"都会上下移动.

I am rather new to excel macros and can't figure out how to fix what I need. Basically i'm writing a code that finds text in a sheet, which is currently at B63 ("Today"), selects the row from the found value down to the next value that it should stop at, B83 ("Tomorrow") in this case. The problem is that "Today" and "Tomorrow" tend to move up and down each time I download the new data.

我已经尝试为此编写代码，但是还没有成功，在这一点上，我什至不确定自己是否采用了正确的方法.这是我所拥有的，将不胜感激:

I've tried writing the code for this but haven't had any success and at this point i'm not even sure that I'm taking the right approach. Here is what I have, any help would be appreciated:

将单元格设为范围

    For i = 1 To 100
    For f = 30 To 100
    Sheets("Download").Select
    If Cells(i, "B").Value = "Today" Then
    If Cells(f, "B").Value = "Tomorrow" Then
    'Insert code to select rows from "i" to "f"-1 (one above f)
    Selection.Copy
    Sheets("Statements").Select
    Range("A3").Select
    ActiveSheet.Paste
    End If

    Next i

推荐答案

尝试一下

Sub test()
Dim td As Range, tm As Range
With Sheets("Download")
    Set td = .[B:B].Find("today")
    Set tm = .[B:B].Find("tomorrow")
    If (Not td Is Nothing) And (Not tm Is Nothing) Then
        .Rows(td.Row & ":" & tm.Offset(-1, 0).Row).Copy Sheets("Statements").[A1]
    End If
End With
End Sub

更新

尝试

Sub test()
Dim td&, tm&, n&, cl As Range, Dwnld As Worksheet, Stmnt As Worksheet
td = 0: tm = 0
Set Dwnld = Sheets("Download"): Set Stmnt = Sheets("Statements")
    With Dwnld
    n = .Cells.Find("*", , , , xlByRows, xlPrevious).Row
    For Each cl In .Range("B1:B" & n)
        If LCase(cl.Value) = "today" Then td = cl.Row
        If LCase(cl.Value) = "tomorrow" Then tm = cl.Offset(-1, 0).Row
        If td > 0 And tm > 0 Then
            .Rows(td & ":" & tm).Copy Stmnt.Range("A" & Stmnt.Cells(Rows.Count, "A").End(xlUp).Row + 1)
            td = 0: tm = 0
        End If
    Next cl
    End With
End Sub

源

目的地

这篇关于Excel VBA在工作表中查找文本，复制范围，粘贴到其他工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！