Assign excel cells to variables

**jayinthe813** · 09-06-2012, 11:57 PM

Hello,

What i'm looking to achieve is to select a number from a column using inputbox, and then assign each cell in that row to a variable, then pass the variable into a web form (The easy part).

I type in 200 for the input box and then:

Number	Name	Address	City	State	ZIP	Phone
200	John Doe	123 Main St	Anywhere	CA	11111	5555555555

I understand i might be able to autofilter the rows, and I do not want to delete them because I will need to keep searching through the list, what I would like to do is to be able to assign each cell to a set variable, and then export out that variable into a web form and repeat. If someone could explain the concept of doing it, or show me something as a demo, I would appreciate it..

**rscsmith** · 09-07-2012, 12:35 AM

Hi

I haven't used webforms, so that is beyond me. However, assigning the values to a bunch of variables is easy.

Is there a relationship between the Number and the row number, or will we have to search? I have assumed there is no relationship and I have searched for it. It may be possible to go straight to the row by setting the row as N-5, or similiar, if there is a relationship.

I have assumed the number is in column A.

Sub GetNumber()
Dim n, lr As Single, sRow As Single
'Number Name Address City State ZIP Phone
Dim stName As String, stAdd As String, stCity As String
Dim stState As String, stZip As String, stPhone As String

n = InputBox("Enter a number", "Number")
If n = "" Then Exit Sub
n = Val(n)
If n = 0 Then Exit Sub

'Find used range (last row)
lr = ActiveSheet.Range("A50000").End(xlUp).Row

'Check down column 1 for value n.  Column 1 = Column A.
stName = ""
For sRow = 1 To lr
 If Cells(sRow, 1) = n Then
  stName = Cells(sRow, 2).Value
  stAdd = Cells(sRow, 3).Value
  stCity = Cells(sRow, 4).Value
  stState = Cells(sRow, 5).Value
  stZip = Cells(sRow, 6).Value
  stPhone = Cells(sRow, 7).Value
  Exit For
  End If
 Next sRow
  
If stName = "" Then
 MsgBox "Not Found"
 Else
 MsgBox "Found " & stName
 'Send it to the webform Subroutine(stName, stAdd,stCity,stState,stZip,stPhone)
 End If
 
End Sub

Hope some of this helps.

Cheers, Rob.

**jayinthe813** · 09-07-2012, 12:50 AM

that works perfectly! Thank you so much! There is no relationship, just a ton of data.

The only thing I changed is

lr = Range("A" & Rows.Count).End(xlUp).Row

Which does the same thing, and I doubt theres any real-world difference

Perhaps I should make a guide about taking excel items to a web form once i am finished. There is very little on it from a VBA standpoint, im having to use javascript articles and figure the VBA equivalents

**rscsmith** · 09-07-2012, 01:08 AM

Hi

Glad it worked.

I for one would be interested in such a guide! I must say, I haven't found a need for it yet, but that doesn't mean I won't with more and more data becoming available through web portals, not only on the www, but also internally on company intranets.

Cheers, Rob.