Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old October 25th 05, 07:28 AM
Paul
 
Posts: n/a
Default Visual Basic macro run time error(13) type mismatch

Hi,
I am having trouble to run the following VB macro. It basiclly fills one row
of sheet with data extracted from Lotus Notes. Looks like VB can only make
around 10 assignment statements. I have tried to comment out assignment
statements at various places, the run time error always happened at around 9
or 10th statements.

I don't know Excel and VB very much though. I inherited this application
from someone I don't know. And this application runs fine on my old windows
2000 machine. But it failed on my newly upgrate windows XP pro machine.

Anyone know what's going on here?

Thanks.

Paul


'---------------------------------------------------------------------------------------------
Option Explicit

Const ID_COLUMN As Integer = 1
Const UNIQUE_ID_COLUMN As Integer = 2
Const NAME_COLUMN As Integer = 3
Const SCF_COLUMN As Integer = 4
Const OWNER_COLUMN As Integer = 5
Const CONNECTED_COLUMN As Integer = 6
Const LEAD_COLUMN As Integer = 7
Const BRANCHWK_COLUMN As Integer = 8
Const TEST_REQUIRED_COLUMN As Integer = 9
Const TEST_PERFORMED_COLUMN As Integer = 10
Const TEST_ENGINEER_COLUMN As Integer = 11
Const PRIORITY_COLUMN As Integer = 12
Const STATUS_COLUMN As Integer = 13
Const RELNAME_COLUMN As Integer = 14
Const RELTIME_COLUMN As Integer = 15
Const LATEST_COLUMN As Integer = 16

Private Declare Function CoRegisterMessageFilter Lib "OLE32.DLL" _
(ByVal lFilterIn As Long, ByRef lPreviousFilter) As Long
'--------------------------------------------------------------------------------------------
Public Sub GetValues(doc As Object, TargetExcelSheet As Worksheet, ByVal
counter As Integer)
Dim var1, var2 As Variant
Dim Anch As Range, addr As String


With TargetExcelSheet

.Cells(counter, ID_COLUMN).Value = doc.ID
.Cells(counter, UNIQUE_ID_COLUMN).Value = doc.universalID
.Cells(counter, NAME_COLUMN).Value = doc.Name
.Cells(counter, LEAD_COLUMN).Value = doc.FELProduct
.Cells(counter, OWNER_COLUMN).Value = doc.RespNames_tx
.Cells(counter, CONNECTED_COLUMN).Value = doc.connectedid
.Cells(counter, STATUS_COLUMN).Value = doc.FEStatus
.Cells(counter, BRANCHWK_COLUMN).Value = doc.BranchWK
.Cells(counter, LATEST_COLUMN).Value = doc.LatestComment

.Cells(counter, TEST_REQUIRED_COLUMN).Value = doc.Required
.Cells(counter, TEST_PERFORMED_COLUMN).Value = doc.Performed
.Cells(counter, TEST_ENGINEER_COLUMN).Value = doc.LC2te
.Cells(counter, TEST_ENGINEER_COLUMN) = GetNormalName(.Cells(counter,
TEST_ENGINEER_COLUMN))

' Form SCF+team
var1 = doc.FECFactory
var2 = doc.FETeam
If IsEmpty(var2) Then
.Cells(counter, SCF_COLUMN).Value = var1(0)
Else
.Cells(counter, SCF_COLUMN).Value = var1(0) & " " & var2(0)
End If

.Cells(counter, PRIORITY_COLUMN).Value = doc.FePriority
.Cells(counter, RELNAME_COLUMN).Value = doc.ReleasedName
If Not IsEmpty((.Cells(counter, RELNAME_COLUMN))) Then
.Cells(counter, RELTIME_COLUMN).Value = Right(.Cells(counter,
RELNAME_COLUMN), Len("yyyy_wkww"))
Else
.Cells(counter, RELTIME_COLUMN).Value = "No BREL"
End If

' create hyperlink to Notes - note! Requires Notes registry fix

Set Anch = .Cells(counter, ID_COLUMN)
addr = "notes://" & Range("server").Value & "/" & Range("path").Value &
"/0/" & .Cells(counter, UNIQUE_ID_COLUMN)
'txt = .Cells(counter, ID_COLUMN).Value

' this creates a clickable link into the cell defined, clicking the link
will open the doc in Notes
ActiveSheet.Hyperlinks.Add anchor:=Anch, Address:=addr

End With



End Sub



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Type mismatch on LastRow daniel chen Excel Discussion (Misc queries) 8 April 11th 05 02:29 AM
Subscript & Superscript Karen Excel Discussion (Misc queries) 6 February 22nd 05 11:16 PM
Visual Basic Error Message Scubasocks Excel Discussion (Misc queries) 0 January 28th 05 04:29 PM
Convert data type of cells to Text,Number,Date and Time Kevin Excel Worksheet Functions 1 December 31st 04 01:57 PM
Date and Time Macro m.j.anderson Excel Discussion (Misc queries) 1 December 1st 04 01:35 AM


All times are GMT +1. The time now is 01:02 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017