excel - How to specify the sheet a worksheet function should use via a range object? -

-

i have code searches column of values in sheet(3) in format of string "value1 - value 2"

value2 first value in column in sheet(2) , value1 value in same column, in cell further down sheet.

the setup have is:

in sheet(1), cells c2:c6 have values a-e respectively

in sheet(2), cell c1 has value "yes" , cells c2:c6 have values 1-5 respectively

in sheet(3), cell a2 has value "4 - yes"

so code should countif column in sheet2 first value being yes , cells value 4, , put result in cell b2 on sheet(3)

what find yes column (column c) , search same column on sheet(1) (so message boxes show letters rather numbers).

is there way can more precisely specify sheet countif function uses?

i'm using excel 2000 on windows 7

private sub test_click()  scenario_count = 6 dim integer = 1  sheets(2).select j = 2 24     if sheets(2).cells(1, j).value = right(sheets(3).cells(i + 1, 1).value, len(sheets(3).cells(i + 1, 1).value) - instrrev(sheets(3).cells(i + 1, 1).value, "-") - 1)         msgbox ("number of scenarios " & scenario_count)         msgbox ("value searching " & "'" & left(sheets(3).cells(i + 1, 1).value, instrrev(sheets(3).cells(i + 1, 1).value, "-") - 2) & "'")         msgbox ("range searched " & range(cells(2, j), cells(scenario_count, j)).address & " in " & activesheet.name)         msgbox ("number of occurrences " & sheets(2).application.worksheetfunction.countif(range(cells(2, j), cells(scenario_count, j)), left(sheets(3).cells(i + 1, 1).value, instrrev(sheets(3).cells(i + 1, 1).value, "-") - 2)))          sheets(2).select         sheets(3).cells(i + 1, 2).value = sheets(2).application.worksheetfunction.countif(range(cells(2, j), cells(scenario_count, j)), left(sheets(3).cells(i + 1, 1).value, instrrev(sheets(3).cells(i + 1, 1).value, "-") - 2))          each c in range(cells(2, j), cells(scenario_count, j))             msgbox ("comparing " & c.address & " " & c.value & " " & left(sheets(3).cells(i + 1, 1).value, instrrev(sheets(3).cells(i + 1, 1).value, "-") - 2))         next c      goto endofif2      end if next endofif2: end sub

where have 'worksheetfunction.countif(range(cells(2, j)', insert sheet before range reference, so:

sheets(2).range(sheets(2).cells(2, j), sheets(2).cells(scenario_count, j))

edit full formula references sheet both cells , range functions blatently taken @rory's comment.


Comments

Post a Comment

Popular posts from this blog

Fail to load namespace Spring Security http://www.springframework.org/security/tags -

-
i have problem namespace of spring security on xhtml page. don't understand why can't load uri, , have following issue: whitelabel error page application has no explicit mapping /error, seeing fallback. tue jul 21 16:58:12 utc 2015 there unexpected error (type=not found, status=404). please need help! possible uri has been changed? best regards you using incorrect namespace url. correct url http://www.springframework.org/schema/security the error page seeing spring source website since not recognize url. try visiting http://www.springsource.org/security/tags in browser update : spring security tags used in jsp page correct tag lib declaration is: <%@ taglib prefix="sec" uri="http://www.springframework.org/security/tags" %>
Read more

sql - MySQL query optimization using coalesce -

-
can explain me why second query faster first? (first 1 takes around 40 seconds, second 1 less 0.1) select releases.feature_code, releases.platform_id, version, release_date, general_available, uri, filesize, display_name, feature_name.full_name, if ((select exists(select 1 release_exceptions releases.feature_code = release_exceptions.feature_code , releases.platform_id = release_exceptions.platform_id , releases.version = release_exceptions.version) = 1), 1, 0) release_exceptions releases left outer join platform using(platform_id) left outer join feature_name using(feature_code) order version desc; ~~~ query 2 ------------------ ~~~ select feature_code, version, release_date, general_available, platform_id, uri, filesize, display_name, feature_name.full_name, coalesce(release_exceptions, 0) release_exceptions mus.releases left outer join ( select distinct feature_code, version, platform_id, 1 release_exceptions mus.release_exceptions ) release_exceptions using ...
Read more

Maven Javadoc 'Cannot find default setter' and fails -

-
i'm trying generate javadocs maven project, , i'm running error every time. unable parse configuration of mojo org.apache.maven.plugins:maven-javadoc-plugin:2.10.3:javadoc parameter #: cannot find default setter in class org.apache.maven.plugin.javadoc.options.group . command i'm using mvn javadoc:javadoc root directory, pom is. i don't have groups configured @ all, or have special configuration @ all. same error whether omit plugin in pom completely, add reporting, or add build plugin. i've tried adding empty groups well, , while message changes somewhat, still appears. i ran across this question , a. don't have testng dependencies, b. i'm not using command line parameters, , c. never resolved. it selenium project, suppose there similar, can't figure out. ideas? i've included maven debug stack trace below. [error] failed execute goal org.apache.maven.plugins:maven-javadoc-plugin:2.10.3:javadoc (default-cli) on project selenium: un...
Read more