PC Review


Closed Thread
Thread Tools Rate Thread

VBA - Find Method :: Please Help!

 
 
New Member
Join Date: Jun 2009
Posts: 1
 
      26th Jun 2009
Hi There,
I am writing a function for a project budget in excel using VBA .
I have a range of columns, each column represents one week and has a week-ending date in row 3 of each column in the range. (as extra weeks can be added, I have named the column range). each row represents a project resource (person) which could have a period (between date x and date z) where they are charged out to the client a a discounted rate. I am wanting to calculate the amount of days in which the resource has worked within the discount period so that I can accurately calculate the resources cost. to do this, I need to place a function in each row which queries the range of columns to find the discount period start date and hold the col number in a variable and then the same with the end date and then calculate all days that the resource has worked between start and finish dates on their row (same row that the function resides in)

I am having trouble getting this to work, could someone please help.

Sorry if this is a bit confusing.

Function DiscountDays()

Dim curRow, curCol, colDateA, colDateB, discFrom, discTo


curRow = Application.ThisCell.Row
curCol = Application.ThisCell.Column
discFrom = Application.ThisCell.Offset(0, -2).Value
discTo = Application.ThisCell.Offset(0, -1).Value


colDateA = Range("activeDate").Find(What:=discFrom) .Row 'Where activeDate id the named range
colDateB = Range("activeDate").Find(What:=discTo).Row


'sum values of cells on this row between these columns inclusive.


DiscountDays = Sum.curRow.Range(Columns(colDateA), Columns(colDateB)) 'output value

End Function

Last edited by billabong; 26th Jun 2009 at 06:29 AM.. Reason: Added Screenshot
 
 
 
 
 
Captain Crunchie, Retired
muckshifter's Avatar
Join Date: Mar 2002
Location: In a Hovel
Posts: 20,965
 
      26th Jun 2009
Please stick with one thread on the same subject matter ...


http://www.pcreview.co.uk/forums/thread-3841255.php


attachment removed for better reading & loading



 
 
 
 
 
Closed Thread

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Please post this thread a correct full method, method about Nast Runsome Microsoft Excel New Users 8 25th Feb 2008 04:29 PM
Please post this thread a complete correct method, method about te Nast Runsome Microsoft Excel New Users 0 23rd Feb 2008 10:42 PM
xls vba find method to find row that contains the current date =?Utf-8?B?UkNyYW5zdG9u?= Microsoft Excel Programming 5 28th Mar 2007 03:59 PM
Help! Anyone! Please Please Please Please Please Please =?Utf-8?B?UmFuZHk=?= Microsoft Access Macros 2 17th May 2004 07:16 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 09:34 AM.