• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Add minus sign in the columns(making the numbers negative)

shinchan

New Member
vba code is attached to a seperate file
all files are located in same place
open sample1.xlsx
If column Y has data then add minus sign in the cells
save and close the book
 

Attachments

AlanSidman

Active Member
@shinchan
Code:
Option Explicit

Sub AddMinus()
    Dim i As Long, lr As Long

    lr = Range("Y" & Rows.Count).End(xlUp).Row
    For i = 2 To lr
        If Not IsNull(Range("Y" & i)) Then
            Range("Y" & i) = "-" & Range("Y" & i)
        End If
    Next i


End Sub
@vletm
could not open and read your file. Got an error message telling me that a file was not on my desktop. confused :(
 

vletm

Excel Ninja
AlanSidman
That my (T1toT2.xlsb) file belongs to previous shinchan thread, which is 'my opinion' part of this thread too.
I enclosed this thread to it and it works are written in that thread.

Seems that shinchan would like to run many 'a separate file' with sample1.xlsx -file.
Your 'a separate file' don't work in my 'a separate file'.

If You tried to run that file (as You did) then You have to have needed other files to get wanted result.
Without those files - it'll give a message ... what is missing as You got it!
If You have those needed files, then it will show needed results.

I and everyone can open and read that file without any tricks.

Ps. If Your 'AddMinus' runs twice ... are values still negative?
 

AlanSidman

Active Member
Ps. If Your 'AddMinus' runs twice ... are values still negative?
Excellent question. Did not test for that. Have since trashed the file, but I suppose that the code would need to be amended to make sure that the current value is not already negative. Thanks for you information. I believe that you have this one under control and I will leave it to you.

Alan
 

salim hasan

New Member
Can you try my Macro Please
Code:
Option Explicit
Sub MY_Minus()
    Dim i As Long, lr As Long

    lr = Range("Y" & Rows.Count).End(xlUp).Row
    For i = 2 To lr
    If IsNumeric(Range("Y" & i)) Then
      Range("Y" & i) = _
      IIf(Range("Y" & i) <= 0, Range("Y" & i), -1 * Range("Y" & i))
    End If
    Next i
End Sub
 

Marc L

Excel Ninja
Hi !​
According to the initial attachment without a loop just with an EZ formula :​
Code:
Sub Demo1()
       Dim F$
           F = ThisWorkbook.Path & Application.PathSeparator & "sample1.xls"
    If Dir(F) > "" Then
        With Workbooks.Open(F, False).Worksheets(1).[A1].CurrentRegion.Rows
            With .Item("2:" & .Count).Columns(25)
                 .Value2 = .Parent.Evaluate(Replace("IF(#>0,-#,#)", "#", .Address))
            End With
                 .Parent.Parent.Close True
        End With
    End If
End Sub
Do you like it ? So thanks to click on bottom right Like !​
 
Top