Code to big!

[Steve]

How can I make the follwoing code smaller?

Sub DoSwipeCheck(ByVal SwipeId)

If Worksheets("Cashiers").Range("D6").Value = SwipeId Then
GetNameFromSwipe ("C6")
Sheets("CustomerDetails").Select
ElseIf Worksheets("Cashiers").Range("D7").Value = SwipeId Then
GetNameFromSwipe ("C7")
Sheets("CustomerDetails").Select
ElseIf Worksheets("Cashiers").Range("D8").Value = SwipeId Then
GetNameFromSwipe ("C8")
Sheets("CustomerDetails").Select
ElseIf Worksheets("Cashiers").Range("D9").Value = SwipeId Then
GetNameFromSwipe ("C9")
Sheets("CustomerDetails").Select
ElseIf Worksheets("Cashiers").Range("D10").Value = SwipeId Then
GetNameFromSwipe ("C10")
Sheets("CustomerDetails").Select
ElseIf Worksheets("Cashiers").Range("D11").Value = SwipeId Then
GetNameFromSwipe ("C11")
Sheets("CustomerDetails").Select
ElseIf Worksheets("Cashiers").Range("D12").Value = SwipeId Then
GetNameFromSwipe ("C12")
Sheets("CustomerDetails").Select
ElseIf Worksheets("Cashiers").Range("D13").Value = SwipeId Then
GetNameFromSwipe ("C13")
Sheets("CustomerDetails").Select
ElseIf Worksheets("Cashiers").Range("D14").Value = SwipeId Then
GetNameFromSwipe ("C14")
Sheets("CustomerDetails").Select
ElseIf Worksheets("Cashiers").Range("D15").Value = SwipeId Then
GetNameFromSwipe ("C15")
Sheets("CustomerDetails").Select
ElseIf Worksheets("Cashiers").Range("D16").Value = SwipeId Then
GetNameFromSwipe ("C16")
Sheets("CustomerDetails").Select
ElseIf Worksheets("Cashiers").Range("D17").Value = SwipeId Then
GetNameFromSwipe ("C17")
Sheets("CustomerDetails").Select
ElseIf Worksheets("Cashiers").Range("D18").Value = SwipeId Then
GetNameFromSwipe ("C18")
Sheets("CustomerDetails").Select
Else

End If
End Sub


---
avast! Antivirus: Outbound message clean.
Virus Database (VPS): 080114-3, 14/01/2008
Tested on: 15/01/2008 12:28:32
avast! - copyright (c) 1988-2008 ALWIL Software.
http://www.avast.com

 

[papou]

Hi Steve

Sub DoSwipeCheck(ByVal SwipeId)
Dim i As Integer
For i = 6 To 18

If Worksheets("Cashiers").Range("D" & i).Value = SwipeId Then
GetNameFromSwipe ("C" & i)
Sheets("CustomerDetails").Select
End If
Next i
End Sub

HTH
Cordially
Pascal

 

[Nigel]

another way using a range......

Sub DoSwipeCheck(ByVal SwipeID)
Dim sRng As Range, c As Range
Set sRng = Worksheets("Cashiers").Range("D618")
For Each c In sRng
If c.Value = SwipeID Then
GetNameFromSwipe ("C" & c.Row)
Sheets("CustomerDetails").Select
Exit For
End If
Next c
End Sub

 

[Lazzzx]

Try this

Sub DoSwipeCheck(ByVal SwipeId)
For Each cll In Sheets("Cashiers").Range("D618")
If cll = SwipeID Then
GetNameFromSwipe (cll.Offset(0, -1).Address(RowAbsolute:=False,
ColumnAbsolute:=False))
Sheets("CustomerDetails").Select
End If
Next
End Sub

Setting the RowAbsolute and ColumnAbsolute to False changes removes the
$-signs from the Address.

regds,
Lazzzx