Hey people.
I still can not find anything that directly explains yet how I can get a value in column D in an Excel File that I have linked, to a VB string, based on a selection of a value in column A.
I am dealing with metal gauges and cost for metal
I have a listbox pulling data through databinder from column A in my excel sheet.
Now I need to assign the value cost of metal for that gauge to a variable.
As easy as this sounds to me explaining it, I am missing something here.
In Excel, I would simply use a vlookup command.
In SQL I would use a select cost from materialcosttable where gauge = [gavariable]
But I need to write this natively in VB. I am using VS2005, and am immensly impressed by the ease of databinding, but now I need to move on.
pseudocode:
dim valueav as int
dim gavaluev as int
dim costv as int
valuav = valuea.text
gavaluev = gavalue.text
costv = vlookup (gavalue.text,exceltablematerialcost,4,false)
resultcalc = valuav * costv
I am a programmer from the old days before object oriented, so be gentle.
-Wraith
Excel Data Read
Hot Topic
Microsoft ISV
- Please Help with Access 2003 and ActiveX Controls
- Type Mismatch
- Why Excel 2003 keeps PivotItems with zero record count after refresh?
- Progress Bar
- Excell VBA: displaying values from the sheet in user form
- Business Scorecard Manager 2005 - Deploying to Reporting Services
- Trouble signing up for the Microsoft Empower for ISVs
- How do you find out the Windows User Name from VBA
- Important MS Partner Question
- Fixed!!!
Excel Data Read
rlcowan
Hi, Wraith!
Maybe the Excel's 'Cells' property can help. You could populate your variable like this:
MyVar = WorkSheets.("MySheet").Cells(RowId, ColId) 'ColId = 4 for the column D
Hope this helps.
keerti_maverick
Our support engineer provided the following code and some additional screenshots that I can't post here. If you'd like this additional information, please email me at budsup@microsoft.com
Base on my understanding, our buddy wants to select some data from a validation list and lookup the selected item in a table, then fill the remaining fields with the search result. < xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
1. Suppose we have a sheet named ‘Products’ with following layouts. It’s the source data.
[table image removed]
2. Meanwhile, we have another sheet named ‘Result’ with following layouts. ‘Product’ field will be a validation field that we restrict the data in this field against what on the ‘Products’ sheet. Once we choose the proper ‘Product’, it will automatically lookup the ‘Price’ field in source table and fill in ‘Result’ sheet accordingly.
[table image removed]
3. Please add following code and invoke in the workbook_Open event
=========================
Sub CreateValidation()
‘Define named range in excel
ActiveWorkbook.Names.Add Name:="CPU", RefersToR1C1:="=Products!R2C1:R6C1"
ActiveWorkbook.Names.Add Name:="RAM", RefersToR1C1:="=Products!R7C1:R9C1"
ActiveWorkbook.Names.Add Name:="Mainboard", RefersToR1C1:="=Products!R10C1:R15C1"
‘ Select Result sheet
Sheets("Result").Select
‘ Add validation to CPU (Equal to Data Menu -> Validation…->Allow=”List”/Source=”=CPU”)
With Range("B3").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=CPU"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.IMEMode = xlIMEModeNoControl
.ShowInput = True
.ShowError = True
End With
‘ Add validation to MAINBOARD (Equal to Data Menu -> Validation…->Allow=”List”/Source=”= Mainboard”)
With Range("B4").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=Mainboard"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.IMEMode = xlIMEModeNoControl
.ShowInput = True
.ShowError = True
End With
‘ Add validation to RAM (Equal to Data Menu -> Validation…->Allow=”List”/Source=”= RAM”)
With Range("B5").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=RAM"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.IMEMode = xlIMEModeNoControl
.ShowInput = True
.ShowError = True
End With
‘Add VLOOKUP functions
For i = 3 To 5
strCmd = "VLOOKUP(" & "R" & CStr(i) & "C2,Products!R1C1:R15C2,2)"
strCmd1 = "VLOOKUP(" & "R" & CStr(i) & "C2,Products!R1C1:R15C2,2, False)"
strCmd = "IF(ISERROR(" & strCmd1 & "),""""" & "," & strCmd & ")" 'Handle the exception and replace the value in that cell with custom message
Sheets("Result").Range("C" & i).FormulaR1C1 = "=" & strCmd
Next
End Sub
=========================
[screenshot removed]
-brenda (ISV Buddy Team)