excel - vba check if value exists in column h and if month of date in column m is this month? -

excel - vba check if value exists in column h and if month of date in column m is this month? -

i trying utilize vba count rows on sheet 2 ('logs') column h matches value entered cell on sheet 1 ('home'). doing using next code:

dim ival integer ival = application.worksheetfunction.countif(sheets("logs").columns("h"), range("n10").value) ival2 = application.worksheetfunction.countif(sheets("logs").columns("j"), range("n20").value) if iserror(application.match(range("n10").value, sheets("logs").columns("h"), 0)) msgbox "no match" else msgbox "hi " & range("n10").value & "," & vbnewline & vbnewline & "your section has requested " & ival2 & " suppliers month. have " & 5 - ival & " requests remaining month." & vbnewline & vbnewline & "each section allowed 5 new supplier requests per month.", vbokonly + vbinformation, "important notice!" exit sub end if

now want add together if statement , count matching values against column h value entered in cell on sheet 1 if date in same row in column m on sheet 2 contains current month , year.

so instance lets todays month 'october 2014' , column h contains word 'apples' , in same row in column m '21/10/2014'.

if type 'apples' in cell on sheet 1 count 1 occurrence of word apples month.

but if in column h have 'apples' , in column m '21/09/2014' , todays month oct 2014 not counted month not current month.

does know way this? trying surround code if statement i'm new vba , don't think way go.

> if month(date) = month(sheets("logs").columns("m")) > msgbox "date now" > else > msgbox "date not now" > end if

you don't strictly require vba accomplish this. assuming info grid of 99 rows on both sheet1 , sheet2, next formula using native excel functions homecoming count sheet1!h1:99 matching n10 , sheet2!m1:99 matching year , month of n20. =sumproduct((sheet1!$h$1:$h$99=n10)*(text(sheet2!$m$1:$m$99,"yyyymm")=text(n20,"yyyymm")))

addendum: if qualify cell references, can utilize square brackets evaluate formula this:

sub test() activesheet .range("a1") = [=sumproduct((sheet1!$h$1:$h$99=sheet3!n10)*(text(sheet2!$m$1:$m$99,"yyyymm")=text(sheet3!n20,"yyyymm")))] end end sub

note i've added worksheet references n10 , n20.

excel vba