CODEWITHSUNDEEP
vbaloopsvariablesexcel
arlederman
arlederman

Reputation: 15

Creating variables within a loop

I'm working in VBA and I wanted to know if it was possible to create and name variables within a while loop. My goal is to look through a particular column in the worksheet and create variables whose values are the text within the cells of that column.

Ideally, the variable names and their values would be something along the lines of:

party1 = "Owner" 
party2 = "Contractor" 
party3 = "Subcontractor"

And so on a so forth based on an number of parties.

So far I have:

Dim i As Integer, j As Integer
i = 2
j = 1
Do While Cells(i, 7).Value <> ""
    Dim partyName As String
    ' Pulls the text from the worksheet cell
    partyName = Cells(i, 7).Text
    Dim curNum As String
    curNum = CStr(j)
    Dim tempParty As String
    tempParty = "party" & curNum
    i = i + 1
    j = j + 1
Loop

I think this comes down to the question: How do I then get partyX to be the variable name and the variable value to be the string stored in partyName?

This seems like it's along the lines of what I'm looking for, but it doesn't quite answer my question. Also, I recognize there may be issues with the scope of these variables, but I can't think of any other way to do this.

Upvotes: 1

Views: 16218

Answers (1)

Chrismas007
Chrismas007

Reputation: 6105

IF you need to turn a NAME into a NUMBER using Select Case:

Dim CurRow As Long, LastRow As Long
LastRow = Cells(Rows.Count, 7).End(xlUp).Row

For CurRow = 1 to LastRow
    partyName = Cells(i, 7).Value
    Select Case partyName
        Case "Owner"
            partyNum = 1
        Case "Contractor"
            partyNum = 2
        Case "Subcontractor"
            partyNum = 3
        Case Else
    End Select
    'Do something with partyNum here
Next CurRow

If you need to turn a NUMBER into a NAME using Select Case:

Dim CurRow As Long, LastRow As Long
LastRow = Cells(Rows.Count, 7).End(xlUp).Row

For CurRow = 1 to LastRow
    partyNum = Cells(i, 7).Value
    Select Case partyNum
        Case 1
            partyName = "Owner"
        Case 2
            partyName = "Contractor"
        Case 3
            partyName = "Subcontractor"
        Case Else
    End Select
    'Do something with partyName here
Next CurRow

Upvotes: 2

Related Questions