VBA macro

[Ishaqur]

I have 1700 hundred record some are debit and some are credit I need to move credit value in debit column with negative sign as value

 

[LMacD]

Move to s different column same row?
Is this a one time event or is it run every time a number is entered into the debt column

 

[jolivanes]

Column B is the Debit Column while Column C is the Credit Column
Change references in code as required.

Sub Move_Cr_To_Dbt()
Dim lr As Long
lr = Range("B:C").Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
    With Range("B2:B" & lr)
        .SpecialCells(4).Formula = "=RC[+1]*-1"
        .Value = .Value
        .Offset(, 1).ClearContents
    End With
End Sub

 

[Ishaqur]

Column B is the Debit Column while Column C is the Credit Column
Change references in code as required.

Sub Move_Cr_To_Dbt()
Dim lr As Long
lr = Range("B:C").Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
    With Range("B2:B" & lr)
        .SpecialCells(4).Formula = "=RC[+1]*-1"
        .Value = .Value
        .Offset(, 1).ClearContents
    End With
End Sub

Thank you for your reply if I don’t want to move credit data just need to copy in debit side as negative where I have to change

 

[LMacD]

You do not say how you identify a credit value in the debt column. I am assuming it already has a negative value.
If my assumption is incorrect, tell me how you know a value in the debt column (B) is a credit.
I used "sheet 1". If your data is on a different sheet change sheet1 to whatever it is named.

[FONT=Verdana][FONT=Verdana]Sub CopyCreditToDebt()
    Dim lastRow As Long
    'Find the last row in col B that has a value.
    lastRow = Sheets("Sheet1").Range("B:B").SpecialCells(xlCellTypeLastCell).Row
    Sheets("Sheet1").Range("C1:C" & lastRow).Value = "=IF(B1<0,B1,"""")"
End Sub
[/FONT][B][I][U][SUB][SUP]<strike>
</strike>[/SUP][/SUB][/U][/I][/B][/FONT]

 

[jolivanes]

Please don't quote whole Posts. Too much clutter. Refer to a Post Number or use the Poster's name if needed.
Leave this out

.Offset(, 1).ClearContents

 

[Ishaqur]

Hi above code is working fine thank you but I just need to show credit amount in debit side ($75.21)

Second line code I want to delete all serial number column if it comes more then once I am using countif formula please advise with VBA code

 

[jolivanes]

Re: "above code is working fine"
As asked in Post #6 . Which code are you referring to?
Re: "($75.21)"
What does that mean?
What is meant by "debit side" Is that the column where the debit numbers are?
What is "serial number column"?
Explain in detail what your sheet looks like.
Better yet, Upload a copy of your file with before and after examples into DropBox or a similar site and mark it for sharing.

 

[Ishaqur]

Just want to change the formal only like (72.56) instead of -72.56

 

[jolivanes]

Did you not say in the first post that you wanted a negative sign?
However, can't you change the format of the cells?
See here
https://www.exceltip.com/excel-format/formatting-a-negative-number-with-parentheses.html

Or, if you mean the code from Post #3 :
You can change this

.SpecialCells(4).Formula = "=RC[+1]*-1"

to this

.SpecialCells(4).Formula = "=""(""&RC[1]&"")"""

However, this last solution might change automatically to negative numbers.