0

I am currently using Microsoft Visual Basic 2010 *(VBA)* and Microsoft Excel 2010, to create an interface for a user to select their location then a device they wish to have information for, in return for output of device information (usage, etc.) My question I have been looking for has yet an answer and leaves me looking for the VB code needed to continue. I have an input box, that asks the user to type in their device (which is the a string) and the idea is this: 

If chkCapacity.Checked = True Then
            lblOutput.Text = "This is the Capacity for " & cmbLocation.SelectedItem & " " & strdevice
            lstResults.Items.Add(cmbLocation.SelectedItem)
            lstResults.Items.Add("Device_Capacity")
End If

I want to be able to call the matching information that I have in Excel, for the Location, Device, and then the end result they want. There are already Excel columns for location, device and capacity. I hope I made this clear. (Match the device selected and location selected to the excel values, return the device information needed) Obviously I can't just do:

Dim Sheet_Device_Capacity As String

    Sheet_Device_Capacity = exSheet.Range("s2:s75").Value.ToString

since I'm trying to match the column of device and location not just the column of capacity.

My spreadsheet looks like this:

PoP Device 1 Capacity

  • Rochester - Not in Location - 42%

  • Syracuse - In Location - 33%

  • Binghamton - In Location - 45%

  • Albany - Not In Location - 50%

    (Hopefully you understand the formatiing as I couldn't add an image)

Community
  • 1
  • 1
amanda
  • 3
  • 2

1 Answers1

0

A fairly simple loop that goes through your information looking for a match should suffice. For example,

Dim mylocation
Dim mydevice
Dim myinfo
Dim index As Long
Dim lastrow As Long

lastrow = Cells(Rows.Count, "A").End(xlUp).Row

'assuming location in column A,
'device in column B,
'info in column C

With ActiveSheet
  For index = 2 To lastrow
    If .Cells(index, 1).Value = mylocation And _
    .Cells(index, 2).Value = mydevice Then
    myinfo = .Cells(a, 3)
  Next index
End With

This looks for a match, and returns the value of column C.

Aaron Thomas
  • 5,054
  • 8
  • 43
  • 89
  • So this will return the value of column c, related to the other two columns that are what my string value is? If so, this is such an easy approach :) – amanda Oct 04 '13 at 12:17
  • Yes, if a row is found where col A = mylocation and col B = mydevice, it sets myinfo to whatever is in col C. There are several ways to search for information in a worksheet, this was just the fastest I could type in and maybe the easiest to understand! – Aaron Thomas Oct 04 '13 at 12:19
  • The only issue I have, is that capacity is in different columns in excel based on the device. (one device in column d, capacity in g, another device in l, etc.) could I still use this search and add multiple columns to the myinfo result? – amanda Oct 04 '13 at 13:25
  • I think if you have multiple columns, you will either need to: (1) do the loop with new values, once the first loop is done, or (2) change the method to use Range.Find or something similar. See [here](http://stackoverflow.com/questions/14931700/how-to-find-a-value-in-an-excel-column-by-vba-code-cells-find?lq=1) for more ideas. – Aaron Thomas Oct 04 '13 at 14:55