String Function not working when an IF statement is present

String Function .xlsm

I have a large pricing spreadsheet that chooses the correct pricing formula based on the part number. I have inserted a basic version. The string function works great when pulling over the pricing formula as long as it doesn't contain a FUNCTION name, ie. IF. I need help working around this problem.

How should I fix the problem? Do you have any ideas on a work around to bring the correct function over rather than using Evaluate?

How do you do that?

The VBA code you sent me to read the value and clear the cell looks like it should work. However, it errors out at the If Not Iserror line. I can't seem to read in the code where it it telling it what to do when it does error, only when it's not an error. Help?



Private Sub Worksheet_Change(ByVal Target As Range)
Dim rCell As Range
Dim vFormula
If Not Intersect(Target, Range("C:C")) Is Nothing Then
Application.EnableEvents = False
For Each rCell In Intersect(Target, Range("C:C")).Cells
If Len(rCell.Value) > 0 Then
vFormula = Application.VLookup(rCell.Value, Sheets("Function examples").Range("A:B"), 2, False)
If Not IsError(vFormula) Then Cells(Target.Row, "J").Formula = "=" & Replace(vFormula, "|", Target.Row)
End If
Next rCell
Application.EnableEvents = True
End If
End Sub

It didn't give an error message, it just stopped and when I evaluated the formula in the Excel Screen it gave a #REF error in the Vlookup. I tried running it again to see if it would give an error message in the VBA and it didn't.

Here is the file.

String Function .xlsm

My apologies. That was an error on my part. Here is the corrected file. I used two new part numbers and their formulas. It is still erring out, however, this time it's the #Value.

I was messing around in the file before trying to understand it and I typed some code in before the END IF. I deleted it and it must not have saved. Your version of the file still contains it.

So, for the second part number it returns a price?

Not seeing why it would work for you and not me. I am working in 2010. Would that have anything to do with it? Will you please send me the corrected file from your end and I will check it on my computer.

I have tried opening it on two different computers and it still will not work. Any suggestions?

I tried again on a different computer and it works fine. Makes me wonder if I have a setting on my program that is stopping it. Thank you for your help. I can go from here.

Joseph,

I just realized another problem has arisen. The larger spreadsheet that I am using this in requires that I copy and paste the part numbers into Column C, about 5-10 lines at a time. It also includes the info for column D,E, F and G. When I copy the data into the spreadsheet the only price in (J) that calculates correctly is the first line. Then it fails to work on the remaining lines.

Thank you. Works beautifully!