Results 1 to 15 of 15

Search vba array if value found then do something

Threaded View

  1. #1
    Forum Contributor
    Join Date
    04-03-2009
    Location
    USA, California
    MS-Off Ver
    Excel 2007
    Posts
    385

    Search vba array if value found then do something

    I need to search a VBA array for a value, if the value is found then I want to skip a few lines of code. If more info is needed I can provide more information. I realize there will most likely be duplicates in my array due to the way the below code is written. Therefore some code to eliminate the dupes would be appreciated. Thanks in advance for the help.

    Below is the code currently without the array
    sub printerrors()
    Dim Cell, Cell2 As Range
    Dim LR, LR2 As Long
    Dim Up, Down As Integer
    Dim ItemNumber As String
    Dim MyVariant As Variant
    Dim r  As Integer
    
    With ActiveSheet.PageSetup
            .PrintTitleRows = "$1:$1"
    End With
    
    LR = Range("A" & Rows.count).End(xlUp).Row
    
    With Range("L2:L" & LR)
    .FormulaR1C1 = "=IF(RC[-3]="""","""",COUNTIF(R[-1]C[-11]:R2C[-11],RC[-11]))"
    Calculate
    .Value = .Value
    End With
    
    With Range("M2:M" & LR)
    .FormulaR1C1 = "=IF(RC[-4]="""","""",COUNTIF(R[1]C[-12]:R[388]C[-12],RC[-12]))"
    Calculate
    .Value = .Value
    End With
    
    For Each Cell In Range("I2:I" & LR)
    
    If Cell.Value = "ERROR" Then
    
    Up = Cell.Offset(, 3).Value
    Down = Cell.Offset(, 4).Value
    
    Range("A" & Cell.Row - Up & ":" & "H" & Cell.Row + Down).Select
    
    ItemNumber = Cell.Offset(, -8).Value
    
    LR2 = Range("N" & Rows.count).End(xlUp).Row
    
    ' code needed to check if value is contained in the array, if the value is found then I need to skip to skipprint
    
    'Selection.PrintOut Copies:=1, Collate:=True
    
    SkipPrint:
    Next Cell
    
    Range("A1").Select
    
    
    End Sub
    Last edited by randell.graybill; 05-08-2010 at 08:02 PM.

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1