CODEWITHSUNDEEP
excelvba
ozymandias11
ozymandias11

Reputation: 35

VBA Macro to run on a specific worksheet

I am looking to have a macro to run for a specific worksheet only. The purpose of the macro would be to look for a specified range and find negative values and if there is any to extract the whole row into a new worksheet and at the same time transform those negatives to positives. I came up with some but i know for sure that this is totally wrong...missing a lot of stuff. Still trying to learn, new to this vba stuff. Thanks in advance for any help that you can provide. fyi ntp stands for negativestopositives. Not sure if that will help, just thought i can write all the details to my small "code" 

Sub ntp()
Dim ws As Worksheet
If ws.Name <> "originalNeg" Then
    For Each Cell In Range("I2:I1048576")
        If Cell.Value < 0 Then
            Cell.Value = Abs(Cell.Value)
        End If
    Next Cell

End Sub

Upvotes: 0

Views: 2985

Answers (2)

BruceWayne
BruceWayne

Reputation: 23283

Sub ntp()
Dim ws      As Worksheet
Dim cel     As Range
With Activeworkbook.Worksheets("originalNeg")
    For Each cel In .Range("I2:I" & .Range("I" & Rows.Count).End(xlUp).row)
        If cel.Value < 0 Then cel.Value = Abs(cel.Value)
    Next cel
End With
End Sub

This uses the worksheet, and note the . before Range() which links the range to that specific sheet.

Also, only rarely would you want to use all cells in a column. I used the .End(xlUp).Row to get the last used row in Column I for you to loop through.

Upvotes: 1

Liss
Liss

Reputation: 441

Set ws = ThisWorkbook.Worksheets("originalNeg")

instead of the if on the name.

Upvotes: 0

Related Questions