CODEWITHSUNDEEP
vbaexcelloopsfor-loop
lydias
lydias

Reputation: 841

VBA Passing Array values to a pre-defined String

This is a continuation of a past question: VBA Fill cells on multiple sheets with formulas

As an improved version of my previous code, I'm curious on how to assigned array values to strFormulas(2). I tried to use the following loops to assign array to every sheets that the loop lands on.

I hope to achieve:

Table 1 and Table 2 are separate sheets in the same workbook

If loop lands on Table 1 (sheet index = 2) then strFormulas(2) = "=IF('Table 1 S'!N1838="S","S","")"

If loop lands on Table 2 (sheet index = 3) then strFormulas(2) = "=IF('Table 2 S'!N1838="S","S","")"

However, with the following loop, nT is not replaced by values in array nTable. It is carried over as a string and generates formula -> "=IF(nT!N1838="S","S","")"

 Dim nT As Variant, nTable As Variant

 nTable = Array("'Table 1 S'", "'Table 2 S'")

 Dim w As Long
 Dim strFormulas(1 To 2) As Variant

  For w = 2 To ActiveWorkbook.Worksheets.Count
  With ActiveWorkbook.Worksheets(w)

    strFormulas(1) = "=IF(ISBLANK('Sheet 1'!A4),"""",'Sheet 1'!A4)"
    .Range("A2:AJ2").Formula = strFormulas(1)
    .Range("A2:AJ2000").FillDown
    'I believe here I need to define nT in terms of active sheet, but I can't think of an efficient way to do so...

    For Each nT In nTable
    strFormulas(2) = "=IF(nT!N1838=""S"",""S"","""")"
    .Range("AK2:AR2").Formula = strFormulas(2)
    .Range("AK2:AR2000").FillDown
    next nT

  End With
 Next w

Appreciate any help!

Upvotes: 0

Views: 94

Answers (1)

QHarr
QHarr

Reputation: 84465

Like this? Credit to @Jeeped for the formula part using char and text.

Public Sub AddFormulas()
    Dim nTable As Variant, w As Long
    Application.ScreenUpdating = False

    nTable = Array("Table 1", "Table 2") 'assumes these exist otherwise needs error handling

    For w = LBound(nTable) To UBound(nTable)
        With ActiveWorkbook.Worksheets(nTable(w))
                .Range("AK2:AR2000").Formula = "=IF('" & .Name & " S'!N1838=char(83), char(83), text(,))
        End With
    Next w
    Application.ScreenUpdating = True
End Sub

Upvotes: 1

Related Questions