VBA: Excel Comparison Replace Problem

  • So this is what my problem is .. as you can see from A2 and A2 it has the same name but when i get the data from the database SQL is limited to some sort of foreign key which is too wierd to explain sorry i'm pretty bad at explain things haha because i'm obviously not exactly the brightest. but the problem with this is i need to replace D3, E3 and G3 with 0s
    and it is always 0s only. The search criteria I guess would depend if they have the same name but have a different type the second type would always be 0s on the three cells D3 E3 and G3. Also for DEF is it the same case like for C5 C6 D5 D6 adn G5 G6 all should be 0s. If you have any insite in how i could write a macro to automate this function i really would love the help given . Thanks for the quick response already !!

  • You could try this code:

    Dim LastRow As Long
    LastRow = Range("A65536").End(xlUp).Row
    Range("H2:I" & LastRow).Formula = "=IF($A2=$A1,0,D2)"
    Range("K2:K" & LastRow).Formula = "=IF($A2=$A1,0,G2)"
    Range("J2:J" & LastRow).Formula = "=F2"
    Range("H2:K" & LastRow).Copy
    Range("D2").PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    Range("H2:K" & LastRow).ClearContents


    Barrie Davidson
    My Excel Web Page
    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  • Thanks the code works but i only have one problem somehow it is putting 0 on one column over the other ... it is suppose to be C , D , and G column with 0s but i think i hope i can figure it out ... sorry for being so stupid ... i'm still a beginner at this i hope i could just be as good as u .. but thx for all the help already i'm a step closer !!

  • Thanks a lot Thanks a lot i figured it out .. Thanks ... I don't know how I could repay you !!
    But thank you very much !! Let me know if u ever need n e thing ....

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!