A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Applying Offset to Range in VBA



 
 
Thread Tools Display Modes
  #1  
Old June 20th 06, 04:48 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default Applying Offset to Range in VBA

I'm trying to convert a rather complicated formula into a custom
function.

Effectively, what the function does is it takes two values and uses the
worksheet function Match to locate the position of the values from a
sequentially increasing set in a range that are just smaller than the
two values being tested and then makes a comparison of those two
location values.

E.g. A = Match(value1, range1,1), B= Match(value2,range1,1); C = B - A

There are different procedures dependent on whether C = 0, 1, or is
greater than 1.
In each case, the procedure requires (in the workbook formula, not the
custom function - yet?)
the use of an offset function applied to another range of values
(range2) of similar length to range1. I do this by using the reference
cell that is the first in the range of range2 and the offset a certain
distance based on the values of A,B, and C and then perform some simple
math functions.

How do I reference the first cell location in range2 and use the offset
formula within a custom function? I'm using the
Application.WorksheetFunction.Offset, but it doesn't seem to work.

Thoughts?

Ads
  #2  
Old June 20th 06, 05:26 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default Applying Offset to Range in VBA

Have you tried using the VBA Find Method to locate the value in Range 2?

From the help file (you'll obviously want to modify it to suit):

With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.Pattern = xlPatternGray50
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

" wrote:

> I'm trying to convert a rather complicated formula into a custom
> function.
>
> Effectively, what the function does is it takes two values and uses the
> worksheet function Match to locate the position of the values from a
> sequentially increasing set in a range that are just smaller than the
> two values being tested and then makes a comparison of those two
> location values.
>
> E.g. A = Match(value1, range1,1), B= Match(value2,range1,1); C = B - A
>
> There are different procedures dependent on whether C = 0, 1, or is
> greater than 1.
> In each case, the procedure requires (in the workbook formula, not the
> custom function - yet?)
> the use of an offset function applied to another range of values
> (range2) of similar length to range1. I do this by using the reference
> cell that is the first in the range of range2 and the offset a certain
> distance based on the values of A,B, and C and then perform some simple
> math functions.
>
> How do I reference the first cell location in range2 and use the offset
> formula within a custom function? I'm using the
> Application.WorksheetFunction.Offset, but it doesn't seem to work.
>
> Thoughts?
>
>

  #3  
Old June 21st 06, 04:01 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default Applying Offset to Range in VBA

VBA has its own offset method.

Range("A1").Offset(3, 0)
refers to cell A4 ( Offset(0,0) is cell A1)

Range("A1") (4, 1)
also refers to cell A4 (cell A1 is 1,1 so the row and column offset will be
1 more than if you use the offset method shown above)

With Worksheets("Sheet1")
.Range("A1", .Range("A1").Offset(3,0))
End With

refers to Sheet1!A1:A4




" wrote:

> I'm trying to convert a rather complicated formula into a custom
> function.
>
> Effectively, what the function does is it takes two values and uses the
> worksheet function Match to locate the position of the values from a
> sequentially increasing set in a range that are just smaller than the
> two values being tested and then makes a comparison of those two
> location values.
>
> E.g. A = Match(value1, range1,1), B= Match(value2,range1,1); C = B - A
>
> There are different procedures dependent on whether C = 0, 1, or is
> greater than 1.
> In each case, the procedure requires (in the workbook formula, not the
> custom function - yet?)
> the use of an offset function applied to another range of values
> (range2) of similar length to range1. I do this by using the reference
> cell that is the first in the range of range2 and the offset a certain
> distance based on the values of A,B, and C and then perform some simple
> math functions.
>
> How do I reference the first cell location in range2 and use the offset
> formula within a custom function? I'm using the
> Application.WorksheetFunction.Offset, but it doesn't seem to work.
>
> Thoughts?
>
>

 




Thread Tools
Display Modes

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

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
checking that cells have a value before the workbook will close kcdonaldson Excel Worksheet Functions 8 December 5th 05 04:57 PM
Help with using range names in sum function soteman2005 Excel Worksheet Functions 2 November 28th 05 04:43 PM
Help PLEASE! Not sure what answer is: Match? Index? Other? baz Excel Worksheet Functions 7 September 3rd 05 03:47 PM
Conditional Format - Formula to Colour Every 3rd Cell in Offset Range Sam via OfficeKB.com Excel Discussion (Misc queries) 7 August 13th 05 04:19 AM
Applying formula to only NON-EMPTY cells in range Tasi Excel Discussion (Misc queries) 5 March 29th 05 10:48 PM


All times are GMT +1. The time now is 06:34 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.