CODEWITHSUNDEEP
excelvba
Muneeb Hussain
Muneeb Hussain

Reputation: 69

How to solve Formula Error is solved in Macro

I am trying to run this formula in module but unable to figure out what is missing. The Formula is =IF('301'!$F$10=0,"-",'301'!$F$10). Its concept is it will take the values to all those sheets started with number with their relevant Range("F10") and pasted in to "Strength" Sheet started from Range("D4") to the last row

Sub Strength()
    Dim i As Long
    Dim LastRow As Long

    LastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "B").End(xlUp).Row
    Application.ScreenUpdating = False
    For i = 4 To LastRow
    '=IF('301'!$F$10=0,"-",'301'!$F$10)
        Range("D" & i).Formula = "=IF('" & Worksheets(i - 1).Name & "'!$F$10=0, ""-"" & , ' & Worksheets(i - 1).Name & '!$F$10)"
    Next i
    Application.ScreenUpdating = True
End Sub

I grateful if the problem is solved.

Thanks & Regards

Muneeb

Upvotes: 0

Views: 58

Answers (2)

Алексей Р
Алексей Р

Reputation: 7627

Using a formula template improves code clarity, avoiding errors with quotes, and outputting an array to a worksheet in a single operation increases speed. It is also advisable to specify precisely the books, worksheets, and ranges to be processed

Option Explicit

Sub Strength()
    Dim i As Long, LastRow As Long, arr
    Const FORMULA_T = "=IF('#'!$F$10=0,""-"",'#'!$F$10)"  'a template; # will be replaced by WS names
    
    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
        ReDim arr(4 To LastRow, 1 To 1) 'make the array to store the formulae before output to the WS
        For i = 4 To LastRow
            arr(i, 1) = Replace(FORMULA_T, "#", .Parent.Worksheets(i - 1).Name)   ' fill the arr with formulae
        Next i
        .Range("D4").Resize(UBound(arr) - LBound(arr) + 1).Formula = arr 'output the array to the WS at once
    End With
End Sub

Upvotes: 1

xShen
xShen

Reputation: 572

Try

Range("D" & i).Formula = "=IF("& Worksheets(i- 1).Name &"!$F$10=0,""-"","& Worksheets(i- 1).Name &"!$F$10)"

or if you must the single quote (gap in worksheet name) then use

Range("D" & i).Formula = "=IF('"& Worksheets(i- 1).Name &"'!$F$10=0,""-"",'"& Worksheets(i- 1).Name &"'!$F$10)"

Upvotes: 1

Related Questions