I\'m developing a user form that was originally designed to work in a worksheet.

Question

I'm developing a user form that was originally designed to work in a worksheet.

Originally, the worksheet included two cells - each set to use Data Validation to display the contents of named ranges on a different worksheet. The first cell was set to display a list from the first column of a range. The second cell was set to display an offset of any records found in the named range based on the match of the first cell's selected value to the value in the first column in the named range. Because multiple rows could exist in the named range for the selected value, the formula used offset and countif to identify the rows that met the criteria. The formula I used was:

=OFFSET(Requestor,MATCH(B6,RequestorColumn,0)-1,7,COUNTIF(RequestorColumn,B6),1) with Requestor and RequestorColumn being the named ranges and B6 being the cell on the worksheet containing the selected value.

I'm now trying to move this functionality to a Userform with two ActiveX combo boxes but am having difficulty getting this formula to work. I have spelled out the named ranges to be Worksheets("xxxx").Range("xxxx") and I have extended the MATCH and COUNTIF to be Application.Worksheet.Function.MATCH and Application.Worksheet.Function.COUNTIF). But can't figure out how to get OFFSET to Work. I've tried things like Application.Range("a1").OFFSET and APPLICATION.CELLS(1,1).OFFSET but get various errors messages when I try to run. I'm not sure how to code OFFSET since I'm not really using Cell Ranges or specific Cells.

Thanks

Explanation / Answer

OFFSET the worksheet function is not available in the Application.WorksheetFunction collection because VBA has its own. But, to get the full functionality of the OFFSET function, you need to use VBA's Offset (which takes row and column offsets) in combination with Resize (which will give your the optional height and width of OFFSET)

=OFFSET(Range,RowOffset,ColumOffset,Height, Width)

becomes

Set RangeObject = Range.Offset(RowOffset,ColumOffset).Resize(Height, Width)

though the order is optional:

Set RangeObject = Range.Resize(Height, Width).Offset(RowOffset,ColumOffset)

Here is how to do your OFFSET in VBA. I have assumed that all the ranges and cells are on Sheet1:

Sub TestMacro()
Dim rngC As Range
'=OFFSET(Requestor,MATCH(B6,RequestorColumn,0)-1,7,COUNTIF(RequestorColumn,B6),1)
With Worksheets("Sheet1")
Set rngC = .Range("Requestor").Offset(Application.Match(.Range("B6").Value, _
.Range("RequestorColumn"), False), 7).Resize( _
Application.CountIf(.Range("RequestorColumn"), .Range("B6").Value), 1)
MsgBox rngC.Address
End With
End Sub

Without using the range object, you could just do:

Sub TestMacro2()
'=OFFSET(Requestor,MATCH(B6,RequestorColumn,0)-1,7,COUNTIF(RequestorColumn,B6),1)
With Worksheets("Sheet1")
MsgBox .Range("Requestor").Offset(Application.Match(.Range("B6").Value, _
.Range("RequestorColumn"), False), 7).Resize( _
Application.CountIf(.Range("RequestorColumn"), .Range("B6").Value), 1).Address
End With
End Sub

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.