Home VBA Passing Array values to a pre-defined String
Reply: 0

VBA Passing Array values to a pre-defined String

user1549
1#
user1549 Published in May 21, 2018, 3:02 am

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!

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.305232 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO