• 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

  • sample1.xls
    30 KB · Views: 10
@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 :(
 
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?
 
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
 
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
 
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 !​
 
Thnx Sir for giving ur precious time and great support to this post
Have a Great Day
I actually needed this type of code But after editing I made the code Thnx Sir for ur guidance
Code:
Sub abc()
    Dim i As Long
    Dim lr As Long
    Dim wbk1 As Workbook
    Dim wsh1 As Worksheet
    
    Application.ScreenUpdating = False
    
    Set wbk1 = Workbooks.Open(ThisWorkbook.Path & "\ap.xls")
    Set wsh1 = wbk1.Worksheets(1)

    With wsh1
    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 With
    Application.DisplayAlerts = False
    wbk1.Close SaveChanges:=True
    Application.DisplayAlerts = True
 
    Application.ScreenUpdating = True
    
End Sub
 
shinchan
ref with Your previous 'added more ...' - writings
If You need to something to be always negative then You could use =-abs(value) then no need to compare anything.
Or
You could include that to Your previous code then no need to have ... two codes.
 
Back
Top