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

-

I am trying to use VBA to count rows on sheet 2 ('log') where the column Matching the value entered in H. My cell at sheet 1 ('home') I am doing this using the following code: 

  dim iVal = application as integer IVal. Worksheet Function. Range ("N"), Range ("N20") Value if the EERR (application) match (range ("log") column ("h"), range ("N10") (range ("N10"). Value, Sheets ("Log"), Columns ("H"), 0)) Then MSBBX "No Match" Els MSBBbox "Hi" & Range ("N10"). Value & amp; "," & Amp; VbNewLine & VbNewLine & "Your department has requested" & IVal2 & "Suppliers this month." You have "& 5 - Eyes &" Rest of the Request for This Month. " Amp; VbNewLine & VbNewLine & "Every Department Requires 5 New Supplier Requests Per Month "VbOKOnly + vbInformation, exit the" Important Notice! "Supplement if

now I want to add in my statement and calculate the values ​​matching only column H I would like that in my cell with the value put on 1 sheet, in the same date there is the current month and year in the column M in the column M.

So for example today, say that today Month of 'October 2014' and 'apple' in column H Includes later and column 21/10/2014 'in the same line in M.

If I type 'apple' in my cell on Sheet 1 then it will calculate the word 1 apple for this month.

But if I have 'apples' in my column and '21 / 09/2014' in column M and today's month is October 2014, then this count will not be possible because the month is not the current month .

Do anyone know that I can do this? I am trying to encompass my code with another if this statement is but I'm new to VBA and I do not think it's the way to go. 

  & gt; If the month (date) = month (sheet ("log"). Column ("m")) then & gt; MsgBox "date now" & gt; Other & gt; MsgBox "no longer date" & gt; If you do not need VBA strictly to complete it, then

Considering the data grid of 99 rows on both Sheet1 and Sheet 2, using the basic Excel functions, the following formula is called Sheet1! H1: 99 Match N10 and Sheet2! M1: 99 Matching year and month N20 = SUMPRODUCT ((sheet 1 $ H $ 1: $ H $ 99 = N10) * (Text (Sheet2 $ M $ 1: $ M $ 99, "YYYYMM") = TEXT (N20, "YYYYMM")))

Appendix: If you fully qualify cell references, To evaluate, you can simply use square brackets: 

  sub-testing (with ActiveSheet). Range ("A1") = [= SUMPRODUCT ((Sheet1! $ H $ 1: $ H $ 99 = Sheet3! N10) * (Text (Sheet2! $ M $ 1: $ M $ 99, Yameem "End" with the end

Note that I've added worksheet references for N10 and N20.


Comments

Post a Comment

Popular posts from this blog

java - Can't add JTree to JPanel of a JInternalFrame -

-
Image
There are two panels in my JInternalframe. I want to add TopPanel to Jtree to nominate another. But I can not add Jaitari to the top of the panel. Please help me in this piece of code: DefaultMutableTreeNode root = new DefaultMutableTreeNode ("deck"); DefaultMutableTreeNode Item Clubs = New DefaultMutableTreeNode ("Club"); AddAllCard (itemClubs); Root.add (itemClubs); DefaultMutableTreeNode item = New DefaultMutableTreeNode ("Diamonds"); AddAllCard (itemDiamonds); Root.add (itemDiamonds); DefaultMutableTreeNode Itemspads = New DefaultMutableTreeNode ("Spades"); AddAllCard (itemSpades); Root.add (itemSpades); DefaultMutableTreeNode item increases = new DefaultMutableTreeNode ("heart"); AddAllCard (itemHearts); Root.add (itemHearts); Default Trimodel Tree Model = New DefaultTreeModel (Route); Tree = new jetty (tree modal); ScrollPane = new JScrollPane (tree); // scrollPane.setViewportView (tree); . ScrollPane.getViewport () add (tr
Read more

javascript - data.match(var) not working it seems -

-
OK, I have this small block code, which is to scanned a number on the website, but for some reason There is a hip that it is not scanning at all. var regenges = / \ & lt; Span class = "currency-robux" data-to-"item-private-value-price" & gt; ([\ D,]) \ & LT; \ / Span \ & gt; /; Var ValueCiling = Data. Match (reggae); Value-song = number (value slicing location (",", "")); Is there anything wrong with that? This is related to the if statement if (value sling and lieutenant; = price watching) { < P> A function calls to move from there, but for some reason it does not seem to run at all. So I think the Regex is wrong but I'm not sure how. (There is a variable in PriceWinking , this is a snippet from the code.) On the website, I'm trying to remove it. & lt; Span class = "robux" data-plus = "item-privatesale-price" & gt; 115 & lt; / Span & gt; Keep in mind
Read more

javascript - How can I pause a jQuery .each() loop, while waiting for user input? -

-
Before I continue to get a jQuery .each loop to wait for user input There is a problem in repeatedly repeating This is my code (I have commented on what I would like to do): & lt; Html xmlns = "http://www.w3.org/1999/xhtml" & gt; & Lt; Top & gt; & Lt; Link rel = "stylesheet" type = "text / css" href = "common_components.css" /> & Lt; Meta http-equiv = "content-type" content = "text / html; charset = UTF-8" /> & Lt; Link rel = "stylesheet" href = "slider.css" & gt; & Lt ;? Php include_once 'common_components.php'; $ Query = $ dbh- & gt; Queries ("SELECT movies_dir FROM settings"); $ Line = $ query- & gt; Obtain (PDO :: FETCH_ASSOC); Date_default_timezone_set ('UTC'); Echo "& lt; title & gt; movie scanner & lt; / title & gt;"; ? & Gt; & Lt; Script src = "jquery.min.js" & gt; &am
Read more