• 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.

Loop through table columns and set the entire column format based on a condition

I

inddon

Member
Hello There,

I have a table with a few columns, and I would like to loop through the first row and all the table columns and check it's values. If a column value satisfies the criteria then set the entire column format to a certain format. There is a small conversion for the column where there is a certain system date value (below point 2).

I have the basic Function in the module, to check:
1. If the value is Scientific
2. If the value is a system date with certain text in it.


Please find the attached a sample workbook with the table/explanation and the desired output for your reference. I would like to know the VBA code.

Thank you & regards,
Don
 

Attachments

  • Sample workbook.xlsm
    19 KB · Views: 7
Hello, according to your attachment a VBA demonstration for starters in order to get real dates :​
Code: 
Sub Demo1()
    With Sheet1.ListObjects(1).DataBodyRange.Columns
        With .Item(5)
             .NumberFormat = " dd-mmm-yyyy hh:mm:ss "
             .Value2 = .Parent.Evaluate(Replace("IF(ISTEXT(#),SUBSTITUTE(LEFT(#,19),""T"","" ""),#)", "#", .Address))
        End With
             .Item("G:H").NumberFormat = "#"
    End With
End Sub
Do you like it ? So thanks to click on bottom right Like !​
​
 
In your expected results table in column F you have text representations of a date and not a real date; is this what you want?
Power Query has built-in functionality to handle timezones. So I attach an offering with trepidation since:
  • it doesn't do the transformation in situ
  • doesn't use VBA
  • it returns real dates
  • it assumes everything is text except for columns SR. and UPDATE_DATE (because I'd guess you don't want to do arithmetic on the likes of BATCH_ID & HEADER_ID

I've made a small change to your source data in cell F5 to add a 5 hour time zone difference to demonstrate how it could handle those time zones (see result in cell F32).
You can change the source data; to update the result table right-click in it and choose Refresh.
 

Attachments

  • Chandoo47864Sample workbook.xlsm
    29 KB · Views: 1
Marc L said:
Hello, according to your attachment a VBA demonstration for starters in order to get real dates :​
Code: 
Sub Demo1()
    With Sheet1.ListObjects(1).DataBodyRange.Columns
        With .Item(5)
             .NumberFormat = " dd-mmm-yyyy hh:mm:ss "
             .Value2 = .Parent.Evaluate(Replace("IF(ISTEXT(#),SUBSTITUTE(LEFT(#,19),""T"","" ""),#)", "#", .Address))
        End With
             .Item("G:H").NumberFormat = "#"
    End With
End Sub
Do you like it ? So thanks to click on bottom right Like !​
​
Click to expand...


Thank you Marc L for your response and the sample code. It works perfect for the current table that is displayed in the worksheet.

However, as mentioned in the sample workbook I would like (to make this more generic) to loop through all the columns in the first row only and identify whether the column value satisfies the criteria to change the format. If yes then apply the conversion and format to the entire column.

Would be happy to see if this could be achieved.

Regards,
Don
 
p45cal said:
In your expected results table in column F you have text representations of a date and not a real date; is this what you want?
Power Query has built-in functionality to handle timezones. So I attach an offering with trepidation since:
  • it doesn't do the transformation in situ
  • doesn't use VBA
  • it returns real dates
  • it assumes everything is text except for columns SR. and UPDATE_DATE (because I'd guess you don't want to do arithmetic on the likes of BATCH_ID & HEADER_ID
I've made a small change to your source data in cell F5 to add a 5 hour time zone difference to demonstrate how it could handle those time zones (see result in cell F32).
You can change the source data; to update the result table right-click in it and choose Refresh.
Click to expand...


Thank you p45Cal for your quick response and help. Powerquery is something new for me, has not yet tried. Just responded if the requirement could be achieved in a more generic way to identify the column if it meets the conversion criteria, and then do the necessary conversion using VBA.

Regards,
Don
 
inddon said:
I would like (to make this more generic)
Click to expand...
A generic Excel basics VBA demonstration :​
Code: 
Sub Demo2()
         Dim Rc As Range
    For Each Rc In ActiveSheet.ListObjects(1).DataBodyRange.Columns
        With Rc.Cells(1)
            If IsNumeric(.Value2) And .Text Like "*E+*" Then
                Rc.NumberFormat = "#"
            ElseIf .Value2 Like "####-##-##T##:##:##*" Then
                Rc.NumberFormat = " dd-mmm-yyyy hh:mm:ss "
                Rc.Value2 = Evaluate("IF({1},SUBSTITUTE(LEFT(" & Rc.Address & ",19),""T"","" ""))")
            End If
        End With
    Next
End Sub
You may Like it !​
​
 
Hello @Marc L

I have one last column to convert. I tried to follow your given code but couldn't make it. Therefore the request.

3rd Elseif statement
ElseIf IsDate(.Value) And .Value <> .Text Then 'This is for period_names

As seen in the below snapshot, the period name column displayed, eg. Oct 21, and the the value displayed is always the 1st day of that month. I would like the Value to be the same as Text (format General/Text), whatever the text is

Could you please help me in knowing the VBA code for this conversion? Many thanks

78450


Code: 
Sub Demo2()
    Dim Rc As Range
    For Each Rc In ActiveSheet.ListObjects(1).DataBodyRange.Columns
        With Rc.Cells(1)
            If IsNumeric(.Value2) And .Text Like "*E+*" Then
                Rc.NumberFormat = "#"
            ElseIf .Value2 Like "####-##-##T##:##:##*" Then
                Rc.NumberFormat = " dd-mmm-yyyy hh:mm:ss "
                Rc.Value2 = Evaluate("IF({1},SUBSTITUTE(LEFT(" & Rc.Address & ",19),""T"","" ""))")
            '-----------------------------------------------
            ElseIf IsDate(.Value) And .Value <> .Text Then 'This is for period_names
            '-----------------------------------------------

                'Need to convert/remove all the value and make it same as seen in the cell Text/General Format (whatever the text is) 


            End If
        End With
    Next
End Sub



Regards,
Don
 

Attachments

  • 1649869580122.png
    1649869580122.png
    11.2 KB · Views: 1
Last edited:
As with a picture it's nothing but just a guessing challenge (very not the purpose of any Excel forum)​
so you may have to update the number format used within this demonstration revamped :​
Code: 
Sub Demo2r()
         Dim Rc As Range
    For Each Rc In ActiveSheet.ListObjects(1).DataBodyRange.Columns
        With Rc.Cells(1)
            If IsNumeric(.Value2) Then
                If .Text Like "*E+*" Then
                    Rc.NumberFormat = "#"
                ElseIf .NumberFormat = "mmm yy" Then
                    Rc.NumberFormat = "@"
                    Rc.Value2 = Evaluate("IF({1},TEXT(" & Rc.Address & ",""mmm yy""))")
                End If
            ElseIf .Value2 Like "####-##-##T##:##:##*" Then
                Rc.NumberFormat = " dd-mmm-yyyy hh:mm:ss "
                Rc.Value2 = Evaluate("IF({1},SUBSTITUTE(LEFT(" & Rc.Address & ",19),""T"","" ""))")
            End If
        End With
    Next
End Sub
You could Like it !
​
 
Marc L said:
As with a picture it's nothing but just a guessing challenge (very not the purpose of any Excel forum)​
so you may have to update the number format used within this demonstration revamped :​
Code: 
Sub Demo2r()
         Dim Rc As Range
    For Each Rc In ActiveSheet.ListObjects(1).DataBodyRange.Columns
        With Rc.Cells(1)
            If IsNumeric(.Value2) Then
                If .Text Like "*E+*" Then
                    Rc.NumberFormat = "#"
                ElseIf .NumberFormat = "mmm yy" Then
                    Rc.NumberFormat = "@"
                    Rc.Value2 = Evaluate("IF({1},TEXT(" & Rc.Address & ",""mmm yy""))")
                End If
            ElseIf .Value2 Like "####-##-##T##:##:##*" Then
                Rc.NumberFormat = " dd-mmm-yyyy hh:mm:ss "
                Rc.Value2 = Evaluate("IF({1},SUBSTITUTE(LEFT(" & Rc.Address & ",19),""T"","" ""))")
            End If
        End With
    Next
End Sub
You could Like it !​
​
Click to expand...

Thank you for the sample code. My excuses. Please find attached sample workbook (Sheet2) for your reference.

Somehow the control did not go into the expected Elseif. Below snapshot of the cell format, and the format via VAB Msgbox. Both look different

Based on your code, I added another ElseIf. I am sure there would be a smarter way to do it than my starter ElseIf code.

78461


Regards,
Don
 

Attachments

  • Sample workbook.xlsm
    26.4 KB · Views: 3
According to your last attachment like I wrote « you may have to update the number format used within this demonstration »​
​
so the codeline must be ElseIf .NumberFormat Like "mm*yy*" Then …​
​
 
You must log in or register to reply here.
Back
Top