excel 基于另一工作表上某列的某个范围内的值更新行中的单元格

cl25kdpy  于 2023-05-19  发布在  其他
关注(0)|答案(1)|浏览(144)

我试图搜索表1上的D列,并抓取一定范围内(10-100)的值,并将它们填充到表2中的一行(例如:行20),并跳过不在该范围内的值,而不在结果中留下间隙/空白单元格。但是,最新的值会覆盖旧的单元格值(因此,在第2页上没有10、11、12,而只有12、12、12)。此外,我还没有能够弄清楚如何让值填充在一个单独的行(如行C3:Z3),所以他们一直填充在一列。
方法一:

Sub SearchLoop

Range("Sheet1!D").Select

Do Until IsEmpty(ActiveCell)
If ActiveCell.Value > 10 And ActiveCell.Value < 100 
Then Range("Sheet2!A:A").Value = Selection.Value
ActiveCell.Offset(1, 0).Select

Loop
End Sub

方法二:

Sub SearchLoop

Dim cell As Range

For Each cell in Sheets("Sheet1").Range("D:D")
If cell.Value > 10 And cell.Value < 100 Then
Range("Sheet2!A").Value = cell.Value

End If
Next Cell

End Sub
58wvjzkj

58wvjzkj1#

尝试

Sub CopyValuesInRange()
    
    Dim lastRow As Long
    Dim srcRange As Range
    Dim cell As Range
    Dim dstColumn As Long
    
    
    ' Find the last row in column D of Sheet1
    lastRow = Worksheets("Sheet1").Cells(ws1.Rows.Count, "D").End(xlUp).Row
    
    ' Set the source range in column D of Sheet1
    Set srcRange = Worksheets("Sheet1").Range("D1:D" & lastRow)
    
    ' Loop through each cell in the source range
    For Each cell In srcRange
        If cell.Value > 10 And cell.Value < 100 Then
            Worksheets("Sheet2").Range("A20").Offset(0, dstColumn).Value = cell.Value
            ' Increment the destination column
            dstColumn = dstColumn + 1
        End If
    Next cell
End Sub

相关问题