+ Reply to Thread
Results 1 to 4 of 4

Debug Help?

Hybrid View

  1. #1
    Registered User
    Join Date
    08-12-2009
    Location
    Cardiff, Wales
    MS-Off Ver
    Excel 2007
    Posts
    53

    Debug Help?

    Help, I did have this code working but made some changes and lost a save when my pc crashed.
    Cant seem to get the following 2 lines right?
    MyArr(I, 2) = Rng.Offset(0, 1).Value
    MyArr(I, 3) = Rng.Offset(0, 2).Value
    This code basically searches all worksheets for each value in an array, when it finds each value it needs to add 2 offset values from the found values next to the original value in the array (thats the bit im stuck on). Cant see what im not doing, staring me in the face no doubt?
    This is the whole code:
    Option Explicit
    Sub Mark_Cells_In_Column()
     Dim Rng                As Excel.Range
     Dim Sh                 As Excel.Worksheet
     Dim rngBins            As Excel.Range
     Dim MyArr              As Variant
     Dim scpSheets          As Object
     Dim FirstAddress       As String
     Dim I                  As Long
    
     Sheets("Sheet1").Select
     With Application
        .ScreenUpdating = False
        .EnableEvents = False
     End With
     Set rngBins = Range("A:A")
     MyArr = rngBins.Value
     Set scpSheets = CreateObject("Scripting.Dictionary")
     scpSheets.CompareMode = 1
     For Each Sh In ThisWorkbook.Worksheets
        If Sh.Name <> "Sheet1" Then
            With Sh.Range("A:A")
                For I = LBound(MyArr) To UBound(MyArr)
                    If MyArr(I, 1) <> "" Then
                        Set Rng = .Find(What:=MyArr(I, 1), After:=.Cells(.Cells.Count), _
                            LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, MatchCase:=False)
                        If Not Rng Is Nothing Then
                            FirstAddress = Rng.Address
                            If Not scpSheets.Exists(Sh.Name) Then
                                scpSheets.Item(Sh.Name) = Sh.Index
                            End If
                            Do
                                MyArr(I, 2) = Rng.Offset(0, 1).Value
                                MyArr(I, 3) = Rng.Offset(0, 2).Value
                                Set Rng = .FindNext(Rng)
                            Loop While Not Rng Is Nothing And Rng.Address <> FirstAddress
                        End If
                    End If
                Next I
            End With
        End If
     Next Sh
     Set Rng = Nothing
     Set rngBins = Nothing
     With Application
        .ScreenUpdating = True
        .EnableEvents = True
     End With
    End Sub

  2. #2
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Debug Help?

    MyArr(I, 2) = Rng.Offset(0, 1).Value
    MyArr(I, 3) = Rng.Offset(0, 2).Value
    You are assigning, or copying the rng found in to these two arrays, but what do you wish to do after copying them in to an array? If you wish to copy them in to a range, you do not need to copy them in to array, you can do it directly, or you have to copy back the array you assigned in to a range

  3. #3
    Registered User
    Join Date
    08-12-2009
    Location
    Cardiff, Wales
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Debug Help?

    Im getting myself so confused!! I need to copy the offset values into the offset cells of each of the array values.
    So, if A1 is the first value of the array, I need to copy the offset values from the found rng into B1 and C1. Does that make sense?

  4. #4
    Registered User
    Join Date
    08-12-2009
    Location
    Cardiff, Wales
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Debug Help?

    Just realised im being a fool!!!! As you said just need to copy directly.
    Used this line:
    ThisWorkbook.Worksheets("Sheet1").Cells(I, 7) = Rng.Offset(0, 1).Value
    Now works fine
    Not sure what I was thinking??

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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