CODEWITHSUNDEEP
vbaexcel
ShakeelAhmad Mscit
ShakeelAhmad Mscit

Reputation: 57

Excel VBA Macro Formula Help Regarding not show formula in formula bar

I want to use if condition formula to calculate the cube via vba macro button. I write code and its working is fine but code is show in formula bar and also working with formula come from vba macro and remain their, I want that this formula did not should be show in formula bar and the calculation should be done only via macro button when i press it. here is my code. Thanks

Sub CalCubMeter()

Worksheets("sheet1").Range("d3:d21").Formula = "=if(c3=C3,PRODUCT($c3^3),""-"")"

End Sub

Upvotes: 0

Views: 481

Answers (1)

QHarr
QHarr

Reputation: 84455

Something along the lines of:

Public Sub CubeColumnC
    Dim wb as Workbook
    Dim ws as Worksheet
    Dim sourceRange as Range
    Dim sourceArr()
    Dim i as long

    Set wb = ThisWorkbook
    Set ws = wb.Sheets("Sheet1") 'change to as needed
    Set sourceRange = ws.Range("C3:C21")

    'Assign column c values to array
    sourceArr = Application.Transpose(sourceRange.Value)

    With ws
        For i = Lbound(sourceArr) To Ubound(sourceArr) 'Write back out to col D
            If .Cells(2+i, "C") = vbNullString Then   'if blank
                .Cells(2+i, "D") = "-"
            Else
                .Cells(2+i,"D")  = sourceArr(i)^3 'Calc the Cube value
            End If
       Next i
   End With
End Sub

I ignored your C3 = C3 as this is always True, amend as you see fit.

Edit: Application.WorksheetFunction.Power(sourceArr(i),3) changed to sourceArr(i)^3 for Cube value

Upvotes: 1

Related Questions