Inserting a formula not working correctly

[crystalneedshelpplzthnx]

Hi All, I have been searching and changing this code and nothing has worked. I have been running the code after copying the formula as a work around. Can anyone explain what it is I am doing incorrectly. Ive tried the following:

Sub Key_Items_Validation1()

Sheets("ProductivityData MTD").Select

    Range("y1").Select
    ActiveCell.FormulaR1C1 = "Validation"
    
    Rows("1:1").Select
    Selection.AutoFilter
    Selection.AutoFilter
'I have a filter and need it to also include the new column being added, so I turn it off and back on
    
 Range("y2").Formula = "IFERROR(IF(OR(VALUE(LEFT(D2,4))=1653,VALUE(LEFT(D2,4))=1717,VALUE(LEFT(D2,4))=1802,VALUE(LEFT(D2,4))=1803,VALUE(LEFT(D2,4))=1804,VALUE(LEFT(D2,4))=1805,VALUE(LEFT(D2,4))=2659,VALUE(LEFT(D2,4))=2664,VALUE(LEFT(D2,4))=2665,VALUE(LEFT(D2,4))=2666,VALUE(LEFT(D2,4))=2667,VALUE(LEFT(D2,4))=2702,VALUE(LEFT(D2,4))=2729,VALUE(LEFT(D2,4))=2730,VALUE(LEFT(D2,4))=2731,VALUE(LEFT(D2,4))=2747,VALUE(LEFT(D2,4))=2750,VALUE(LEFT(D2,4))=2862,VALUE(LEFT(D2,4))=2863,VALUE(LEFT(D2,4))=2864,VALUE(LEFT(D2,4))=2865,VALUE(LEFT(D2,4))=2866,VALUE(LEFT(D2,4))=2867,VALUE(LEFT(D2,4))=2972),VALUE(VLOOKUP(A2&""*"",'Call Report Data MTD'!A:A,1,0))=A2,""""),""Not Found"")"

Range("y2:y" & Sheets("ProductivityData MTD").Range("A" & Rows.Count - 1).End(xlUp).Row).Select

Selection.FillDown

End Sub

And

Sub Key_Items_Validation2()Sheets("ProductivityData MTD").Select

    Range("y1").Select
    ActiveCell.FormulaR1C1 = "Validation"
    
    Rows("1:1").Select
    Selection.AutoFilter
    Selection.AutoFilter
    
    Range("y2").Select
      ActiveCell.FormulaR1C1 = "=IFERROR(IF(OR(VALUE(LEFT(R[-2]C[-21],4))=1653,VALUE(LEFT(R[-2]C[-21],4))=1717,VALUE(LEFT(R[-2]C[-21],4))=1802,VALUE(LEFT(R[-2]C[-21],4))=1803,VALUE(LEFT(R[-2]C[-21],4))=1804,VALUE(LEFT(R[-2]C[-21],4))=1805,VALUE(LEFT(R[-2]C[-21],4))=2659,VALUE(LEFT(R[-2]C[-21],4))=2664,VALUE(LEFT(R[-2]C[-21],4))=2665,VALUE(LEFT(R[-2]C[-21],4))=2666,VALUE(LEFT(R[-2]C[-21],4))=2667,V" & _
        "T(R[-2]C[-21],4))=2702,VALUE(LEFT(R[-2]C[-21],4))=2729,VALUE(LEFT(R[-2]C[-21],4))=2730,VALUE(LEFT(R[-2]C[-21],4))=2731,VALUE(LEFT(R[-2]C[-21],4))=2747,VALUE(LEFT(R[-2]C[-21],4))=2750,VALUE(LEFT(R[-2]C[-21],4))=2862,VALUE(LEFT(R[-2]C[-21],4))=2863,VALUE(LEFT(R[-2]C[-21],4))=2864,VALUE(LEFT(R[-2]C[-21],4))=2865,VALUE(LEFT(R[-2]C[-21],4))=2866,VALUE(LEFT(R[-2]C[-21],4)" & _
        "LUE(LEFT(R[-2]C[-21],4))=2972),VALUE(VLOOKUP(R[-2]C[-24]&""*"",'Call Report Data MTD'!C[-24],1,0))=R[-2]C[-24],""""),""Not Found"")"

Range("y2:y" & Sheets("ProductivityData MTD").Range("A" & Rows.Count - 1).End(xlUp).Row).Select
Selection.FillDown
End Sub

I can only get it to work this way:

Sub Key_Items_Validation()
Sheets("ProductivityData MTD").Select

    Range("y1").Select
    ActiveCell.FormulaR1C1 = "Validation"
    
    Rows("1:1").Select
    Selection.AutoFilter
    Selection.AutoFilter
    
    Range("y2").Select
    ActiveSheet.Paste

Range("y2:y" & Sheets("ProductivityData MTD").Range("A" & Rows.Count - 1).End(xlUp).Row).Select
Selection.FillDown
End Sub

Which I run after copying the formula. Any help would be greatly appreciated.

Thank you
Crystal

 

[Nutster]

Key_Items_Validation will be executed to handle the Validation event from the Key_Items object. The Subs named Key_Items_Validation1 and Key_Items_Validation2 will not get triggered by an event because there is no event called Validation1 nor Validation2. In VBA an event handler must be named Object_Event. Anything else and it will be skipped, unless you call it directly.

Public Sub Key_Items_Validation()
    Key_Items_Validation1
    Key_Items_Validation2
End Sub

This will call the two non-event handlers from within the properly named event handler.

 

[shg]

How about instead,

  ActiveCell.FormulaR1C1 = _
  "=IFERROR(IF(OR(--LEFT(R[-1]C[-1],4) = {1653,1717,1802,1803,1804,1805,2659,2664,2665,2666,2667,2702,2729,2730,2731,2747,2750,2862,2863,2864,2865,2866,2867,2972}," & Chr(10) & "--VLOOKUP(R[-1]C[-4] & ""*"", 'Call Report Data MTD'!C[-4], 1, 0)) = R[-1]C[-4], """"), ""Not Found"")"

 

[crystalneedshelpplzthnx]

Key_Items_Validation will be executed to handle the Validation event from the Key_Items object. The Subs named Key_Items_Validation1 and Key_Items_Validation2 will not get triggered by an event because there is no event called Validation1 nor Validation2. In VBA an event handler must be named Object_Event. Anything else and it will be skipped, unless you call it directly.

Public Sub Key_Items_Validation()
    Key_Items_Validation1
    Key_Items_Validation2
End Sub

This will call the two non-event handlers from within the properly named event handler.

Hmmm, I'm not intending to call an 'event'. I intending to paste a formula into the cell...I just named it differently because I was experimenting with different codes...Neither 1 or 2 is working. Once I get it to work, it won't have a number on it at all. It would look like this:

Sub Key_Items_Validation()

Sheets("ProductivityData MTD").Select

    Range("y1").Select
    ActiveCell.FormulaR1C1 = "Validation"
    
    Rows("1:1").Select
    Selection.AutoFilter
    Selection.AutoFilter
    
    Range("y2").Select
      ActiveCell.FormulaR1C1 = "=IFERROR(IF(OR(VALUE(LEFT(R[-2]C[-21],4))=1653,VALUE(LEFT(R[-2]C[-21],4))=1717,VALUE(LEFT(R[-2]C[-21],4))=1802,VALUE(LEFT(R[-2]C[-21],4))=1803,VALUE(LEFT(R[-2]C[-21],4))=1804,VALUE(LEFT(R[-2]C[-21],4))=1805,VALUE(LEFT(R[-2]C[-21],4))=2659,VALUE(LEFT(R[-2]C[-21],4))=2664,VALUE(LEFT(R[-2]C[-21],4))=2665,VALUE(LEFT(R[-2]C[-21],4))=2666,VALUE(LEFT(R[-2]C[-21],4))=2667,V" & _
        "T(R[-2]C[-21],4))=2702,VALUE(LEFT(R[-2]C[-21],4))=2729,VALUE(LEFT(R[-2]C[-21],4))=2730,VALUE(LEFT(R[-2]C[-21],4))=2731,VALUE(LEFT(R[-2]C[-21],4))=2747,VALUE(LEFT(R[-2]C[-21],4))=2750,VALUE(LEFT(R[-2]C[-21],4))=2862,VALUE(LEFT(R[-2]C[-21],4))=2863,VALUE(LEFT(R[-2]C[-21],4))=2864,VALUE(LEFT(R[-2]C[-21],4))=2865,VALUE(LEFT(R[-2]C[-21],4))=2866,VALUE(LEFT(R[-2]C[-21],4)" & _
        "LUE(LEFT(R[-2]C[-21],4))=2972),VALUE(VLOOKUP(R[-2]C[-24]&""*"",'Call Report Data MTD'!C[-24],1,0))=R[-2]C[-24],""""),""Not Found"")"

Range("y2:y" & Sheets("ProductivityData MTD").Range("A" & Rows.Count - 1).End(xlUp).Row).Select
Selection.FillDown
End Sub

in cell y2 I want the formula:

=IFERROR(IF(OR(VALUE(LEFT(D2,4))=1653,VALUE(LEFT(D2,4))=1717,VALUE(LEFT(D2,4))=1802,VALUE(LEFT(D2,4))=1803,VALUE(LEFT(D2,4))=1804,VALUE(LEFT(D2,4))=1805,VALUE(LEFT(D2,4))=2659,VALUE(LEFT(D2,4))=2664,VALUE(LEFT(D2,4))=2665,VALUE(LEFT(D2,4))=2666,VALUE(LEFT(D2,4))=2667,VALUE(LEFT(D2,4))=2702,VALUE(LEFT(D2,4))=2729,VALUE(LEFT(D2,4))=2730,VALUE(LEFT(D2,4))=2731,VALUE(LEFT(D2,4))=2747,VALUE(LEFT(D2,4))=2750,VALUE(LEFT(D2,4))=2862,VALUE(LEFT(D2,4))=2863,VALUE(LEFT(D2,4))=2864,VALUE(LEFT(D2,4))=2865,VALUE(LEFT(D2,4))=2866,VALUE(LEFT(D2,4))=2867,VALUE(LEFT(D2,4))=2972),VALUE(VLOOKUP(A2&"*",'Call Report Data MTD'!A:A,1,0))=A2,""),"Not Found")

 

[crystalneedshelpplzthnx]

How about instead,

  ActiveCell.FormulaR1C1 = _
  "=IFERROR(IF(OR(--LEFT(R[-1]C[-1],4) = {1653,1717,1802,1803,1804,1805,2659,2664,2665,2666,2667,2702,2729,2730,2731,2747,2750,2862,2863,2864,2865,2866,2867,2972}," & Chr(10) & "--VLOOKUP(R[-1]C[-4] & ""*"", 'Call Report Data MTD'!C[-4], 1, 0)) = R[-1]C[-4], """"), ""Not Found"")"

It ran without error, but did not give me the result I was expecting. Let me play around with it a bit. thx for your help!

 

[crystalneedshelpplzthnx]

It ran without error, but did not give me the result I was expecting. Let me play around with it a bit. thx for your help!

Simply Amazing! Thnx you thank you thank you @shg
I have been trying to use an or statement like that forever, now I know how to do it correctly, as well as...my macro runs as it should....THANK YOU THANK YOU THANK YOU!!!

Sub Key_Items_Validation()
Sheets("ProductivityData MTD").Select
    Range("y1").Select
    ActiveCell.FormulaR1C1 = "Validation"
    
    Rows("1:1").Select
    Selection.AutoFilter
    Selection.AutoFilter
    
    Range("y2").Select
       
       ActiveCell.FormulaR1C1 = _
       "=IFERROR(" & Chr(10) & "IF(OR(VALUE(LEFT(RC[-21],4)) = {1653,1717,1802,1803,1804,1805,2659,2664,2665,2666,2667,2702,2729,2730,2731,2747,2750,2862,2863,2864,2865,2866,2867,2972})," & Chr(10) & "VALUE(VLOOKUP(RC[-24] & ""*"", 'Call Report Data MTD'!C[-24], 1, 0))=RC[-24]," & Chr(10) & " """")," & Chr(10) & " ""Not Found"")"
    
Range("y2:y" & Sheets("ProductivityData MTD").Range("A" & Rows.Count - 1).End(xlUp).Row).Select
Selection.FillDown
End Sub

Crystal

 

[shg]

You're welcome.