excel - Using a wildcard with a cell reference. What am I missing? -


in sheet list of names. attempting count different names on sheet in table on sheet b, column b. names not exact , referring cell , not writing in name. sub below works think not using wildcqard correctly. please if can. in advance.

sub countif_crr_cnt_until_lastrow()      dim lastrow long     dim wb1 workbook       set wb1 = workbooks("macro client v.01.xlsm")      lastrow = wb1.sheets("a").range("a:a").find("overall - total", searchorder:=xlbyrows, searchdirection:=xlprevious).row      = 21 lastrow  cells(i, 10) = application.countifs(wb1.sheets("b").range("b:b"), "*" & cells(i, 3) & "*")  next  end sub

i believe issue unqualified references when use cells(). vba needs know sheet expect on, when using multiple sheets. i'm assuming cells(i,10) , cells(i,3) on sheet "a". if not, change sheet name:

wb1.sheets("a").cells(i, 10) = application.countifs(wb1.sheets("b").range("b:b"), "*" & wb1.sheets("a").cells(i, 3) & "*")

as can see, that's kind of long , little tricky read. alternative way of doing that, use with:

with wb1.sheets("a")    .cells(i,10) = application.countifs(wb1.sheets("b").range("b:b"), "*" & .cells(i,3) & "*") end

so, wherever see simple .cells() without before ., it's going use follows with. it's equivalent of formula above...does make sense?


-

Popular posts from this blog

Apache NiFi ExecuteScript: Groovy script to replace Json values via a mapping file -

i working apache nifi 0.5.1 on groovy script replace incoming json values ones contained in mapping file. mapping file looks (it simple .txt): header1;header2;header3 a;some text;a2 i have started following: import groovy.json.jsonbuilder import groovy.json.jsonslurper import java.nio.charset.standardcharsets def flowfile = session.get(); if (flowfile == null) { return; } flowfile = session.write(flowfile, { inputstream, outputstream -> def content = """ { "field1": "a" "field2": "a", "field3": "a" }""" def slurped = new jsonslurper().parsetext(content) def builder = new jsonbuilder(slurped) builder.content.field1 = "a" builder.content.field2 = "some text" builder.content.field3 = "a2" outputstream.write(builder.toprettystring().ge...
Read more

node.js - How do I prevent MongoDB replica set from querying the primary? -

read-operations-only: is there way have mongodb replica set, make mongodb instance on box connected to, mongodb gets queried? i have 3 ec2 instances behind aws load balancer. on each ec2 instance runs mongodb, part of replica set. i have express endpoints on nodejs , connect replica set follows mongodb.mongoclient.connect('mongodb://1.1.1.1,2.2.2.2,3.3.3.3.', function (err, db) { if (err) { complete('{}'); } else { i distribute load of queries evenly across 3 instances of mongodb replica set instead of default having queries routed ec2 instance primary mongodb defined, because destroys point of load balancer (which balance load of queries across 3 instances evenly). my understanding when connecting mongodb replica set, primary instance selected unless primary instance down, , therefore purpose of every secondary instance serve backup. in example people call hot,cold,cold. because there 2...
Read more