Filling array bas on vba formula

[Alternsti]

Hi, I was using the following code to fill a specific cell but not I want to autofill until the end. I'm unable to figure out how to solve this

Sub Test()

    LastRow = Sheets("Existing 2W").Range("B" & Rows.Count).End(xlUp).Row


    Sheets("Existing 2W").Range("S1").Interior.ColorIndex = 5 ' 3 indicates Red Color
    ''''The following two lines are working fine to fill the S2 cell but now I want to fill S2:S
    'Sheets("Existing 2W").Range("S2").Value = WorksheetFunction.Index(Range("Latest_Range"), WorksheetFunction.Match(Left(Right((Sheets("Existing 2W").Range("Z2").Value), 11), 5), Range("LatestLineNo"), 0), 11)
    'Sheets("Existing 2W").Range("S2").Value = Application.Index(Range("Latest_Range"), Application.Match(Left(Right((Sheets("Existing 2W").Range("Z2").Value), 11), 5), Range("LatestLineNo"), 0), 11)
    For i = 1 To LastRow
        'Sheets("Existing 2W").Cells(1, i + 1) = rs.Fields(i).Name
        Sheets("Existing 2W").Cells(1, i + 1).Value = Application.Index(Range("Latest_Range"), Application.Match(Left(Right((Sheets("Existing 2W").Cells(19, i + 1).Value), 11), 5), Range("LatestLineNo"), 0), 11)
    Next i


End Sub

[arnelgp]

you can also use Conditional Format on whole Column.

[Bob Phillips]

You are using the value in S, but overwriting it with the indexed value, doesn't seem right.

[Alternsti]

It's another good solution but I need to use VBA for now.

[Alternsti]

S1 cell will show the header (that's why I put a background color).
S2-end cells (up to the end of columns) will show formula result. My goal is to check when if S2 then fill according to Z2, if S3 then fill according to Z3.

About overwriting the S,... I don't see what you mean.

[Bob Phillips]

you said you wanted to fill an array, and your code comments showed S2:S, now you are saying S2 based on Z2, so I am not clear what cells you want to populate.

[Alternsti]

I need to populate the S column. S1 = header string and S2-end = value from the formula. I need to filldown.

I'm doing several test based on the following cone below (I'm think I'm pretty close to the solution).

Sub NewTest()    
    Dim sh As Worksheet
    Dim rng As Range
    Set sh = Sheets("Existing 2W")
    Set rng = Range("S2:S" & Range("S" & Rows.Count).End(xlUp).Row)
    
    Sheets("Existing 2W").Range("S1").Interior.ColorIndex = 3 ' 3 indicates Red Color
    ''''The following two lines are working fine to fill the S2 cell but now I want to fill S2:S
    'Sheets("Existing 2W").Range("S2").Value = WorksheetFunction.Index(Range("Latest_Range"), WorksheetFunction.Match(Left(Right((Sheets("Existing 2W").Range("Z2").Value), 11), 5), Range("LatestLineNo"), 0), 11)
    'Sheets("Existing 2W").Range("S2").Value = Application.Index(Range("Latest_Range"), Application.Match(Left(Right((Sheets("Existing 2W").Range("Z2").Value), 11), 5), Range("LatestLineNo"), 0), 11)
    With sh
        With .Range("S2")
            .FormulaLocal = "=INDEX(Latest_Range;EQUIV(GAUCHE(DROITE(Z2;11);5);LatestLineNo;0);11)"
            .FormulaArray = .Formula
            .AutoFill rng
        End With
    End With
End Sub

[Paul_Hossler]

I couldn't rest with your formula, but just putting a simple one, something like this maybe

Option Explicit


Sub NewTest()
    Dim sh As Worksheet
    Dim rng As Range
    
    Set sh = Sheets("Existing 2W")
    With sh
        Set rng = .Cells(.Rows.Count, 19).End(xlUp).Offset(1, 0)
        Set rng = Range(.Cells(2, 19), rng)
    
        .Range("S1").Interior.Color = vbRed
    End With
    
'    rng.FormulaLocal = "=INDEX(Latest_Range;EQUIV(GAUCHE(DROITE(Z2;11);5);LatestLineNo;0);11)"
    rng.FormulaLocal = "=10*RAND()"


End Sub

[Bob Phillips]

Now it's a formula you want to create!

As Paul suggests, if you replace

    With sh
        With .Range("S2")
            .FormulaLocal = "=INDEX(Latest_Range;EQUIV(GAUCHE(DROITE(Z2;11);5);LatestLineNo;0);11)"
            .FormulaArray = .Formula
            .AutoFill rng
        End With
    End With

with

    With rng
            .FormulaLocal = "=INDEX(Latest_Range;EQUIV(GAUCHE(DROITE(Z2;11);5);LatestLineNo;0);11)"
    End With

it should work. But are you sure you want to check for last row in column S, that is the column you are injecting the formula in.