# Case Structure too BIG

#### cart0250

##### Active Member
The below snippet assigns the value in B1 of sheet1 to the personName variable. It then loops through each sheet in the workbook and, if the value in column B is equal to personName, inserts corresponding values from the sheet "SenDates" into the adjacent cells in columns C and D. The case is based on the value in cell A12 of each sheet. Case 116 inserts the corresponding values from the third and fourth columns of the SenDates sheet, Case 117 inserts values from fifth and sixth columns, Case 120 from the seventh and 8th etc. Each Case picks data from the next 2 columns in the SenDates sheet.

It works as intended, however, I am ending up with over 100 cases, making for some disgusting code.

What would be a better way to do this?

Code:
``````Sub senDate1()
Dim i As Integer, j As Integer
Dim ws As Worksheet
Dim personName As String
personName = Sheets("Sheet1").Range("B1").Value

For Each ws In Worksheets

If ws.Name <> "Sheet1" And ws.Name <> "SenDates" Then

Select Case ws.Range("A12").Value

Case "116"
For i = 150 To 2 Step -1
If ws.Cells(i, 2).Value = personName Then
For j = 625 To 2 Step -1
If Sheets("SenDates").Cells(j, 2).Value = personName Then
ws.Cells(i, 4).Value = Sheets("SenDates").Cells(j, 3).Value
ws.Cells(i, 5).Value = Sheets("SenDates").Cells(j, 4).Value
End If
Next j
End If
Next i

Case "117"
For i = 150 To 2 Step -1
If ws.Cells(i, 2).Value = personName Then
For j = 625 To 2 Step -1
If Sheets("SenDates").Cells(j, 2).Value = personName Then
ws.Cells(i, 4).Value = Sheets("SenDates").Cells(j, 5).Value
ws.Cells(i, 5).Value = Sheets("SenDates").Cells(j, 6).Value
End If
Next j
End If
Next i

Case "120"
For i = 150 To 2 Step -1
If ws.Cells(i, 2).Value = personName Then
For j = 625 To 2 Step -1
If Sheets("SenDates").Cells(j, 2).Value = personName Then
ws.Cells(i, 4).Value = Sheets("SenDates").Cells(j, 7).Value
ws.Cells(i, 5).Value = Sheets("SenDates").Cells(j, 8).Value
End If
Next j
End If
Next i
...``````

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

#### ravishankar

##### Well-known Member
Hi
List 100+ cases in col S1 to S100+ and the corresponding column nos in col T (if it is 5 & 6 , put 5).
Code:
``````Sub senDate1()
Dim i As Integer, j As Integer
Dim ws As Worksheet
Dim personName As String
personName = Sheets("Sheet1").Range("B1").Value
For Each ws In Worksheets
If ws.Name <> "Sheet1" And ws.Name <> "SenDates" Then
x = Application.WorksheetFunction.VLookup(Cells(12, 1), Range("S1:T100"), 2, False)
For i = 150 To 2 Step -1
If ws.Cells(i, 2).Value = personName Then
For j = 625 To 2 Step -1
If Sheets("SenDates").Cells(j, 2).Value = personName Then
ws.Cells(i, 4).Value = Sheets("SenDates").Cells(j, x).Value
ws.Cells(i, 5).Value = Sheets("SenDates").Cells(j, x + 1).Value
End If
Next j
End If
Next i
Endsub``````
Run the macro. vlookup will pick up the col nos corresponding to A12 value. Instead of S & T , you can use any available column.
Ravi

#### cart0250

##### Active Member
thanks a lot! I'll give it a try.

#### cart0250

##### Active Member
I get an error message when running this code:
"unable to get the vLookUp property of the worksheet function class"

I am not sure which sheet I have to list the Cases and the corresponding columns in? I would like to do this in the "SenDate" sheet.

Does anyone know how to modify this code, specifically the line:
Code:
``x = Application.WorksheetFunction.VLookup(Cells(12, 1), Range("S1:T100"), 2, False)``

so I can enter the cases and corresponding columns in "SenDate"?

Code:
``````Sub senDate1()
Dim i As Integer, j As Integer
Dim ws As Worksheet
Dim personName As String
personName = Sheets("Sheet1").Range("B1").Value
For Each ws In Worksheets
If ws.Name <> "Sheet1" And ws.Name <> "SenDates" Then
x = Application.WorksheetFunction.VLookup(Cells(12, 1), Range("S1:T100"), 2, False)
For i = 150 To 2 Step -1
If ws.Cells(i, 2).Value = personName Then
For j = 625 To 2 Step -1
If Sheets("SenDates").Cells(j, 2).Value = personName Then
ws.Cells(i, 4).Value = Sheets("SenDates").Cells(j, x).Value
ws.Cells(i, 5).Value = Sheets("SenDates").Cells(j, x + 1).Value
End If
Next j
End If
Next i
End If
Next ws
End Sub``````

#### ravishankar

##### Well-known Member
Hi
The syntax of vlookup given is OK and works too on insertion into your codes. if you want to have the lookup table in a different sheet then use
x = Application.WorksheetFunction.VLookup(Cells(12, 1), worksheets("sheet4").Range("S1:T100"), 2, False)
Ensure that data range mentioned in the formula match with what you have.
Ravi

#### cart0250

##### Active Member
I have got this to work when run by itself. I was trying to call it from another procedure which generated the error message,"unable to get the vLookUp property of the worksheet function class". Not sure why this is happening... but thanks for all your help with this.

#### cart0250

##### Active Member
I have one problem with this.

The below code is scrolling through the sheets of a workbook.
When vlookup finds a match for Sheet2, but does not find a match for Sheet 3, it continues to use the value found for Sheet2 rather than returning "empty" for Sheet 3.

Not sure why it is not assigning "empty" to x each time there is no match found rather than using the value that pertains to a previous sheet?

Anyone know how I can change this?

Code:
``````For Each ws In Worksheets
If ws.Name <> "Sheet1" And ws.Name <> "SenDates" Then
If ws.Range("A12").Value <> "" Then
On Error Resume Next

x = Application.WorksheetFunction.VLookup(ws.Cells(12, 1), Worksheets("SenDates").Range("IS1:IT80"), 2, False)

For i = 150 To 2 Step -1
If ws.Cells(i, 2).Value = personName Then
For j = 625 To 2 Step -1
If Sheets("SenDates").Cells(j, 2).Value = personName Then
ws.Cells(i, 4).Value = Sheets("SenDates").Cells(j, x).Value
ws.Cells(i, 5).Value = Sheets("SenDates").Cells(j, x + 1).Value
End If
Next j
End If
Next i
End If
End If
Next ws``````

Replies
6
Views
187
Replies
8
Views
312
Replies
2
Views
248
Replies
4
Views
213
Replies
1
Views
160

### Forum statistics

1,186,164
Messages
5,956,309
Members
438,246
Latest member
Deelea ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back