CODEWITHSUNDEEP
excelvba
Jva2794
Jva2794

Reputation: 11

getting error about application-defined or object-defined error

Any help will be appreciated 

Sub changeRange(startRange As Integer, endRange As Integer, rowNum As Integer, col As Integer)

    Select Case col
        Case 5
            ThisWorkbook.Sheets("Campus Summary #").Cells(rowNum, col).Formula = "=COUNTIF('TCP Export'!L" & startRange & ":L" & endRange & ", '>0')"
        Case 7
            Worksheets("Campus Summary #").curCell.Formula = "COUNTCase('TCP Export'!MstartRange:MendRange, '>0')"
        Case 9
            Worksheets("Campus Summary #").curCell.Formula = "COUNTCase('TCP Export'!NstartRange:NendRange, '>0')"
        Case 11
            Worksheets("Campus Summary #").curCell.Formula = "COUNTCase('TCP Export'!OstartRange:OendRange, '>0')"
        Case 13
            Worksheets("Campus Summary #").curCell.Formula = "COUNTCase('TCP Export'!PstartRange:PendRange, '>0')"
        Case 15
            Worksheets("Campus Summary #").curCell.Formula = "COUNTCase('TCP Export'!QstartRange:QendRange, '>0')"
        Case 17
            Worksheets("Campus Summary #").curCell.Formula = "COUNTCase('TCP Export'!RstartRange:RendRange, '>0')"
        Case 19
            Worksheets("Campus Summary #").curCell.Formula = "COUNTCase('TCP Export'!SstartRange:SendRange, '>0')"
        Case 21
            Worksheets("Campus Summary #").curCell.Formula = "COUNTCase('TCP Export'!TstartRange:TendRange, '>0')"
        Case 23
            Worksheets("Campus Summary #").curCell.Formula = "COUNTCase('TCP Export'!UstartRange:UendRange, '>0')"
        Case 25
            Worksheets("Campus Summary #").curCell.Formula = "COUNTCase('TCP Export'!VstartRange:VendRange, '>0')"
        Case 27
            Worksheets("Campus Summary #").curCell.Formula = "COUNTCase('TCP Export'!WstartRange:WendRange, '>0')"
    End Select

End Sub

Upvotes: 1

Views: 45

Answers (2)

VBasic2008
VBasic2008

Reputation: 54807

Tricky Tricky

Sub changeRange(startRange As Integer, endRange As Integer, _
    rowNum As Integer, col As Integer)

  Const cStrWs1 As String = "Campus Summary #"
  Const cStrWs2 As String = "TCP Export"
  Const cStrCase As String = "5,7,9,11,13,15,17,19,21,23,25,27"
  Const cStrColumn As String = "L,M,N,O,P,Q,R,S,T,U,V,W"
  Const cStr1 As String = "COUNTIF('"
  Const cStr2 As String = "'!"
  Const cStr3 As String = ":"
  Const cStr4 As String = "," & Chr(34) & ">0" & Chr(34) & ")"

  Dim vntCase As Variant
  Dim vntColumn As Variant

  Dim i As Integer

  vntCase = Split(cStrCase, ",")
  vntColumn = Split(cStrColumn, ",")

    For i = 0 To UBound(vntCase)
      If col = CInt(Trim(cStrCase(i))) Then
        ThisWorkbook.Worksheets(cStrWs1).Cells(rowNum, col).Formula _
            = cStr1 & cStrWs2 & cStr2 & Trim(vntColumn(i)) & startRange & _
            cStr3 & Trim(vntColumn(i)) & endRange & cStr4
        Exit For
      End If
    Next

End Sub

Upvotes: 0

QHarr
QHarr

Reputation: 84465

Some pointers:

  1. I find your error confusing initially because there is no .curCell method/property of Worksheet object. You are using this where I would expect to find .Range or .Cells.
  2. When using .Cells, or indeed .Range, neither row nor column reference can be 0.
  3. It is misleading to have range in the name of variables passed as integers.
  4. Finally, you want Long rather than Integer to avoid potential overflow.

Upvotes: 2

Related Questions