Converting a Formula to VBA

0
votes

I have two Sheets , Sheet1 and Sheet2. The column J in sheet1 contains, the Name of Project. I wanted the closing date of poject from sheet2. for this,

  1. I am looking into sheet2 with Project Name of sheet1 and if they are same, then I copy the date to sheet1 in column w.
  2. once, I have copied, then i compare the date in column u with the column w and say if it is ok or not ok.

I have formulated the above conditions in the form of formula.

But I wanted to have them in VBA. Could anyone suggest how i can Frame my formula in VBA.

Column W =IFERROR(VLOOKUP(J2;Sheet2!$A:$L;7;0);"")

Column X=IF($W2>$U2;0;1)

Column Y=IF($W2<=$U2;0;1)

I would not prefer to use recorded macros.

2
vbaexcel
Just use the built-in VBA methods or functions. Your column X and Y formulas are pretty straight forward relying on operators and Boolean comparisons; your lookup can be accomplished in a variety of ways. The Range.Find method is one. If you have lots of data, it might be more efficient to do the work within VBA arrays. Post your code and sample data if you are having problems, with the specific problem. – Ron Rosenfeld
@RonRosenfeld Thank you for the clue, i got my Output :) – Mikz
@RonRosenfeld one concern, since i am just looking for matching value, many of the rows are empty. Can you suggest me how i could hide them with code ? – Mikz

2 Answers

0
votes

The easiest way is to record macro, when you activate and accept formula. The in VBA code I can find the right solutions. For example:

IF($W2>$U2;0;1) for cell H3

Range("H3").Select
ActiveCell.FormulaR1C1 = "=IF(RC23>RC21,0,1)"
0
votes
Range("H3").Formula = "=IF(RC23>RC21,0,1)"