Case Structure too BIG

[cart0250]

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?

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
...

 

[ravishankar]

Hi
List 100+ cases in col S1 to S100+ and the corresponding column nos in col T (if it is 5 & 6 , put 5).

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]

thanks a lot! I'll give it a try.

 

[cart0250]

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:

x = Application.WorksheetFunction.VLookup(Cells(12, 1), Range("S1:T100"), 2, False)

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

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]

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]

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]

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?

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