CODEWITHSUNDEEP
excelvba
cena
cena

Reputation: 420

How to make selective entire column absolute number

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.

Updated Code Below: enter image description here

    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

Before pressing the program enter image description here

After pressing the program enter image description here

In reality i want it to be enter image description here

Upvotes: 0

Views: 143

Answers (1)

BigBen
BigBen

Reputation: 50007

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

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

Try something like the following:

 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

Full code:

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

EDIT:

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

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

Upvotes: 1

Related Questions