Consulting

Results 1 to 9 of 9

Thread: Filling array bas on vba formula

  1. #1

    Filling array bas on vba formula

    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

  2. #2
    you can also use Conditional Format on whole Column.

  3. #3
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,415
    Location
    You are using the value in S, but overwriting it with the indexed value, doesn't seem right.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

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

  5. #5
    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.

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,415
    Location
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  7. #7
    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

  8. #8
    VBAX Sage
    Joined
    Apr 2007
    Posts
    8,319
    Location
    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
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  9. #9
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,415
    Location
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •