VBA: Convert all column data from formula to value based on whether they have data in them?-CodePudding

I have a spreadsheet for our bi-weekly payroll that has an INDEX MATCH formula that pulls an employee's pay based on the pay cycle and of course their position number. I am hoping I could run a macro which would convert the formulas into values once those bi-weekly salaries show up on the table as the year progresses.

Essentially, I don't want the columns shaded in blue's formulas be overwritten when I run this code. I've tracked some threads for formulas where it will do that for the entire workbook/sheet, but I'm at a loss for this specific need.

Example

Thank you!

CodePudding user response：

Try this:

Dim col As Range

For Each col In ActiveSheet.Range("C4:F12").Columns 'adjust range as needed
    'column contains at least one number, and first cell has a formula
    If Application.Count(col) > 0 And col.Cells(1).HasFormula Then
        col.Value = col.Value 'remove any formulas
    End If
Next col

CodePudding user response：

Here's an example how one way to solve this:

The vba code:

Option Explicit

Sub ConvertToValues()

''' Assume table isn't massive and use integers for row numbers
    Dim inC%, inR%, inCountEmpty%, inDataRows%

''' Assume sheet with the target table is the currently active sheet.
    With ActiveSheet.Range("wstEmpPays")
        
    ''' Based on the defined and bordered name-range:
    ''' o  Number of data rows is total rows less heading and top and bottom border rows
        inDataRows = .Rows.Count - 3
        
    ''' Based on the defined and bordered name-range:
    ''' o  Look in column 3  and rows 3 to last-1 (i.e. the data area, excluding the table headings)
    ''' o  Covert all formulas to values, stopping at first 'all empty' column
        For inC = 3 To .Columns.Count

        ''' Ignore columns already converted (i.e. has a value, not formula, in the first data cell)
            If Not .Cells(3, inC).HasFormula Then GoTo procNxtC
            
        ''' NOTE: Here I'm testing for your 'no-value found' returning "" (zero lengths string)
        ''' o  Change this if 'nothing' is represented by some other value
            inCountEmpty = 0
            For inR = 3 To .Rows.Count - 1
                If .Cells(inR, inC) = "" Then inCountEmpty = inCountEmpty   1
            Next inR
            
        ''' If all data cells are nothing: End now (1st 'all empty' column found)
            If inCountEmpty = inDataRows Then Exit For
            
        ''' Otherwise: Convert data columns to values
            For inR = 3 To .Rows.Count - 1
                .Cells(inR, inC) = .Cells(inR, inC)
            Next inR
                    
        ''' Step to next column
procNxtC: Next inC
    End With

End Sub