如何使选择性整列绝对数字 [英] How to make selective entire column absolute number

问题描述

每当列的开头检测到Vbd时，我都试图使某些整个列为绝对列.但是，该参数不是可选的，在尝试将数字转换为列字母的过程中，我尝试设置columnname = last column时会弹出错误.任何帮助将不胜感激.

I am trying to make certain entire columns to be absolute whenever the start of the column detects Vbd. However, the the argument is not optional error pops up at the part where i try to setcolumnname=last column in an attempt to convert number into column alphabet. Any help will be appreciated.

以下更新的代码:

    Option Explicit

Sub testing1()
Dim i As Long
Dim LastColumn As Long
Dim sht As Worksheet
Dim rngToAbs As Range
Dim lastrow As Long

Set sht = ThisWorkbook.Sheets("Sheet1")
LastColumn = sht.Cells(1, sht.Columns.Count).End(xlToLeft).Column
lastrow = sht.Cells(sht.Rows.Count, "E").End(xlUp).Row
 For i = 1 To LastColumn
     With sht
         If sht.Cells(1, i).Value = "Vbd" Then
             Set rngToAbs = .Range(sht.Cells(2, i), sht.Cells(lastrow, i))
             rngToAbs.Value = .Evaluate("=abs(" & rngToAbs.Address & ")")
         End If
     End With
 Next

End Sub

在按下程序之前

按下程序后

实际上，我希望它成为

In reality i want it to be

推荐答案

1)您实际上不需要列字母.您可以只使用 Cells 中的列号.

1) You actually don't need the column letter. You can just use the column number within Cells.

2)您需要遍历标题行中的每个单元格，以测试其值是否等于"Vbd" .

2) You need to loop through each individual cell in the header row to test if its value is equal to "Vbd".

尝试以下操作:

 Dim i as Long
 For i = 1 to LastColumn
     With sht
         If .Cells(1, i).Value = "Vbd" Then
             Set rngToAbs = .Range(.Cells(2, i), .Cells(LastRow, i))
             rngToAbs.Value = .Evaluate("=abs(" & rngToAbs.Address & ")")
         End If
     End With
 Next

完整代码:

Sub testing()

    Dim sht As Worksheet
    Set sht = ThisWorkbook.Sheets("Sheet4")

    Dim lastColumn As Long
    lastColumn = sht.Cells(1, sht.Columns.Count).End(xlToLeft).Column

    Dim lastRow As Long
    lastRow = sht.Cells(sht.Rows.Count, 1).End(xlUp).Row

    Dim i As Long
    For i = 1 To lastColumn
        With sht
            If .Cells(1, i).Value = "Vbd" Then
                Dim rngToAbs As Range
                Set rngToAbs = .Range(.Cells(2, i), .Cells(lastRow, i))
                rngToAbs.Value = .Evaluate("=abs(" & rngToAbs.Address & ")")
            End If
        End With
    Next

End Sub

请参阅

Please see this follow-up question. Note that depending on your version of Excel, you may need

 .Evaluate("=INDEX(abs(" & rngToAbs.Address & "),)")

这篇关于如何使选择性整列绝对数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！