| Home | Forums | Reviews | Articles | Register |
 |
| Thread Tools | Rate Thread |
|
Guest
Posts: n/a
|
|
|
|
||
|
|
| |
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
another way using a range......
Sub DoSwipeCheck(ByVal SwipeID) Dim sRng As Range, c As Range Set sRng = Worksheets("Cashiers").Range("D6  18")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 -- Regards, Nigel (E-Mail Removed) "Steve" <(E-Mail Removed)> wrote in message news:iH1jj.64686$(E-Mail Removed)... > 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 > > > |
|
|
|
|
|||
|
Guest
Posts: n/a
|
Try this
Sub DoSwipeCheck(ByVal SwipeId) For Each cll In Sheets("Cashiers").Range("D6 18")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 "Steve" <(E-Mail Removed)> skrev i meddelelsen news:iH1jj.64686$(E-Mail Removed)... > 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 > > > |
|
|
|
|
|||
|
|
| |
|
| Thread Tools | |
| Rate This Thread | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Linq to XML--Are there code examples that make Linq as easy as SQL? Or how can I convert ths simple pseudo code into real code? | Reece | Microsoft C# .NET | 4 | 10th Dec 2008 03:13 AM |
| ATI Radeon Drivers - Code 43, Code 37 & Code 10 | =?Utf-8?B?SmFrZQ==?= | Windows Vista Hardware | 14 | 29th Aug 2006 05:50 AM |
| ATI Display Drivers - Code 43, Code 37, Code 10 | Jake | Windows Vista Hardware | 2 | 8th Jul 2006 04:00 PM |
| what is the difference between code inside a <script> tag and code in the code-behind file? | keithb | Microsoft ASP .NET | 1 | 29th Mar 2006 02:00 AM |
| [New] Zipoid - ZIP Code, City Name and Area Code Lookup - Zip Code to Zip Code Distance Calculation | Mel | Freeware | 0 | 22nd Jul 2005 04:13 PM |
