Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range VBA: double entry table
Good evening:
I´m trying to create a vba function to look for in a double entry table. The imputs for this function a 1 the table name in Excel 2 the name to look for in the left column 3 the name to look for in the up row The main problem is I want to use the function in an worksheet and the double entry table can be in different worksheet (usually will be in another worksheet), and because of that I have some problems with the ranges. You can read the code below (it is a short function) as it currently is, the problem start in the line Set = RowUp, after this line RowUp= nothing and the the code doesn´t work Thanks in advance for your help. Function LookTable(TableName As String, DataLeftColumn As Variant, DataRowUp As Variant) As Variant Dim TableRange As Range Dim RowUp As Range Dim LeftColumn As Range Set TableRange = worksheets(Range(TableName).Parent.Name).Range(Tab leName) Set RowUp = TableRange.Range(Cells(1, 1), Cells(1, TableRange.Columns.Count)) Set LeftColumn = TableRange.Range(Cells(1, 1), Cells(TableRange.Rows.Count, 1)) LookTable = WorksheetFunction.Index(TableRange, _ WorksheetFunction.Match(DataRowUp, _ RowUp, _ 0), _ WorksheetFunction.Match(DataLeftColumn, _ LeftColumn, _ 0)) End Function |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range VBA: double entry table
This worked for me even though the table (named range) was in a different
worksheet. The named range ("myTable") was defined as: =Sheet3!$C$7:$G$11 Public Function LookTable(TableName As String, ColVal As String, _ RowVal As String) As String Dim i As Long, rw As Long, col As Long With Range(TableName) For i = 1 To .Columns.Count If .Cells(1, i).Value = RowVal Then col = i Exit For End If Next For i = 1 To .Rows.Count If .Cells(i, 1).Value = ColVal Then rw = i Exit For End If Next End With LookTable = Application.Evaluate("=Index(" & TableName & "," & _ rw & "," & col & ")") End Function Greg |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range VBA: double entry table
Fully useful
Thank you very much Greg. Best regards. "Greg Wilson" escribió en el mensaje ... This worked for me even though the table (named range) was in a different worksheet. The named range ("myTable") was defined as: =Sheet3!$C$7:$G$11 Public Function LookTable(TableName As String, ColVal As String, _ RowVal As String) As String Dim i As Long, rw As Long, col As Long With Range(TableName) For i = 1 To .Columns.Count If .Cells(1, i).Value = RowVal Then col = i Exit For End If Next For i = 1 To .Rows.Count If .Cells(i, 1).Value = ColVal Then rw = i Exit For End If Next End With LookTable = Application.Evaluate("=Index(" & TableName & "," & _ rw & "," & col & ")") End Function Greg |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Double Entry Posting | Excel Worksheet Functions | |||
Range vba: double entry table | Excel Programming | |||
Double Entry table | Excel Worksheet Functions | |||
Double entry | Excel Discussion (Misc queries) | |||
Double-clicking entry | Excel Programming |