none
VBA code to recalculate values in Excel sheet RRS feed

  • Question

  • Hi, does anybody know a VBA code to recalculate array's values in dynamic table?

    I have imported a bunch of text files into one Excel sheet containing few columns of data is Seconds. My mission would be to select those columns and change cells' values from seconds to minutes (divide by 60 every cell). Afterwards I want to plot values on the chart.

    Moreover, It might be that every time different amount of text files are going to be imported....

    As an example portion of my data in Table:

        Temper    Temper    Temper     Temper                Seconds          Seconds       Seconds        Seconds
    37,98885 37,98885 63,92529 31,3809 2190 397,5 1792,5 1230
    37,98885 37,98885 63,92529 31,3809 2190 397,5 1792,5 1230
    37,98885 37,98885 63,92529 31,3809 2190 397,5 1792,5 1230
    37,98885 37,98885 63,92529 31,3809 2190 397,5 1792,5 1230
    37,98885 37,98885 63,92529 31,3809 2190 397,5 1792,5 1230
    37,98885 37,98885 63,92529 31,3809 2190 397,5 1792,5 1230
    37,98885 37,98885 63,92529 31,3809 2190 397,5 1792,5 1230
    37,98885 37,98885 63,92529 31,3809 2190 397,5 1792,5 1230
    37,98885 37,98885 63,92529 31,3809 2190 397,5 1792,5 1230
    Wednesday, December 17, 2014 8:07 AM

Answers

  • Hi,

    You can loop all columns in current worksheet's UsedRange and loop all cells if current column's header equals "Seconds". After that, change every cell's value as you want.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, December 18, 2014 2:03 AM
    Moderator
  • Hi GytasS,

    Here is a sample to caculate the data in the worksheet from A1:

    Sub calc()
    Dim targetSheet As Worksheet
    Set targetSheet = Application.ActiveSheet
    Dim fromColume As Integer
    Dim toColumn As Integer
    Dim lastRow As Integer
    fromColume = 1
    toColumn = targetSheet.UsedRange.End(xlToRight).Column
    lastRow = targetSheet.UsedRange.End(xlDown).Row
    
    For i = fromColume To toColumn
        If targetSheet.Cells(1, i).Value = "Seconds" Then
            For j = 2 To lastRow
                targetSheet.Cells(j, i).Value = targetSheet.Cells(j, i).Value / 60
            Next j
        End If
    Next i
    End Sub

    Also here are some helpful links for you learing Excel developing using VBA:
    Welcome to the Excel 2013 developer reference

    Welcome to the Visual Basic for Applications language reference for Office 2013

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, December 26, 2014 7:26 AM
    Moderator

All replies

  • Hi,

    You can loop all columns in current worksheet's UsedRange and loop all cells if current column's header equals "Seconds". After that, change every cell's value as you want.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, December 18, 2014 2:03 AM
    Moderator
  • Hey! Thank you for good idea and proposal... But I am fairly new at VBA... Could you please help me with the Macros code?

    The data is imported from bunch of text files and the Columns containing cells with seconds have different headers... I just posted like that to show what kind of values columns contain.

    Thank you for help!

    Thursday, December 18, 2014 8:30 AM
  • Hi GytasS,

    Here is a sample to caculate the data in the worksheet from A1:

    Sub calc()
    Dim targetSheet As Worksheet
    Set targetSheet = Application.ActiveSheet
    Dim fromColume As Integer
    Dim toColumn As Integer
    Dim lastRow As Integer
    fromColume = 1
    toColumn = targetSheet.UsedRange.End(xlToRight).Column
    lastRow = targetSheet.UsedRange.End(xlDown).Row
    
    For i = fromColume To toColumn
        If targetSheet.Cells(1, i).Value = "Seconds" Then
            For j = 2 To lastRow
                targetSheet.Cells(j, i).Value = targetSheet.Cells(j, i).Value / 60
            Next j
        End If
    Next i
    End Sub

    Also here are some helpful links for you learing Excel developing using VBA:
    Welcome to the Excel 2013 developer reference

    Welcome to the Visual Basic for Applications language reference for Office 2013

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, December 26, 2014 7:26 AM
    Moderator