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.