• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 103
  • Last Modified:

Getting a value from an array?

I have a value stored in a name called CH_FinGrp, in this case it is: X1, however X1 will refer to a value in a table on a sheet named: FinGrpFinUsedTable
How do I look down column A of the FinGrpFinUsedTable sheet to find the CH_FinGrp value, then grab the value in the cell to the right (offset one column right)
Then give that a name to use, like ActualFinishToUse = the result found?

So in my example, if X1 is the value of CH_FinGrp, it should return TR_E and assign that the name ActualFinishToUse
FinGrpFinishUsedTable.xlsx
0
RWayneH
Asked:
RWayneH
  • 4
2 Solutions
 
Rgonzo1971Commented:
Hi,

pls try

CH_FinGrp = "X1"
ActualFinishToUse = WorksheetFunction.VLookup(CH_FinGrp, Range("A1:C18"), 2, 0)

Regards
0
 
RWayneHAuthor Commented:
Can this be made more dynamic?  Perhaps if I name the range ("A1::C18") to "FinGrpTable"  How would this change the way this looks?
0
 
RWayneHAuthor Commented:
ActualFinishToUse = WorksheetFunction.VLookup(CH_FinGrp, Range("A1:C18"), 2, 0)

is failing.  "unable to get vlookup property of the WorkSheetFunction class
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
RWayneHAuthor Commented:
Ok got this to work... now.  How do I remove absolute locations and use a name?
0
 
Glenn RayExcel VBA DeveloperCommented:
If you're expecting the size of the table - currently in A1:C18 - to change, then I recommend you define it as a Table in Excel (Menu: Insert, Table...accept defaults).  The range will be named "Table1" by default; you can change that name in the Name Manager (Menu: Formulas, Name Manager).  Just highlight Table1, click Edit, then change the name (ex., FinGrpTable).

Then, in the VBA code you can replace any occurence of
A1:C18
with the new table name (ex., "FinGrpTable").

An Excel Table is very dynamic and the range will expand as needed when/if new rows are added.

Regards,
-Glenn
0
 
RWayneHAuthor Commented:
Thanks for the help.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now