Case Structure too BIG

cart0250

Active Member
Joined
Jun 24, 2006
Messages
284
Office Version
  1. 2016
Platform
  1. Windows
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

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top