Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Feb 2015
    Posts
    128
    Thanks
    19
    Thanked 1 Time in 1 Post

    Post Code not working on most of data set

    Hi Experts
    I have the below code which check the data in the sheet and where ever find the currency convert the entire column to format of accounting. It works fine in my sample file, but when I am trying to test it in live data it doesn’t work. I have attached the live data sample with the thread.
    Code:
    Sub test()
    Dim r As Range
      For i = 1 To Sheets.Count
    Sheets(i).Activate
         For Each r In Range("a2:AB2")
         If InStr(r.NumberFormat, "") Then r.Columns.EntireColumn.NumberFormat = "_-$* #,##0_-;-$* #,##0_-;_-$* ""-""??_-;_-@_-"
       Next
       Next i
    End Sub
    Regards,
    JD
    Attached Files Attached Files
    Last edited by RetiredGeek; 2015-07-22 at 09:44. Reason: Fixed Code Tags

  2. #2
    2 Star Lounger
    Join Date
    Feb 2015
    Posts
    128
    Thanks
    19
    Thanked 1 Time in 1 Post
    Hi

    I somehow manged to get this fixed with the help of following code.

    Code:
     If VarType(r.Value) = vbCurrency Then r.Columns.EntireColumn.NumberFormat = "_-$* #,##0_-;-$* #,##0_-;_-$* ""-""??_-;_-@_-"
    Is it possible to also find the blank cell with this condition. I want to highlight the blank cell if they are in the currency columns.

    Regards,
    JD

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Jaggi,

    Try this modified code

    Code:
    Sub test()
    Dim r As Range
    For i = 1 To Sheets.Count
        Sheets(i).Activate
        For Each r In Sheets(i).UsedRange
            If r.NumberFormat = """""#,##0.00" Then r.NumberFormat = "_-$* #,##0_-;-$* #,##0_-;_-$* ""-""??_-;_-@_-"
            If r = "" And r.Column < 4 Then r.Interior.Color = vbYellow
        Next r
    Next i
    End Sub
    I regards to highlighting the blanks in the first 4 columns I think you would be much better off using conditional formatting.

    HTH,
    Maud
    Attached Files Attached Files
    Last edited by Maudibe; 2015-07-21 at 13:33. Reason: added file

  4. #4
    2 Star Lounger
    Join Date
    Feb 2015
    Posts
    128
    Thanks
    19
    Thanked 1 Time in 1 Post
    Hi Maud
    When I am trying to use your code in my live data set it is not working. The currency conversion is working, but the empty cells are not getting highlighted. Instead few of the other cells are getting highlighted which are not part of the columns we are targeting because they format might be same. Please lemme know what I am doing wrong here.
    Regards,
    JD
    Attached Files Attached Files

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Jagdev,

    Downloading your file, there is no data on the sheet. Running the code highlighted all the cells in the first 3 columns as expected because the were empty. The usedrange, however, was extended to the last possible row of the spreadsheet because you applied borders by entire columns instead of selecting a range of cells.

    Maud

  6. #6
    2 Star Lounger
    Join Date
    Feb 2015
    Posts
    128
    Thanks
    19
    Thanked 1 Time in 1 Post
    Hi Maud
    I just deleted the column data before uploading the file. There could be possibility of having empty cells in other column as well. We have to set 2 condition here..One is to find the currency filed column and then check the blank cells in it after it is found.
    It should not fill blank cells excluding currency columns.
    I am attaching the file again filling in the empty data for your reference.
    Regards,
    JD
    Attached Files Attached Files

  7. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Jagdev,

    Well of course it will not work as you expected because your currency live data is in different columns than the columns in your . sample to which the code was built (col A-C).

    If your live data will always have the currency in col K-M then adjust the code to:

    Code:
    Sub test()
    Dim r As Range
    For i = 1 To Sheets.Count
        Sheets(i).Activate
        For Each r In Sheets(i).UsedRange
            If r.NumberFormat = """""#,##0.00" Then r.NumberFormat = "_-$* #,##0_-;-$* #,##0_-;_-$* ""-""??_-;_-@_-"
            If r = "" And r.Column >10 and r.Column<14 Then r.Interior.Color = vbYellow
        Next r
    Next i
    End Sub
    HTH,
    Maud

  8. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    If the columns for the source currency data will change, then the Column headers can be used to identify the columns to look for the blanks

    Code:
    Sub test()
    Dim r As Range
    For i = 1 To Sheets.Count
        Sheets(i).Activate
        For Each r In Sheets(i).UsedRange
            If Left(Cells(1, r.Column), 6) = "Amount" Then
                If r.NumberFormat = """""#,##0.00" Then r.NumberFormat = "_-$* #,##0_-;-$* #,##0_-;_-$* ""-""??_-;_-@_-"
                If r = "" And r.Column > 10 And r.Column < 14 Then r.Interior.Color = vbYellow
            End If
        Next r
    Next i
    End Sub
    Last edited by Maudibe; 2015-07-22 at 14:26.

  9. #9
    2 Star Lounger
    Join Date
    Feb 2015
    Posts
    128
    Thanks
    19
    Thanked 1 Time in 1 Post
    Hi Maud
    Thanks for the code. My currency column varies in its location. This will highlight all the blank cells available in the said range. Is it possible to check particular columns data and if it is currency then check the empty cell condition?
    Regards,
    JD

  10. #10
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Jagdev,

    The following code will cycle through all the cells in the Used Range.

    If the cell is in a column with a header beginning with "Amount" then it will:
    1. Check the format if currency and convert if needed
    2. Highlight if blank.

    If the cell is in a column with a header that does not begin with "Amount", the cell is skipped to the next cell check

    Code:
    Sub test()
    Dim r As Range
    For i = 1 To Sheets.Count 'CYCLE THROUGH SHEETS
        Sheets(i).Activate  'ACTIVATE THE CURRENT SHEET
        For Each r In Sheets(i).UsedRange  'CYCLE THROUGH ALL CELLS IN THE USED RANGE
            If Left(Cells(1, r.Column), 6) = "Amount" Then  'IF COLUMN HEADER FOR CELL STARTS WITH "AMOUNT" THEN
                If r.NumberFormat = """""#,##0.00" Then r.NumberFormat = "_-$* #,##0_-;-$* #,##0_-;_-$* ""-""??_-;_-@_-" 'CONVERT FORMAT TO ACCOUNTING
                If r = "" Then r.Interior.Color = vbYellow  'HIGHLIGHT IF BLANK
            End If
        Next r
    Next i
    NOTE: If you want those cells whose value is zero (represented by a "-" in accounting) to be highlighted, change the blue code line to
    If r = "" Or r = 0 Then r.Interior.Color = vbYellow

    NOTE: The column headers beginning with the word "Amount" can be located anywhere in row 1.

    HTH,
    Maud

  11. #11
    2 Star Lounger
    Join Date
    Feb 2015
    Posts
    128
    Thanks
    19
    Thanked 1 Time in 1 Post
    Hi Maud
    This works like a charm. I was wondering is it possible to check the 2 row for the condition, because the header name gets changed in few of the occasions. I mean to say check 2 row and if it find a data with currency type then perform the operation. Is it possible?
    Regards,
    JD

  12. #12
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Jagdev,

    The following code will cycle through the columns in row 2 to determine which columns have currency and store the column numbers in an array variable. The code will then cycle through all the cells in the Used Range and if it is in one of the currency columns, it will convert the number format to Accounting and highlight empty cells. It does not make any difference where the currency columns are located nor what their header is as long their cells in row 2 have a value.

    HTH,
    Maud

    Code:
    Sub test()
    '-------------------------------
    'DECLARE AND SET VARIABLES
    Dim rng As Range, cell As Range, arrayCol() As Variant, x As Integer, I As Integer, J As Integer
    x = 0
    '-------------------------------
    'CYCLE THROUGH SHEETS
    For I = 1 To Sheets.Count 
        Sheets(I).Activate
    '-------------------------------
    'FIND COLUMNS THAT HAVE CURRENCY AND ASSIGN TO ARRAY VARIABLE
        LastCol = ActiveSheet.Cells(1, Application.Columns.Count).End(xlToLeft).Column
        Set rng = Sheets(I).Range(Cells(1, 1), Cells(1, LastCol))
        For Each cell In rng
            If cell.NumberFormat = "_-$* #,##0_-;-$* #,##0_-;_-$* ""-""??_-;_-@_-" Or _
               cell.NumberFormat = """""#,##0.00" Then
                ReDim Preserve arrayCol(x)
                arrayCol(x) = cell.Column
                x = x + 1
            End If
        Next cell
    '-------------------------------
    'CONVERT NUMBER FORMAT AND HIGHLIGHT EMPTY CELLS
        Set rng = Sheets(I).UsedRange
        For Each cell In rng
            For J = LBound(arrayCol) To UBound(arrayCol)
                If cell.Column = arrayCol(J) Then
                    If cell.NumberFormat = """""#,##0.00" Then cell.NumberFormat = "_-$* #,##0_-;-$* #,##0_-;_-$* ""-""??_-;_-@_-" 'CONVERT FORMAT TO ACCOUNTING
                    If cell = "" Then cell.Interior.Color = vbYellow
                End If
            Next J
        Next cell
    Next I
    End Sub

  13. #13
    2 Star Lounger
    Join Date
    Feb 2015
    Posts
    128
    Thanks
    19
    Thanked 1 Time in 1 Post
    Hi Maud

    Thanks for the above code it works like a charm. Really appreciate your input on this thread. YOu are a savior.

    Regards,
    JD

Tags for this Thread

Posting Permissions

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