Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
checking that cells have a value before the workbook will close | Excel Worksheet Functions | |||
Help with using range names in sum function | Excel Worksheet Functions | |||
Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
Conditional Format - Formula to Colour Every 3rd Cell in Offset Range | Excel Discussion (Misc queries) | |||
Applying formula to only NON-EMPTY cells in range | Excel Discussion (Misc queries) |