Results 1 to 15 of 15

help changing repeating code into array of somekind also copy highlighted rows to new shee

Threaded View

  1. #1
    Registered User
    Join Date
    10-01-2014
    Location
    Tx
    MS-Off Ver
    2013
    Posts
    50

    help changing repeating code into array of somekind also copy highlighted rows to new shee

    I am repeating the following code many times for varying cell contents. I know there must be a better way. I tried to change the code to array myself but apparently I was not doing something correctly. I am new to this (duh.lol)! I found the code in a forum and changed highlight cell to highlight entire row because the next step is to copy all rows in which are highlighted to a new sheet within the same workbook. Also the cell contents the code is searching for can be anywhere in column A thru M on any given row on the active sheet. I have Excel 2013 and am running Windows8.1.
    1. Should I use variant array, string array, named range(no clue how to do but have read about it), or another function?
    2.Could you please point me in the right direction as to how to copy highlighted rows to a new sheet?
    3.Lastly, should I be highlighting the entire row or just the active cells(as that is all I need) in the needed row? .
    Your help is very appreciated!!!

    Sub HighlightCells()
        
    Dim i As Long
    Dim Fnd As String
    Dim fCell As Range
    
    Fnd = ("DVOK8467*")
    
        Set fCell = Range("A1")
            For i = 1 To WorksheetFunction.CountIf(ActiveSheet.UsedRange, Fnd)
                Set fCell = Cells.Find(What:=Fnd, After:=fCell, _
                    LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, MatchCase:=False)
                    
                If fCell Is Nothing Then
                MsgBox Fnd & " not on sheet !!"
                Exit Sub
                Else
                 With fCell
                     .EntireRow.Interior.ColorIndex = 6
                 End With
                End If
                
            Next i
    
    
    Fnd = ("DVOK8443*")
    
        Set fCell = Range("A1")
            For i = 1 To WorksheetFunction.CountIf(ActiveSheet.UsedRange, Fnd)
                Set fCell = Cells.Find(What:=Fnd, After:=fCell, _
                    LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, MatchCase:=False)
                    
                If fCell Is Nothing Then
                MsgBox Fnd & " not on sheet !!"
                Exit Sub
                Else
                 With fCell
                     .EntireRow.Interior.ColorIndex = 6
                 End With
                End If
                
            Next i
            
            
    Fnd = ("DVOK8720*")
    
        Set fCell = Range("A1")
            For i = 1 To WorksheetFunction.CountIf(ActiveSheet.UsedRange, Fnd)
                Set fCell = Cells.Find(What:=Fnd, After:=fCell, _
                    LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, MatchCase:=False)
                    
                If fCell Is Nothing Then
                MsgBox Fnd & " not on sheet !!"
                Exit Sub
                Else
                 With fCell
                     .EntireRow.Interior.ColorIndex = 6
                 End With
                End If
                
            Next i
           
           
     Fnd = ("5DVIA8797*")
    
        Set fCell = Range("A1")
            For i = 1 To WorksheetFunction.CountIf(ActiveSheet.UsedRange, Fnd)
                Set fCell = Cells.Find(What:=Fnd, After:=fCell, _
                    LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, MatchCase:=False)
                    
                If fCell Is Nothing Then
                MsgBox Fnd & " not on sheet !!"
                Exit Sub
                Else
                 With fCell
                     .EntireRow.Interior.ColorIndex = 6
                 End With
                End If
                
            Next i
            
            
     
    Fnd = ("DVIA8809*")
    
        Set fCell = Range("A1")
            For i = 1 To WorksheetFunction.CountIf(ActiveSheet.UsedRange, Fnd)
                Set fCell = Cells.Find(What:=Fnd, After:=fCell, _
                    LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, MatchCase:=False)
                    
                If fCell Is Nothing Then
                MsgBox Fnd & " not on sheet !!"
                Exit Sub
                Else
                 With fCell
                     .EntireRow.Interior.ColorIndex = 6
                 End With
                End If
                
            Next i
                   
            
    End Sub
    Again thank you for taking the time to read my ramblings and helping me!
    Last edited by baby_kay_2003; 10-22-2014 at 10:55 AM.
    baby_kay_2003

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Copy Highlighted Rows to another sheet
    By MBCMDR in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 03-07-2016, 08:56 AM
  2. [SOLVED] copy all highlighted rows to new sheet in order
    By cfinch100 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-22-2014, 02:21 PM
  3. Replies: 0
    Last Post: 11-12-2013, 04:56 PM
  4. [SOLVED] VBA Code: copy relative array formula into X rows
    By haven83 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-30-2012, 01:46 AM
  5. copy highlighted rows from Auto filter
    By tahirawan11 in forum Excel General
    Replies: 1
    Last Post: 06-19-2008, 12:00 PM

Tags for this Thread

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