Urgent help please excel formatting

H

huuhaaw

New member
Joined
Jan 3, 2015
Messages
1
Reaction score
0
Points
0
HI SANDY HERE,

HI FRIENDS I NEED AN URGENT HELP AS I AM BEGINNER TO EXCEL AND LEARNING THINGS AS I CAME ACROSS, I HAVE A REQUIREMENT AND I HAVE MENTIONED A SAMPLE BELOW ANY HELP WOULD BE GREATLY APPRECIATED . WHOLE DATA VALUES ARE IN TEXT FORMAT.

THANKS.


Have 6 columns WITH DATA
SAMPLE :
ABDEF
POSTING KYFLAGVal posted 1Val posted 2Val posted 3
140S400.25100.00
250H110.7580.25
340S250.0055.00
450H230.00440.25
540S123.25345.76
650H75.7557.78


OUTPUT:

ABDEF
POSTING KYFLAGVal posted 1Val posted 2Val posted 3
140S400.25100.00
250H-110.75-80.25
340S250.0055.00
450H-230.00-440.25
540S123.25345.76
650H-75.75-57.78


FORMULA TRYING TO USE ON SINGLE CELL D3:

=IF(AND($A$1="50",$B$1="H"),-($D$3),($D$3))

SO AS COPY ACROSS WHOLE RANGE D6:F6 SO AS TO SHOW NEGATIVE FOR THE CONDITION WHEN TRUE AS MENTIONED IN FORMULA.

BUT ITS THROWING A CIRCULAR REFERENCE ERROR

PLS HELP..
 
Hi,
plz find attachment.
 

Attachments

  • Example.xlsx
    8.7 KB · Views: 9
If you wanted to just alter the values in the existing cells, you can run a double loop macro noted below. The i value is the starting row, j is the first column tested, c1 is the first test column, c2 is the last column of valued (val posted 3). Add a CommandButton and insert the text below (without repeating the top and bottom Sub text...

Private Sub CommandButton21_Click()
Dim i As Integer
Dim j As Integer
Dim c1 As Integer
Dim c2 As Integer
i = 2 'row references
j = 4 'column references
c1 = 2 'test column
c2 = 6 'max revised columns
Do While j <= c2
Do While i <= 7
If (Cells(i, c1).Value = 50 And Cells(i, c1 + 1).Value = "H" And Cells(i, c1).Value <> "") Then Cells(i, j).Value = -Cells(i, j).Value
i = i + 1
Loop
j = j + 1
i = 2
Loop
End Sub
 
You can do it with single-loop code

Code: 
[COLOR=#0000cd]Public Sub[/COLOR] ResetValues()
[COLOR=#0000cd]Dim [/COLOR]lastrow [COLOR=#0000cd]As Long[/COLOR]
[COLOR=#0000cd]Dim [/COLOR]lastcol [COLOR=#0000ff]As Long[/COLOR]
[COLOR=#0000cd]Dim [/COLOR]cell [COLOR=#0000cd]As[/COLOR] Range


    [COLOR=#0000cd]With [/COLOR]ActiveSheet
    
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        lastcol = .Cells(1, .Columns.Count).End(xlToLeft).Column
        [COLOR=#0000cd]For Each [/COLOR]cell [COLOR=#0000cd]In [/COLOR].Range(.Range("D2"), .Cells(lastrow, lastcol))
        
            [COLOR=#0000cd]If [/COLOR]cell.Value <> "" [COLOR=#0000cd]Then[/COLOR]
            
                [COLOR=#0000cd]If [/COLOR].Cells(cell.Row, "B").Value = 50 [COLOR=#0000cd]And [/COLOR].Cells(cell.Row, "C").Value = "H" [COLOR=#0000cd]Then[/COLOR]
            
                    cell.Value = -cell.Value[COLOR=#0000cd]
                End If
            End If
        Next[/COLOR] cell
    [COLOR=#0000cd]End With
End Sub[/COLOR]
 
Last edited:
You must log in or register to reply here.
Back
Top