0

So I am trying to come up with a VBA code that uses a vlookup only if certain criteria is met. The criteria is a bit difficult to me because it somehow needs to traceback and pull information once criteria is met. I need a macro that uses a vlookup on duplicate IDs that have different values. The macro is supposed to ignore if the values of the same ISIN contain all positive numbers. But if one negative number exists amongst the duplicate numbers, all values containing that ISIN including positive should populate.

I understand that this solution will contain if value > 1 but I am unsure how to get the macro to grab all the values from that specific ISIN when a positive number is found

Sub Crit()
'Created through Macro record    
    If ("D2") > 1 Then        
       Range("N2").Select
       ActiveCell.FormulaR1C1 = _
            "=IF(VLOOKUP(RC[-13],RC[-13]:R[48]C[-9],4,FALSE)<1,RC[-10],"""")"
       Range("N2").Select
       ActiveCell.FormulaR1C1 = _
           "=IF(VLOOKUP(RC[-13],RC[-13]:R[48]C[-9],4,FALSE)>1,RC[-10],"""")"
       Range("N2").Select
End Sub

Expected results would be a button that once data is uploaded in a static fashion, deletes all the rows with the ISINS that contain purely positive numbers and populate the ones that contain positive and negative.Example of what macro should do. But would prefer it delete the rows that are not being populated entirely.

RayCharles
  • 1
  • 1
  • Maybe more detail on what you are trying to do, and a picture could help – AAA Jul 02 '19 at 15:52
  • It is usually a good idea to avoid [the use of Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code – cybernetic.nomad Jul 02 '19 at 16:06

0 Answers0