VBA - Populate Cell in Column F if Adjacent cell in Column E is not blank

**DoriBeE** · 02-11-2011, 12:45 PM

HELP! I have a Spreadsheet in which Columns F & G retrieve the Month (F) and Year (G) of the date in column E.

The formula is as follows:
=IF(ISBLANK($E2),"",VLOOKUP(MONTH($E2),Months,2,FALSE))

Problem: Spreadsheet lags due to Formulas sitting in cells of COlumn F & waiting for a value to be inputed beside them.

What I need: I want to remove the formulas i have in columns f and g and have a vba code that will populate F & G with this formula =VLOOKUP(MONTH($E2),Months,2,FALSE)) for every cell that is not blank in COlumn e.

Meaning,
If E2 is blank, then no formula in F2 or G2
if E2 is not blank, F2 & G2 calculate Month and Year (Respectively).
for every row in my range...

Gracias in advance!

PS- I have a dynamic range that auto arranges itself, so the code could be to just apply it within that range when ever a change is made to the worksheet? (i could be way off, just a suggestion).

**MarvinP** · 02-11-2011, 04:57 PM

Hi DoriBeE,

Find the attached with what I think you want. It is a sample workbook. When you put a Date in a cell in Column E an Event Macro will fire and put formulas in F and G.

If this isn't it, let me know...

**DoriBeE** · 02-11-2011, 06:09 PM

Hi, this is Exactly what I need! the only problem is i have a vlookup function on mine to return the abbreviated name of the month rather than the #. (i need it for a pivot report). I can't get the Vlookup function to work with yours... It runs but then gives me an error.

I'm almost certain i'm putting the quotations in the wrong parts of the formula...
I'm using:
Cells(Target.Row, "F").Formula = "=IF(ISBLANK($E" & Target.Row & "),"""",vlookup(MONTH($E" & Target.Row & "),Months,2,false)"
"Months" being my Month's table/range

Is this going to update Columns F & G entirely every time a change is made to column E? or does it just fill in that target row? My screen jumped like the spreadsheet was doing too much to calculate and i'm only at 155 rows...

would applications.screenupdate = false keep it from doing that?

**DoriBeE** · 02-11-2011, 06:15 PM

Do i need "If is blank" ? the code only kicks in if a change is made to column E... Can't i just throw in an

Else if Target.Value = VbNullString then
Exit sub
end if

under it and remove the if is blank for the formula quote, leaving only the vlookup?

**MarvinP** · 02-11-2011, 06:26 PM

Hi,
ScreenUpdating will make things go faster. Be sure to turn it back on at the bottom.

For the Formula Quote problem. I always make the darn formula work on the spreadsheet and copy and paste it into my VBA. Everywhere there is a single quote I double it. (Put an extra quote).

I never understood why you wanted a vlookup for the Month and Year. If you are using your dates in a pivot table why don't you simply group the dates by Year and Month instead of trying to extract it?
See http://chandoo.org/wp/2009/11/17/gro...-pivot-tables/

**DoriBeE** · 02-11-2011, 06:47 PM

That link is actually very usefull, i wasn't aware Pivots did that.

It won't let me group some of the dates though, keeps saying "cannot group that selection". It worked for the Order REquest Dates but not for the Orders created.

The pivot comapares Requests vs created by year, then month then gives a total for each. HA if I could get it to work this would be perfect. I'd save my self a few columns of space on the ws and wouldn't have to use this formula.