+ Reply to Thread
Results 1 to 5 of 5

Simplifying code

Hybrid View

T De Villiers Simplifying code 02-23-2007, 06:08 AM
antoka05 The first idea I have may be... 02-23-2007, 06:39 AM
antoka05 I apologize to select a wrong... 02-23-2007, 06:40 AM
T De Villiers Hi, Thaks for the reply,... 02-23-2007, 06:42 AM
Leith Ross Hello T De Villiers, Add a... 02-23-2007, 06:50 AM
  1. 02-23-2007, 06:08 AM #1
    T De Villiers is offline
    Forum Contributor
    Join Date
    08-20-2005
    Posts
    173

    Simplifying code

    Hi,

    I have 20 worksheets which need the same code, below is code for first
    two worksheets which you can see is the same apart from worksheet
    and target names.
    Rather than copying and pasting code 19 times and replacing worksheet
    and target names, I would like something far shorter and efficient,
    maybe an array which goes through all 20 worksheets?

    The code is meant to go through 20 sheets, the summary sheet (here,
    referenced as "sr") takes values through lookups on each sheet.

    Many Thanks

    '1) Worksheet abn
    Set wss = Worksheets("abn")
    wss.Select
    target = "abn"
    Cells.Find(What:="itebal", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
    , SearchFormat:=False).Activate
    itebalcol = ActiveCell.Column

    For i = 500 To 2 Step -1
    If Left(sr.Cells(i, 1), 3) = target And sr.Cells(i, 1).Offset(0, 25) = "GBP" Then
    sr.Cells(i, 6) = WorksheetFunction.VLookup(sr.Cells(i, 1), wss.Range("A1:Z10000"), itebalcol, 0)
    End If
    If Left(sr.Cells(i, 1), 3) = target And sr.Cells(i, 1).Offset(0, 25) = "EUR" Then
    sr.Cells(i, 6) = WorksheetFunction.VLookup(sr.Cells(i, 1), wss.Range("A1:Z10000"), itebalcol, 0)
    End If
    If Left(sr.Cells(i, 1), 3) = target And sr.Cells(i, 1).Offset(0, 25) = "USD" Then
    sr.Cells(i, 6) = WorksheetFunction.VLookup(sr.Cells(i, 1), wss.Range("A1:Z10000"), itebalcol, 0)
    End If
    If Left(sr.Cells(i, 1), 3) = target And sr.Cells(i, 1).Offset(0, 25) = "RUB" Then
    sr.Cells(i, 6) = WorksheetFunction.VLookup(sr.Cells(i, 1), wss.Range("A1:Z10000"), itebalcol, 0)
    End If
    If Left(sr.Cells(i, 1), 3) = target And _
    sr.Cells(i, 1).Offset(0, 25) <> "GBP" And sr.Cells(i, 1).Offset(0, 25) <> "EUR" And sr.Cells(i, 1).Offset(0, 25) <> "USD" And sr.Cells(i, 1).Offset(0, 25) <> "RUB" Then
    sr.Cells(i, 10) = WorksheetFunction.VLookup(sr.Cells(i, 1), wss.Range("A1:Z10000"), itebalcol, 0)
    End If

    '2) Worksheet cap
    Set wss = Worksheets("cap")
    wss.Select
    target = "cap"
    Cells.Find(What:="itebal", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
    , SearchFormat:=False).Activate
    itebalcol = ActiveCell.Column

    For i = 500 To 2 Step -1
    If Left(sr.Cells(i, 1), 3) = target And sr.Cells(i, 1).Offset(0, 25) = "GBP" Then
    sr.Cells(i, 6) = WorksheetFunction.VLookup(sr.Cells(i, 1), wss.Range("A1:Z10000"), itebalcol, 0)
    End If
    If Left(sr.Cells(i, 1), 3) = target And sr.Cells(i, 1).Offset(0, 25) = "EUR" Then
    sr.Cells(i, 6) = WorksheetFunction.VLookup(sr.Cells(i, 1), wss.Range("A1:Z10000"), itebalcol, 0)
    End If
    If Left(sr.Cells(i, 1), 3) = target And sr.Cells(i, 1).Offset(0, 25) = "USD" Then
    sr.Cells(i, 6) = WorksheetFunction.VLookup(sr.Cells(i, 1), wss.Range("A1:Z10000"), itebalcol, 0)
    End If
    If Left(sr.Cells(i, 1), 3) = target And sr.Cells(i, 1).Offset(0, 25) = "RUB" Then
    sr.Cells(i, 6) = WorksheetFunction.VLookup(sr.Cells(i, 1), wss.Range("A1:Z10000"), itebalcol, 0)
    End If
    If Left(sr.Cells(i, 1), 3) = target And _
    sr.Cells(i, 1).Offset(0, 25) <> "GBP" And sr.Cells(i, 1).Offset(0, 25) <> "EUR" And sr.Cells(i, 1).Offset(0, 25) <> "USD" And sr.Cells(i, 1).Offset(0, 25) <> "RUB" Then
    sr.Cells(i, 10) = WorksheetFunction.VLookup(sr.Cells(i, 1), wss.Range("A1:Z10000"), itebalcol, 0)
    End If
    Reply With QuoteRegister To Reply
  2. 02-23-2007, 06:39 AM #2
    antoka05 is offline
    Forum Expert
    Join Date
    11-23-2005
    Location
    Rome
    MS-Off Ver
    Ms Office 2016
    Posts
    1,628
    The first idea I have may be this code:

       For Each wss In ThisWorkbook.Worksheets
   
      '1) Worksheet abn
      'Set wss = Worksheets("abn")
      wss.Select
      target = "abn"
      Cells.Find(What:="itebal", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
      xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
      , SearchFormat:=False).Activate
      itebalcol = ActiveCell.Column

      For i = 500 To 2 Step -1
         If Left(sr.Cells(i, 1), 3) = target And sr.Cells(i, 1).Offset(0, 25) = "GBP" Then
            sr.Cells(i, 6) = WorksheetFunction.VLookup(sr.Cells(i, 1), wss.Range("A1:Z10000"), itebalcol, 0)
         End If
         
         If Left(sr.Cells(i, 1), 3) = target And sr.Cells(i, 1).Offset(0, 25) = "EUR" Then
            sr.Cells(i, 6) = WorksheetFunction.VLookup(sr.Cells(i, 1), wss.Range("A1:Z10000"), itebalcol, 0)
         End If
         
         If Left(sr.Cells(i, 1), 3) = target And sr.Cells(i, 1).Offset(0, 25) = "USD" Then
            sr.Cells(i, 6) = WorksheetFunction.VLookup(sr.Cells(i, 1), wss.Range("A1:Z10000"), itebalcol, 0)
         End If
         
         If Left(sr.Cells(i, 1), 3) = target And sr.Cells(i, 1).Offset(0, 25) = "RUB" Then
            sr.Cells(i, 6) = WorksheetFunction.VLookup(sr.Cells(i, 1), wss.Range("A1:Z10000"), itebalcol, 0)
         End If
         
         If Left(sr.Cells(i, 1), 3) = target And _
            sr.Cells(i, 1).Offset(0, 25) <> "GBP" And sr.Cells(i, 1).Offset(0, 25) <> "EUR" And sr.Cells(i, 1).Offset(0, 25) <> "USD" And sr.Cells(i, 1).Offset(0, 25) <> "RUB" Then
            sr.Cells(i, 10) = WorksheetFunction.VLookup(sr.Cells(i, 1), wss.Range("A1:Z10000"), itebalcol, 0)
         End If
      Next
   Next

    Regards,
    Antonio
    Reply With QuoteRegister To Reply
  3. 02-23-2007, 06:40 AM #3
    antoka05 is offline
    Forum Expert
    Join Date
    11-23-2005
    Location
    Rome
    MS-Off Ver
    Ms Office 2016
    Posts
    1,628
    I apologize to select a wrong color.

    For Each wss In ThisWorkbook.Worksheets   
      '1) Worksheet abn
      'Set wss = Worksheets("abn")
      wss.Select
      target = "abn"
      Cells.Find(What:="itebal", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
      xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
      , SearchFormat:=False).Activate
      itebalcol = ActiveCell.Column

      For i = 500 To 2 Step -1
         If Left(sr.Cells(i, 1), 3) = target And sr.Cells(i, 1).Offset(0, 25) = "GBP" Then
            sr.Cells(i, 6) = WorksheetFunction.VLookup(sr.Cells(i, 1), wss.Range("A1:Z10000"), itebalcol, 0)
         End If
         
         If Left(sr.Cells(i, 1), 3) = target And sr.Cells(i, 1).Offset(0, 25) = "EUR" Then
            sr.Cells(i, 6) = WorksheetFunction.VLookup(sr.Cells(i, 1), wss.Range("A1:Z10000"), itebalcol, 0)
         End If
         
         If Left(sr.Cells(i, 1), 3) = target And sr.Cells(i, 1).Offset(0, 25) = "USD" Then
            sr.Cells(i, 6) = WorksheetFunction.VLookup(sr.Cells(i, 1), wss.Range("A1:Z10000"), itebalcol, 0)
         End If
         
         If Left(sr.Cells(i, 1), 3) = target And sr.Cells(i, 1).Offset(0, 25) = "RUB" Then
            sr.Cells(i, 6) = WorksheetFunction.VLookup(sr.Cells(i, 1), wss.Range("A1:Z10000"), itebalcol, 0)
         End If
         
         If Left(sr.Cells(i, 1), 3) = target And _
            sr.Cells(i, 1).Offset(0, 25) <> "GBP" And sr.Cells(i, 1).Offset(0, 25) <> "EUR" And sr.Cells(i, 1).Offset(0, 25) <> "USD" And sr.Cells(i, 1).Offset(0, 25) <> "RUB" Then
            sr.Cells(i, 10) = WorksheetFunction.VLookup(sr.Cells(i, 1), wss.Range("A1:Z10000"), itebalcol, 0)
         End If
      Next
   Next
    Reply With QuoteRegister To Reply
  4. 02-23-2007, 06:42 AM #4
    T De Villiers is offline
    Forum Contributor
    Join Date
    08-20-2005
    Posts
    173
    Hi,

    Thaks for the reply, however am not sure this will work as I am not looping through all worksheets, only specific ones.

    Many Thanks
    Reply With QuoteRegister To Reply
  5. 02-23-2007, 06:50 AM #5
    Leith Ross is offline
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259
    Hello T De Villiers,

    Add a Standard VBA module to your workbook and then copy and paste the maco code into it. See the example below for calling the macro in your code.

    Sub SummaryMacro(Worksheet_Name As String)

  Set wss = Worksheets(Worksheet_Name)
  wss.Select
  target = Worksheet_Name
 
  Cells.Find(What:="itebal", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
  itebalcol = ActiveCell.Column

    If Left(sr.Cells(i, 1), 3) = target Then
      Select Case sr.Cells(i, 1).Offset(0, 25) 
        Case Is = "GBP", "EUR", "USD", "RUB"
          sr.Cells(i, 6) = WorksheetFunction.VLookup(sr.Cells(i, 1), wss.Range("A1:Z10000"), itebalcol, 0)
        Case Else
          sr.Cells(i, 10) = WorksheetFunction.VLookup(sr.Cells(i, 1), wss.Range("A1:Z10000"), itebalcol, 0)
      End Select
  End If

End Sub
    Calling the Macro:
    Your code now looks like this....
    SummaryMacro "abn"
    SummaryMacro "cap"
    ...etc

    Sincerely,
    Leith Ross
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1