VBA to apply formula to existing data in cells

[MOB]

Cells AT6 to CQ "lastrow" contain numeric values, not formulae

I need a bit of code that looks in each cell, divides the value by 7, and multiplies it by the value in row 4

End result should be a value rather than a formula and if the cell was originally blank, it should remain blank

Is this easy to achieve?

TIA

 

[nemmi69]

Try

'cells AT6 to CQ "lastrow" contain numeric values, not formulae
'
'I need a bit of code that looks in each cell, divides the value by 7, and multiplies it by the value in row 4
'
'End result should be a value rather than a formula and if the cell was originally blank, it should remain blank
'
'Is this easy to achieve?
'




Option Explicit
Dim LastRowNo As Long
Dim FirstCol As Integer
Dim LastCol As Integer
Dim RowLoop As Long
Dim ColLoop As Long


Sub DoFormula()
'get the column values
FirstCol = Worksheets("Sheet1").Range("AT6").Column
LastCol = Worksheets("Sheet1").Range("CQ6").Column


LastRowNo = Worksheets("Sheet1").Cells(Rows.Count, "AT").End(xlUp).Row
'do formula
For ColLoop = FirstCol To LastCol
    For RowLoop = 6 To LastRowNo
        If IsNumeric(Worksheets("Sheet1").Cells(RowLoop, ColLoop).Value) = True Then
            Worksheets("Sheet1").Cells(RowLoop, ColLoop).Value = Worksheets("Sheet1").Cells(RowLoop, ColLoop).Value / 7 * Worksheets("Sheet1").Cells(4, ColLoop).Value
        End If
    Next RowLoop
Next ColLoop


End Sub

 

[MOB]

Excellent, many thanks!