Vba code with if statement and index

[brickwall015]

Hello

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("D4:D40")) Is Nothing Then Exit Sub
If Target = "INPUT WALL" Then
Cells(Target.Row, "E") = " "
Cells(Target.Row, "E").Interior.ColorIndex = 15

Else
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

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[brickwall015]

nope... well that is assuming you ment to just change the formula line. I don't think sub brickwall015() would do anything.

[JOHN H. DAVIS]

Yes, I did mean just to change the formula line within the macro.

Maybe:

Please Login or Register  to view this content.

[brickwall015]

No, it says "Range of Object worksheet failed"

what is happening with the formula I have now is that if the cell in column D does not say "INPUT WALL" everything is fine. And if the next row has input wall, I am able to input a value into row E, but then, if the row below is NOT input wall, then it replaces the value I inputted with "#N/A" and if you click on the cell, it shows the index formula.

It seems like, the way this is set up now, that the whole column is either going to allow a user input, or it will index, but i want each row to allow both of those options. I am sure it has to do with the Range(D4:D40), but I don't know how else to do it so that each row will be independent but the code will work over the whole range.

[brickwall015]

Okay, so I was able to allow the rows to be independent by changing

if Target = "INPUT WALL" Then

to

Cells(Target.Row, "D") = "INPUT WALL" Then

however, now it does not accept the INDEX formula, which is

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,)))"

because it is only looking at row 4, even if I am in a different row. and it says the same thing it did before, about "range of object worksheet failed'

[brickwall015]

and if I change the range of the formula to say Range("E4:E40").Formula = ... then I get the same problem as before, it fills the whole column with the formula, whether it says "INPUT WALL" or not

[brickwall015]

if I upload the file could you possibly help me?

[JOHN H. DAVIS]

Sure. I'd be glad too take a look at it.

[brickwall015]

how do i upload a file?

[brickwall015]

here it is

[brickwall015]

so the grey boxes are all lists that the user selects, and if they select "INPUT WAll" I want them to input a wall thickness. Otherwise I want it to execute the index formula. But I can't seem to get it to work and index with the individual row.

[brickwall015]

any luck? I would really like to figure this out soon!! Thanks in advance

[JOHN H. DAVIS]

I am not sure how this is supposed to work, but this change will input the formula with the correct row.

Please Login or Register  to view this content.

[brickwall015]

it works!!

THANK YOU SO MUCH, YOUR A GENIUS!!

[brickwall015]

the only thing is that if I clear all of the cells, to input things into the different drop down list, I get a runtime error 14 Type mismatch

And when I click debug, it hi lights the "If Target.Value = "INPUT WALL" Then" line... any way to have it so that if D4 is nothing then it ends the sub?

[brickwall015]

Runtime error 13

[JOHN H. DAVIS]

Try:

Please Login or Register  to view this content.

[brickwall015]

IT is doing something crazy!!

HAHA once i select from the list in B4, all of the cells in row 4 have #N/A, like from colmn B until the end of the sheet.

And when i clear it I still get the same error :/

[JOHN H. DAVIS]

I don't know what it is designed to do. I only was concerned with adding the row number to the formula as a cell was selected in Range("D4:D40"). On testing that it worked on the example sheet you provided. However, you have other formulas and links in the sample and I can't tell how they interact with each other.

[brickwall015]

I figured it out, Target.Value = "Input Wall" needed to be target.text

THANKS AGAIN!

[JOHN H. DAVIS]

You're welcome. Glad to hear that, and thanks for the feedback. Please comply with Forum Rule No. 9.


9. Acknowledge the responses you receive, good or bad. If your problem is solved, please say so clearly, and mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved". Or click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Dropdown option or Edit button will not appear -- ask a moderator to mark it.

[brickwall015]

well it isn't totally working actually.
It works once, then doesn't work... it is really weird

[brickwall015]

well with a little bit of messing around, here is the final code, and it works like i wanted it too!

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("D4:D40")) Is Nothing Then
If Target.Text = " " Then Exit Sub

If Target.Text = "INPUT WALL" Then
Target.Offset(, 1).Value = " "
Target.Offset(, 1).Interior.ColorIndex = 15

Else
Target.Offset(, 1).Formula = "=IF(D" & Target.Row & "=0,0, INDEX(Piping!$B$2:$AC$27,MATCH(C" & Target.Row & ",Piping!$B$2:$B$27,),MATCH(D" & Target.Row & ",Piping!$B$2:$AC$2,)))"
Target.Offset(, 1).Interior.ColorIndex = 2
End If
End If

End Sub