Hi,

I have a Case statement that seems to me to be ridiculously long:
Do
Select Case rSearch.Value
Case sName
ScreenUpdating = False
If rSearch.Offset(6, 0) < "" Then
Sheets("blank").Range("c46") = rSearch.Offset(6, 0).Value
End If
If rSearch.Offset(12, 0) < "" Then
Sheets("blank").Range("c47") = rSearch.Offset(12, 0).Value
End If

'<about 120 more IF statements all pretty much the same as
these, only the source and target offset addresses change

Sheets("blank").Visible = True
Sheets("blank").Select
Range("B1:Z69").Select
Range("Y69").Activate
ActiveSheet.PageSetup.PrintArea = "$B$1:$Z$69"
With ActiveSheet.PageSetup
.Orientation = xlLandscape
.PaperSize = xlPaperA4
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
Range("C5").Select

ScreenUpdating = True
Exit Do
Case ""
MsgBox ("please enter a valid name")
Exit Do
Case Else
Set rSearch = rSearch.Offset(0, 1)
iCtr = iCtr + 1
End Select
Loop


A simple question, is there a more efficient way of doing this? It's not
that the code is slow, just seems to me to not be vary concise.

TIA

Dave