Results 1 to 4 of 4

Thread: Urgent help please excel formatting

  1. #1

    Urgent help please excel formatting



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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 :
    A B D E F
    POSTING KY FLAG Val posted 1 Val posted 2 Val posted 3
    1 40 S 400.25 100.00
    2 50 H 110.75 80.25
    3 40 S 250.00 55.00
    4 50 H 230.00 440.25
    5 40 S 123.25 345.76
    6 50 H 75.75 57.78


    OUTPUT:

    A B D E F
    POSTING KY FLAG Val posted 1 Val posted 2 Val posted 3
    1 40 S 400.25 100.00
    2 50 H -110.75 -80.25
    3 40 S 250.00 55.00
    4 50 H -230.00 -440.25
    5 40 S 123.25 345.76
    6 50 H -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..

  2. #2
    Conjurer sambit's Avatar
    Join Date
    Sep 2014
    Posts
    143
    Articles
    0
    Excel Version
    Excel2007
    Hi,
    plz find attachment.
    Attached Files Attached Files

  3. #3
    Seeker DSDressler's Avatar
    Join Date
    Dec 2014
    Location
    Bend, OR
    Posts
    18
    Articles
    0
    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

  4. #4
    You can do it with single-loop code

    Code:
    Public Sub ResetValues()
    Dim lastrow As Long
    Dim lastcol As Long
    Dim cell As Range
    
    
        With ActiveSheet
        
            lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
            lastcol = .Cells(1, .Columns.Count).End(xlToLeft).Column
            For Each cell In .Range(.Range("D2"), .Cells(lastrow, lastcol))
            
                If cell.Value <> "" Then
                
                    If .Cells(cell.Row, "B").Value = 50 And .Cells(cell.Row, "C").Value = "H" Then
                
                        cell.Value = -cell.Value
                    End If
                End If
            Next cell
        End With
    End Sub
    Last edited by Bob Phillips; 2015-01-04 at 09:39 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •