Subscript out of Range Problem on One sheet but not on new sheets

Question

I am running into a problem where the VBA works on a new sheet but not the original sheet. I get a Runtime Error 9. Any advice on what might be going wrong?

The debugger notes a problem here: Runtime Error's specific location according to Debug

When I copy this code and paste it into a new sheet it works fine. I have checked all the worksheet names etc. and everything seems to check out. The most confusing part is that when I create a new workbook this error doesn't exist.

Here is the entire code:

Sub RunMyCode()

Dim iRtn As Integer
iRtn = 5
iRtn = RunStackadapt(iRtn)
iRtn = RunGoogleAds(iRtn)
iRtn = RunFacebook(iRtn)
iRtn = RunBing(iRtn)
iRtn = RunLinkedIn(iRtn)

End Sub

Function RunStackadapt(startLine As Integer) As Integer
   
    Set OrgSheet = Worksheets("Stackadapt")
    Set DesSheet = Worksheets("Rawdata")
    Dim sResult As String
    
    Dim jDes As Integer
            
    jDes = startLine
    For i = 2 To OrgSheet.UsedRange.Rows.Count
            jDes = jDes + 1
            DesSheet.Cells(jDes, 1) = OrgSheet.Cells(i, 1)
            DesSheet.Cells(jDes, 2) = OrgSheet.Cells(i, 3)
            DesSheet.Cells(jDes, 3) = fnDateFromWeek(OrgSheet.Cells(i, 2))
            DesSheet.Cells(jDes, 4) = OrgSheet.Cells(i, 4)
            DesSheet.Cells(jDes, 5) = OrgSheet.Cells(i, 5)
            DesSheet.Cells(jDes, 6) = OrgSheet.Cells(i, 6)
            DesSheet.Cells(jDes, 7) = OrgSheet.Cells(i, 7)
            DesSheet.Cells(jDes, 8) = OrgSheet.Cells(i, 8)
            DesSheet.Cells(jDes, 9) = OrgSheet.Cells(i, 9)
            DesSheet.Cells(jDes, 10) = OrgSheet.Cells(i, 10)
            DesSheet.Cells(jDes, 11) = OrgSheet.Cells(i, 11)
           
            
            If ((OrgSheet.Cells(i, 12) = " ") Or (OrgSheet.Cells(i, 11) = "0") Or (OrgSheet.Cells(i, 11) = 0)) Then
                  DesSheet.Cells(jDes, 12) = "N/A"
            Else
                  DesSheet.Cells(jDes, 12) = OrgSheet.Cells(i, 12) / 100
            End If
            
            
            If ((OrgSheet.Cells(i, 11) = " ") Or (OrgSheet.Cells(i, 11) = "0") Or (OrgSheet.Cells(i, 11) = 0)) Then
                  DesSheet.Cells(jDes, 13) = "N/A"
            Else
                  DesSheet.Cells(jDes, 13) = OrgSheet.Cells(i, 13) / OrgSheet.Cells(i, 11)
            End If
            
            DesSheet.Cells(jDes, 14) = OrgSheet.Cells(i, 13)
            DesSheet.Cells(jDes, 15) = OrgSheet.Cells(i, 14)
            DesSheet.Cells(jDes, 16) = OrgSheet.Cells(i, 15)
            DesSheet.Cells(jDes, 17) = "N/A"
            DesSheet.Cells(jDes, 18) = "N/A"
            DesSheet.Cells(jDes, 19) = "N/A"
    
    Next
    
    RunStackadapt = jDes
End Function


Function RunGoogleAds(startLine As Integer) As Integer
   
    Set OrgSheet = Worksheets("Google Ads")
    Set DesSheet = Worksheets("Rawdata")
    Dim sResult As String
    
    Dim jDes As Integer
            
    jDes = startLine
    For i = 2 To OrgSheet.UsedRange.Rows.Count
            jDes = jDes + 1
            DesSheet.Cells(jDes, 1) = OrgSheet.Cells(i, 1)
            DesSheet.Cells(jDes, 2) = OrgSheet.Cells(i, 2)
            DesSheet.Cells(jDes, 3) = fnDateFromWeek(OrgSheet.Cells(i, 3))
            DesSheet.Cells(jDes, 4) = OrgSheet.Cells(i, 4)
            DesSheet.Cells(jDes, 5) = OrgSheet.Cells(i, 5)
            DesSheet.Cells(jDes, 6) = OrgSheet.Cells(i, 6)
            DesSheet.Cells(jDes, 7) = OrgSheet.Cells(i, 7)
            DesSheet.Cells(jDes, 8) = OrgSheet.Cells(i, 8)
            DesSheet.Cells(jDes, 9) = OrgSheet.Cells(i, 9)
            DesSheet.Cells(jDes, 10) = OrgSheet.Cells(i, 10)
            DesSheet.Cells(jDes, 11) = OrgSheet.Cells(i, 11)
            DesSheet.Cells(jDes, 12) = OrgSheet.Cells(i, 12)
            DesSheet.Cells(jDes, 13) = OrgSheet.Cells(i, 13)
            DesSheet.Cells(jDes, 14) = OrgSheet.Cells(i, 14)
            DesSheet.Cells(jDes, 15) = OrgSheet.Cells(i, 15)
            DesSheet.Cells(jDes, 16) = "N/A"
            DesSheet.Cells(jDes, 17) = "N/A"
            DesSheet.Cells(jDes, 18) = "N/A"
            DesSheet.Cells(jDes, 19) = OrgSheet.Cells(i, 16)
    Next
    
    RunGoogleAds = jDes
End Function

Function RunFacebook(startLine As Integer) As Integer
   
    Set OrgSheet = Worksheets("Facebook")
    Set DesSheet = Worksheets("Rawdata")
    Dim sResult As String
    
    Dim jDes As Integer
            
    jDes = startLine
    For i = 2 To OrgSheet.UsedRange.Rows.Count
            jDes = jDes + 1
            DesSheet.Cells(jDes, 1) = OrgSheet.Cells(i, 1)
            DesSheet.Cells(jDes, 2) = OrgSheet.Cells(i, 2)
            DesSheet.Cells(jDes, 3) = fnDateFromWeek(OrgSheet.Cells(i, 3))
            DesSheet.Cells(jDes, 4) = OrgSheet.Cells(i, 4)
            DesSheet.Cells(jDes, 5) = OrgSheet.Cells(i, 5)
            DesSheet.Cells(jDes, 6) = OrgSheet.Cells(i, 6)
            DesSheet.Cells(jDes, 7) = OrgSheet.Cells(i, 7)
            DesSheet.Cells(jDes, 8) = OrgSheet.Cells(i, 8)
            DesSheet.Cells(jDes, 9) = OrgSheet.Cells(i, 9)
            DesSheet.Cells(jDes, 10) = OrgSheet.Cells(i, 10)
            DesSheet.Cells(jDes, 11) = OrgSheet.Cells(i, 11)
            DesSheet.Cells(jDes, 12) = OrgSheet.Cells(i, 12)
        
            
            If ((OrgSheet.Cells(i, 11) = " ") Or (OrgSheet.Cells(i, 11) = "0") Or (OrgSheet.Cells(i, 11) = 0)) Then
                  DesSheet.Cells(jDes, 13) = "N/A"
            Else
                  DesSheet.Cells(jDes, 13) = OrgSheet.Cells(i, 13) / OrgSheet.Cells(i, 11)
            End If
            
            DesSheet.Cells(jDes, 14) = OrgSheet.Cells(i, 13)
            DesSheet.Cells(jDes, 15) = OrgSheet.Cells(i, 14)
            DesSheet.Cells(jDes, 16) = OrgSheet.Cells(i, 15)
            DesSheet.Cells(jDes, 17) = OrgSheet.Cells(i, 16)
            DesSheet.Cells(jDes, 18) = OrgSheet.Cells(i, 17)
            DesSheet.Cells(jDes, 19) = "N/A"
    Next
    
    RunFacebook = jDes
End Function


Function RunBing(startLine As Integer) As Integer
   
    Set OrgSheet = Worksheets("Bing")
    Set DesSheet = Worksheets("Rawdata")
    Dim sResult As String
    
    Dim jDes As Integer
            
    jDes = startLine
    For i = 2 To OrgSheet.UsedRange.Rows.Count
            jDes = jDes + 1
            DesSheet.Cells(jDes, 1) = OrgSheet.Cells(i, 1)
            DesSheet.Cells(jDes, 2) = " "
            DesSheet.Cells(jDes, 3) = fnDateFromWeek(OrgSheet.Cells(i, 2))
            DesSheet.Cells(jDes, 4) = OrgSheet.Cells(i, 3)
            DesSheet.Cells(jDes, 5) = OrgSheet.Cells(i, 4)
            DesSheet.Cells(jDes, 6) = OrgSheet.Cells(i, 5)
            DesSheet.Cells(jDes, 7) = OrgSheet.Cells(i, 6)
            DesSheet.Cells(jDes, 8) = OrgSheet.Cells(i, 7)
            DesSheet.Cells(jDes, 9) = OrgSheet.Cells(i, 8)
            DesSheet.Cells(jDes, 10) = OrgSheet.Cells(i, 9)
            DesSheet.Cells(jDes, 11) = "N/A"
            DesSheet.Cells(jDes, 12) = "N/A"
            DesSheet.Cells(jDes, 13) = "N/A"
            DesSheet.Cells(jDes, 14) = OrgSheet.Cells(i, 10)
            DesSheet.Cells(jDes, 15) = "N/A"
            DesSheet.Cells(jDes, 16) = "N/A"
            DesSheet.Cells(jDes, 17) = "N/A"
            DesSheet.Cells(jDes, 18) = "N/A"
            
            
            If ((OrgSheet.Cells(i, 11) = " ") Or (OrgSheet.Cells(i, 11) = "0") Or (OrgSheet.Cells(i, 11) = 0)) Then
                  DesSheet.Cells(jDes, 19) = "N/A"
            Else
                  DesSheet.Cells(jDes, 19) = OrgSheet.Cells(i, 11) / 100
            End If
    Next
    
    RunBing = jDes
End Function


Function RunLinkedIn(startLine As Integer) As Integer
   
    Set OrgSheet = Worksheets("LinkedIn")
    Set DesSheet = Worksheets("Rawdata")
    Dim sResult As String
    
    Dim jDes As Integer
            
    jDes = startLine
    For i = 2 To OrgSheet.UsedRange.Rows.Count
            jDes = jDes + 1
            DesSheet.Cells(jDes, 1) = OrgSheet.Cells(i, 1)
            DesSheet.Cells(jDes, 2) = OrgSheet.Cells(i, 2)
            DesSheet.Cells(jDes, 3) = fnDateFromWeek(OrgSheet.Cells(i, 3))
            DesSheet.Cells(jDes, 4) = OrgSheet.Cells(i, 4)
            DesSheet.Cells(jDes, 5) = OrgSheet.Cells(i, 5)
            DesSheet.Cells(jDes, 6) = OrgSheet.Cells(i, 6)
            DesSheet.Cells(jDes, 7) = OrgSheet.Cells(i, 7)
            DesSheet.Cells(jDes, 8) = OrgSheet.Cells(i, 9)
            DesSheet.Cells(jDes, 9) = OrgSheet.Cells(i, 10)
            DesSheet.Cells(jDes, 10) = OrgSheet.Cells(i, 11)
            DesSheet.Cells(jDes, 11) = OrgSheet.Cells(i, 8)
        
            
            If ((OrgSheet.Cells(i, 8) = " ") Or (OrgSheet.Cells(i, 8) = "0") Or (OrgSheet.Cells(i, 8) = 0 Or (OrgSheet.Cells(i, 4) = 0))) Then
                  DesSheet.Cells(jDes, 12) = "N/A"
            Else
                  DesSheet.Cells(jDes, 12) = OrgSheet.Cells(i, 8) / OrgSheet.Cells(i, 4)
            End If
            
            If ((OrgSheet.Cells(i, 8) = " ") Or (OrgSheet.Cells(i, 8) = "0") Or (OrgSheet.Cells(i, 8) = 0 Or (OrgSheet.Cells(i, 12) = 0))) Then
                  DesSheet.Cells(jDes, 13) = "N/A"
            Else
                  DesSheet.Cells(jDes, 13) = OrgSheet.Cells(i, 12) / OrgSheet.Cells(i, 8)
            End If
            
            
            DesSheet.Cells(jDes, 14) = OrgSheet.Cells(i, 12)
            DesSheet.Cells(jDes, 15) = OrgSheet.Cells(i, 14)
            DesSheet.Cells(jDes, 16) = OrgSheet.Cells(i, 13)
            DesSheet.Cells(jDes, 17) = "N/A"
            DesSheet.Cells(jDes, 18) = OrgSheet.Cells(i, 15)
            DesSheet.Cells(jDes, 19) = "N/A"
    Next
    
    RunLinkedIn = jDes
End Function


Function fnDateFromWeek(ByVal sStr) As String
    Dim arr
    Dim iYear As Integer
    Dim iWeek As Integer
    Dim iWeekDday As Integer
    
    iWeekDday = 2
    
    arr = Split(sStr, "|")
    iYear = arr(0)
    iWeek = arr(1) + 1
    fnDateFromWeek = "'" + Format(DateSerial(iYear, 1, ((iWeek - 1) * 7) + iWeekDday - Weekday(DateSerial(iYear, 1, 1)) + 1), "yyyy-mm-dd")
End Function

If `sStr` is zero-length you'll get that error. `UsedRange` is not super-reliable when used as a method to determine how many rows of data you have. — Tim Williams, Jul 20 '21 at 06:51
Find the last row as shown [HERE](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) and then change the line for example `fnDateFromWeek(OrgSheet.Cells(i, 2))` to `If Len(Trim(OrgSheet.Cells(i, 2).Value2)) <> 0 Then DesSheet.Cells(jDes, 3) = fnDateFromWeek(OrgSheet.Cells(i, 2))`. Do this for others as well. — Siddharth Rout, Jul 20 '21 at 07:23

Subscript out of Range Problem on One sheet but not on new sheets

0 Answers0