循环表以更改excel中的值

odopli94 于 2023-05-23 发布在其他

关注(0)|答案(1)|浏览(117)

我在Excel电子表格中有一个表格。在这个表中，我想从列AP 2开始：AW 2，并向下运行行到表的末尾，然后执行2个操作。一个我想把单元格的值从文本转换为数字。第二步，我将每个细胞除以100。到目前为止，我的代码包含两个宏，当按下Ctrl n时触发。它们可以工作，但被硬编码到第2000行，而不是找到表的末尾（有时表可能多于或少于2000行）。我也不认为有两个宏可能是所有的效率。任何关于这方面的帮助将不胜感激！

Sub Number_Conversion()
'Convert text to number
With Sheet1.Range("AP2:AW2000")
    .NumberFormat = "Number"
    .Value = .Value
End With
End Sub

Sub Divide_By()
'Divide number by 100
'declare variables
Dim ws As Worksheet
Dim rng As Range
Dim myVal As Range

Set ws = Sheet1
Set rng = ws.Range("AP2:AW2000")

For Each myVal In rng
    If myVal.Value > 0 Then
        myVal = myVal.Value / 100
    End If
Next myVal
End Sub

excel

来源：https://stackoverflow.com/questions/76284360/loop-table-to-change-values-in-excel

1条答案

按热度按时间

8ehkhllq1#

我会这么做

Sub try_this()
    'declarations
    Dim x As Long, y As Long, myArr, last_row As Long
    
    'find last row in AP:AW range
    last_row = Sheet1.Range("AP:AW").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    'using the range down to last_row
    With Sheet1.Range("AP2:AW" & last_row)
    
    'copy range values to an array
        myArr = .Value2
        
        'cycle through the array horizontally
        For x = 1 To UBound(myArr)
        
            '.. and vertically
            For y = 1 To UBound(myArr, 2)
            
                'divide value by 100 if it equates to more than zero
                If myArr(x, y) > 0 Then myArr(x, y) = myArr(x, y) / 100
                
            Next
        Next
        
        'write array back to the range
        .Value2 = myArr
        
    End With
End Sub

谢谢奥斯卡指出我错过的部分。现在，无论需要多少行，这都将动态工作。

赞(0）回复(0）举报 2023-05-23

我来回答

循环表以更改excel中的值

1条答案

相关问题

热门标签

最新问答