excel vba - Today function equivalent in VBA in combination with countifs -

-

i having problem using countifs formula in excel / vba. have got formula working perfect in excel ideally want use in vba form. here formula in excel works treat:

=countifs(sheet1!a:a,"place",sheet1!k:k,"<"&today())

will count names places in past

=countifs(sheet1!a:a,"place",sheet1!k:k,">"&today())

will count names places current

i have 5 different places in column , hundreds of different dates in column k. above formulas work in excel , return correct values. have spent hours trying work in vba userform keep getting various errors. first part not problem &today function falls apart. can see &today function not available in vba , &date seems recommendation. have tried still no where. i'm missing trick (or several) here , working in vba rather using current formulas in excel. returned results displayed in textboxes on form.

all ideas , feedback welcome!

second edit

================================

thanks quick replies! here actual code playing in vba

 'count events area' dim listlondon long  listlondon = .countif(range("a1:a1998"), "london"), ("sheet1!k1:k1998"), "<" & date)  end  me.textbox1 = listlondon

i know second part of count if wrong regards date - that's how i've left now. hoping use current layout , working date / today code @ end. please show me i've done wrong here!

====

oops - can see mistake - initial problem remains around date issue. should of used countifs using multiple criteria.

  1. you have read values of cells vba code. recommend use excel.range object that. can interpret range edit line of excel, like

    dim foo excel.range

    set foo = yourworksheet.range("a1:b3")

  2. read date type data vba date type variable iterating through cells.

  3. examine relation between read data , current date. current date can read using datetime.now function.

  4. increment variable based on decision


Comments

Post a Comment

Popular posts from this blog

how to insert data php javascript mysql with multiple array session 2 -

-
i have method method discussed , successful data entered 1 when more 1 can not database this programs my php form : <form class="form-horizontal" id="form-produk"> <div class="control-group" > <label class="control-label" for="kelas">kategori</label> <div class="controls"> <select class='form-control' name='id_kategori'> <?php $tampil=mysql_query("select * kategori order nama_kategori"); if ($id_kat==0){ echo "<option value=0 selected>- pilih kategori -</option>"; } while($w=mysql_fetch_array($tampil)){ if ($id_kat==$w[id_kategori]){ echo "<option value=$w[id_kategori] selected>$w[nama_kategori]</option>"; } else{ echo "<option value=$w[id_kategori]>$...
Read more

multithreading - Exception in Application constructor -

-
i wanna write snake game javafx , there exception don't know , want know how fix it. ( know it's not complete yet ) and want know , class extends application ( start override ) main in other programs? see , here not static void main bc didn't need, if want add main shoud do? it's exeption... exception in application constructor exception in thread "main" java.lang.nosuchmethodexception: main_snake.main([ljava.lang.string;) @ java.lang.class.getmethod(class.java:1819) @ com.intellij.rt.execution.application.appmain.main(appmain.java:125) and code : import javafx.animation.animationtimer; import javafx.application.application; import javafx.event.eventhandler; import javafx.scene.scene; import javafx.scene.canvas.canvas; import javafx.scene.canvas.graphicscontext; import javafx.scene.input.keyevent; import javafx.scene.layout.borderpane; import javafx.scene.paint.color; import javafx.stage.stage; import java.util.arraylist; /** * creat...
Read more

windows - CertCreateCertificateContext returns CRYPT_E_ASN1_BADTAG / 8009310b -

-
i realize similar post others (e.g. this one ), there details missing posts might significant case. to start with, here's simplified program: #include "stdafx.h" #include <windows.h> #include <wincrypt.h> int _tmain(int argc, _tchar* argv[]) { // usage: certextract certpath char keyfile[] = "c:\\certificates\\public.crt"; byte lp[65536]; security_attributes sa; handle hkeyfile; dword bytes; pccert_context certcontext; sa.nlength = sizeof(sa); sa.lpsecuritydescriptor = null; sa.binherithandle = false; hkeyfile = createfile(keyfile, generic_read, file_share_read, &sa, open_existing, file_attribute_normal, null); if (hkeyfile) { if (readfile(hkeyfile, lp, getfilesize(hkeyfile, null), &bytes, null) && bytes > 0) { certcontext = certcreatecertificatecontext(x509_asn_encoding, lp, bytes); if (certcontext) { printf("yay...
Read more