CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2013

    Excel, if statement and index in code


    I am trying to create a code that will either allow the user to input a value, or INDEX a value depending on the input in the column before. I have figured out how to allow the user to input the value, thanks to some one else on this Forum, but I can't seem to get the INDEX formula right.

    As of now, this is my code:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("D440")) Is Nothing Then Exit Sub
    If Cells(Target.Row, "D") = "INPUT WALL" Then
    Cells(Target.Row, "E") = " "
    Cells(Target.Row, "E").Interior.ColorIndex = 15

    Range(Target.Row, "E").Formula = "=IF(B4=0,0, INDEX(Piping!$B$2:$AC$27,MATCH(C4,Piping!$B$2:$B$27,),MATCH(D4,Piping!$B$2:$AC$2,)))"
    Cells(Target.Row, "E").Interior.ColorIndex = 2
    End If
    End Sub

    the INDEX however is only looking at C4 and D4 for the whole range from 4 to 40.

    I want it to be like a normal formula you put into a cell and drop the box down. So that when I am in row 5, it is indexing c5 and D5, and when I am in row 6, it is indexing C6 and D6, and so on. because as of now, no matter what row I am in, it still index's C4 and D4. But I only want this to happen if "INPUT WALL" is not selected in column D for that particular Row

  2. #2
    Join Date
    Jan 2006
    Fox Lake, IL

    Re: Excel, if statement and index in code

    try "$"&D&6

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

Posting Permissions

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

Windows Mobile Development Center

Click Here to Expand Forum to Full Width

On-Demand Webinars (sponsored)

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.