Results 1 to 8 of 8

Run Time Error in coding - Can someone help me debug please?

Threaded View

DMacSTB Run Time Error in coding -... 01-31-2013, 06:56 AM
DMacSTB Re: Run Time Error in coding... 01-31-2013, 07:11 AM
Andrew-R Re: Run Time Error in coding... 01-31-2013, 07:11 AM
DMacSTB Re: Run Time Error in coding... 01-31-2013, 07:28 AM
Andrew-R Re: Run Time Error in coding... 01-31-2013, 08:13 AM
DMacSTB Re: Run Time Error in coding... 01-31-2013, 09:17 AM
Andrew-R Re: Run Time Error in coding... 01-31-2013, 09:23 AM
DMacSTB Re: Run Time Error in coding... 01-31-2013, 09:30 AM
  1. #1
    Registered User
    Join Date
    07-10-2012
    Location
    Edinburgh
    MS-Off Ver
    Excel 2003
    Posts
    41

    Run Time Error in coding - Can someone help me debug please?

    I have attached an example file with a macro that strips data found in one column from matching data in another column.

    I get a run time error on the line:
    arrVariant = rngVariant.Cells(1, 1).Offset(1, 0).Resize(lngLastRow - 1, 1).Value

    Could someone help me fix the script please?

    Option Compare Text
    
    Public Sub StripVariants()
    
        Call StripVariant(Worksheets("Sheet1").Range("D:D"), Worksheets("Sheet1").Range("G:G"))
        Call StripVariant(Worksheets("Sheet1").Range("E:E"), Worksheets("Sheet1").Range("G:G"))
    
    End Sub
    
    Public Sub StripVariant(ByVal rngVariant As Excel.Range, ByVal rngText As Excel.Range)
    
        Dim arrVariant          As Variant
        Dim arrText             As Variant
    
        Dim lngLastRow          As Long
        Dim lngRow              As Long
        Dim intCol              As Integer
        Dim intPos              As Integer
    
        intCol = rngVariant.Column
        lngLastRow = rngVariant.Parent.Columns(intCol).Find(What:="*", After:=Cells(1, intCol), _
            SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
            LookAt:=xlPart, LookIn:=xlValues).Row
        arrVariant = rngVariant.Cells(1, 1).Offset(1, 0).Resize(lngLastRow - 1, 1).Value
        arrText = rngText.Cells(1, 1).Offset(1, 0).Resize(lngLastRow - 1, 1).Value
    
        For lngRow = LBound(arrVariant) To UBound(arrVariant)
            If Len(Trim(arrVariant(lngRow, 1))) > 0 Then
                intPos = InStr(1, arrText(lngRow, 1), Trim(arrVariant(lngRow, 1)))
                If intPos > 0 Then
                    arrText(lngRow, 1) = Left(arrText(lngRow, 1), intPos - 1) _
                        & Mid(arrText(lngRow, 1), intPos + Len(arrVariant(lngRow, 1)))
                    If Mid(arrText(lngRow, 1), intPos - 1, 2) = "  " Then
                        arrText(lngRow, 1) = Left(arrText(lngRow, 1), intPos - 1) _
                            & Mid(arrText(lngRow, 1), intPos + 1)
                    ElseIf Mid(arrText(lngRow, 1), intPos - 1, 3) = " , " Then
                        arrText(lngRow, 1) = Left(arrText(lngRow, 1), intPos - 2) _
                            & Mid(arrText(lngRow, 1), intPos + 1)
                    End If
                End If
            End If
        Next lngRow
    
        rngText.Cells(2, 1).Resize(UBound(arrText), 1).Value = arrText
    
    End Sub
    Attached Files Attached Files

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