How to pass a date as an argument in an Excel VBA function -


i'm trying make function take date in cell argument, use date lookup value. date passed in variable effdate. function should go worksheet ratechgs, check newpymteffdaterange effdate, and, upon finding it, go escrowpymtamtrange (one column wide) , return value on same row there.

i've gotten point of testing in immediate window typing getescrowpymt(8/1/2000) (or other date). value of position variable, can tell function isn't finding date though it's there. problem how i'm passing date? 

function getescrowpymt(effdate date) dim pymteffdaterange range  dim escrowpymtamtrange range  dim position integer   set pymteffdaterange = worksheets("ratechgs").range("newpymteffdate") set escrowpymtamtrange = worksheets("ratechgs").range("escrowpymt")  position = application.worksheetfunction.match(effdate, pymteffdaterange, 1) msgbox (position)  end function

the last argument in match function allows returning approximate match. if require exact match, should use last argument of 0 require exact match. otherwise, using arguments 1 or -1 return approximate match , assume data sorted ascending.

position = application.worksheetfunction.match(effdate, pymteffdaterange, 0)

the match function error if effdate value not found in lookup array, may need error handling logic account possibility. use application.match function can accept error type, match function in worksheet class accept long/integer values , raise error if value isn't found:

dim position variant position = application.match(effdate, pymteffdaterange, 0) if iserror(position)     msgbox effdate & " not found!", vbinformation      exit function     '   -- or --     '  assign other return value function, etc. end if

some functions have difficulty working date values, let me know if doesn't solve issue.

vba doesn't play various system locales, if you're expecting "8/1/2000" other august 1, 2000, may have more problems since vba interpret date format, not system locale (e.g., in uk date 8 january, 2000). in case, may best treat date text , match based on text rather date.


Comments

Post a Comment

Popular posts from this blog

java - Spring Data JPA: Why findOne(id) executing delete query internally? -

i have 1 application using @manytomany annotation link 2 different entities 1 parent entity. not sure if have implemented properly. when execute .findone() on parent entity's repository find it, @ first fetch data along executing delete queries internally delete record mapping table (a table jpa creates after adding @manytomany annotation.). following generated sql queries , code snippet: java class @entity @table(name = "inventory") public class inventory { @id long id; // many other properties @manytomany(fetch=fetchtype.lazy) private list<stream> streams; @manytomany(fetch=fetchtype.lazy) private list<subject> subjects; } there no other annotation i've put in stream , subject entity class link inventory class. when run app, create following tables: inventory stream subject inventory_stream inventory_subject (inventory_stream , inventory_subject, 2 tables created automatically, don't k...
Read more

python - Mongodb How to add addtional information when aggregating? -

i beginner , wrote line pipeline works, wanna add other information output, screen name , or number of tweets.i tried add under $group gave me syntax error everytime here pipeline: def make_pipeline(): # complete aggregation pipeline pipeline = [ { '$match': { "user.statuses_count": {"$gt":99 }, "user.time_zone": "brasilia" } }, { "$group": { "_id": "$user.id", "followers": { "$max": "$user.followers_count" } } }, { "$sort": { "followers": -1 } }, { "$limit" : 1 } ]; i using on example : { "_id" : objectid("5304e2e3cc9e684aa98bef97"), "text" : "first week of school on :p", "in_reply_to_status_id" : null, "retweet_count" : null, ...
Read more

java - Incorrect order of records in M-M relationship in hibernate -

Image
i have 4 records in bridge table in mysql database. using @manytomany in hibernate, accurate order of employees incorrect incorrect of projects. following required material figure out doing mistake. this source. @entity @table(name="project") public class projectbean { @id @generatedvalue @column(name="project_id") private int projectid; @column(name="title") private string projecttitle; @manytomany(mappedby="projects") private collection<employeebean> employees; public projectbean() { employees = new arraylist<employeebean>(); } //getters & setters } @entity @table(name="employee") public class employeebean { @id @generatedvalue @column(name="employee_id") private int employee_id; @column(name="first_name") private string ...
Read more