vba problem: cant execute excel 2003 code in excel 2007.....

**phendark** · 12-09-2009, 03:37 AM

I got an excel 2003 file that contain vba code for manipulating the data inside it. Previously, it works with excel 2003 but it cannot work when i use excel 2007....
Here is the code :

Code:

Private Sub CommandButton1_Click()
Dim i, j, k, tempCol, tempRow, NoOfData, l, m, n As Integer
Dim Total, tempTotal
Dim tagname(108), Value As String
piserver = ""
Dim Formula, CellAdd As String
Dim MyRange As Range


i = 1
j = 0

Call Clear

Sheet2.Activate

Do
    If j = 0 Then
        j = j + 1
    Else
        j = j + 2
    End If
    Sheet2.Cells(i, j).Select
Loop Until Sheet2.Cells(i, j).Value = Sheet1.ComboBox1.Text

Do
    If i = 1 Then
        i = 2
    End If
    tagname(i) = Sheet2.Cells(i, j).Value
    i = i + 1
Loop Until Sheet2.Cells(i, j) = ""

Sheet1.Activate

For k = 0 To i - 3

If k = 0 Then
    tempCol = 2
    tempRow = 19
ElseIf k Mod 4 <> 0 Then
    tempCol = tempCol + 3
ElseIf k Mod 4 = 0 Then
    tempRow = tempRow + 5
    tempCol = 2
End If

Sheet1.Cells(tempRow, tempCol).Value = tagname(k + 2)
Sheet1.Cells(tempRow, tempCol + 1).Value = "Seconds"
Sheet1.Cells(tempRow, tempCol + 2).Value = " % "

Sheet1.Cells(tempRow + 1, tempCol).Value = "Failed"
Sheet1.Cells(tempRow + 2, tempCol).Value = "Bad Input"
Sheet1.Cells(tempRow + 3, tempCol).Value = "Normal"

startadd = Sheet1.Cells(tempRow, tempCol).Address
endadd = Sheet1.Cells(tempRow + 3, tempCol + 2).Address
Sheet1.Activate
Sheet1.Range(startadd & ":" & endadd).Select
Call FormatBorder

Sheet3.Activate
    Sheet3.Columns("A:B").Select
    Selection.ClearContents

    'Creating PI-Datalink: PI Compressed Data Formula
    Sheet3.Cells(1, 1).FormulaArray = "=PICompDat(""" & tagname(k + 2) & """,""" & Sheet1.DTPicker1.Value & """,""" & Sheet1.DTPicker2.Value & """,1," & """" & piserver & """" & "," & """inside""" & ")"
    Sheet3.Cells(1, 1).Select
    CellAdd = Selection.Address

    'To List Down All Compressed Values
    Set MyRange = Sheet3.Range(CellAdd, Range(CellAdd).Offset(65535, 1).End(xlDown).Address) ' set the range of the array formula

    Formula = Sheet3.Range(CellAdd).FormulaArray 'save the formula to paste back later
    MyRange.ClearContents 'erase the range
    MyRange.FormulaArray = Formula 'paste the formula back

NoOfData = Sheet3.Cells(1, 2).Value
'*******************************Another Story***********************************************

For n = 1 To 3

    Select Case n
    
        Case 1: Value = "Failed"
        Case 2: Value = "Bad Input"
        Case 3: Value = "Normal"
    
    End Select
    
    l = 2
    m = 2
    Total = 0
    tempTotal = 0
    tempdate = ""
    tempdate2 = ""
    
    Do
    
        If Sheet3.Cells(l, m).Value = Value Then
                tempdate = Sheet3.Cells(l, m - 1)
                tempdate2 = Sheet3.Cells(l + 1, m - 1)
            If Trim(Sheet3.Cells(l + 1, m - 1)) <> "" Then
                tempTotal = DateDiff("s", tempdate, tempdate2)
                Total = Total + tempTotal
                tempTotal = 0
                tempdate = ""
                tempdate2 = ""
            End If
        End If
        
        l = l + 1
    Loop Until (Trim(Sheet3.Cells(l, m).Text) = "#VALUE!") Or (Trim(Sheet3.Cells(l, m).Text) = "") Or (Trim(Sheet3.Cells(l, m).Text) = "#N/A")

Sheet1.Cells(tempRow + n, tempCol + 1).Value = Total
startdate = Sheet3.Cells(2, 1).Value
enddate = Sheet3.Cells(2 + NoOfData - 1, 1).Value
timerange = DateDiff("s", startdate, enddate)
If timerange <> 0 Then
Sheet1.Cells(tempRow + n, tempCol + 2).Value = Format((Total / timerange) * 100, "#.00")
Else
Sheet1.Cells(tempRow + n, tempCol + 2).Value = Format(0, "#.00")
End If
startdate = ""
enddate = ""
timerange = ""

Next n

Next k

    Sheet3.Columns("A:B").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
Sheet3.Range("A" & NoOfData + 2 & ": B65536").Select
Selection.ClearContents
Sheet3.Cells(1, 1).Select
Sheet1.Activate
Sheet1.Cells(5, 2).Select

End Sub

Somehow the error goes to line:

Code:

Loop Until (Trim(Sheet3.Cells(l, m).Text) = "#VALUE!") Or (Trim(Sheet3.Cells(l, m).Text) = "") Or (Trim(Sheet3.Cells(l, m).Text) = "#N/A")

Application-defined or object-defined error
What is the real problem here?

**royUK** · 12-09-2009, 03:41 AM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!