VBA Passing Array values to a pre-defined String

Question

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!

Is there only one table per sheet? Are the tables actual Excel tables not just ranges? And where in the table are you putting the formula? In all columns? — QHarr
– QHarr, Commented Apr 15, 2018 at 11:02
@QHarr Table 1 is a sheet and Table 2 is another sheet. Tables here are sheet names. I'm putting the formula in the range that has already been defined for strFormula(2), which is columns AK to AR. — lydias
– lydias, Commented Apr 15, 2018 at 11:05

QHarr · Accepted Answer · 2018-04-16 15:28:43Z

1

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

edited Apr 16, 2018 at 15:28

answered Apr 15, 2018 at 11:10

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

lydias Over a year ago

oh wow, I will test it out later, but I think this is what I'm looking for. I'm too new to VBA there are so many tricks.

lydias Over a year ago

I have not tested the code had a busy weekend, I will let you know if it worked.. later today. Thank you!

QHarr Over a year ago

The S is there referenced by its Char(). If you put an S in 'Table 2'!N1838 for example you will see it appear.

lydias Over a year ago

how do I add 'S' after table 1 and table 2.. ? Was it a typo in the solution? Can I just add it in the Array? I'm not trying to get the name of the current table that the loop lands on. For table 1 it will refer to table 1 S in the formula.. Sorry your previous comment came to fast, I'm trying it now..

QHarr Over a year ago

The formula inserted, for example in Table 1 sheet is =IF('Table 1'!N1838=CHAR(83), CHAR(83), TEXT(,)) which is the same as =IF('Table 1'!N1838= "S", "S", "") . You can test by putting "S" in N1838 and then looking at AK2 for example.

|

Collectives™ on Stack Overflow

VBA Passing Array values to a pre-defined String

1 Answer 1

12 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related