Craig P.
asked on
Issue with SaveUpdateContact Macro
Hello Experts, I have a workbook of VBA codes that I am trying to update to meet my needs. The VBA main purpose is to work within a Shared Workbook without "Sharing" where the workbook is mapped to a main workbook database. In my revised workbook, I have added additional columns and updated the code to the below. My issue is regarding updating or creating a new entry and then saving. I get a "Run time error 1004: Application-defined or object defined error."
The error is on the For ContCol line. I'm also using database mapping columns that was provided as well but I added on to the original.
I understand if additional info is required as I am trying to keep this as simple as I can. I appreciate any support. Thanks
The error is on the For ContCol line. I'm also using database mapping columns that was provided as well but I added on to the original.
I understand if additional info is required as I am trying to keep this as simple as I can. I appreciate any support. Thanks
Sub SaveUpdateContact()
With Sheet1
If .Range("F5").Value = Empty Then
MsgBox "Please enter a Contact Name"
Exit Sub
End If
SyncFromDatabase 'Check for database updates before Save Or Update
.Calculate 'Calculate on update to make sure Contact Db Row and Contact ID formulas are calculated
If .Range("B10").Value = True Then 'New Contact
ContDbRow = Sheet2.Range("A99999").End(xlUp).Row + 1
Sheet2.Range("A" & ContDbRow).Value = .Range("B8").Value 'Add New Contact ID
Else: 'Existing Contact
If .Range("B9").Value = Empty Then Exit Sub
StopCalc
ContDbRow = .Range("B9").Value 'Contact Database Row
End If
For ContRow = 5 To 9 Step 2
For ContCol = 6 To 25 Step 19
Sheet2.Cells(ContDbRow, .Cells(ContRow, ContCol + 25).Value).Value = .Cells(ContRow, ContCol).Value
Next ContCol
Next ContRow
.Range("B10").Value = False 'Set New Contact To False
.Range("B12").Value = Now 'Update Last Local Change
.Shapes("ExistContGrp").Visible = msoCTrue
.Shapes("NewContGrp").Visible = msoFalse
End With
SyncToDatabase
RefreshContactTable
ResetCalc
ContactSavedMess
End Sub
I am nearly certain that simply inserting < Dim ContDbRow as Long > will help you find the root cause of your problem.
You should always declare your variables (which is why Martin suggested Option Explicit.)
I also agree with Martin that line 22 is probably throwing the error.
But I see other problems.
It is clear that you expect CountDBRow to be a number but your code causes it to be a variant which can contain garbage values like "A" or " ".
Line 15 will not exit and the garbage value and line 16 will put the garbage into ContDbRow which could cause line 22 to throw an error.
15 If .Range("B9").Value = Empty Then Exit Sub ' <== I usually avoid Empty because it is a "Tricky" coding technique. it is equivalent to saying If cell = 0 Or cell = "" Then Exit Sub
16 ContDbRow = .Range("B9").Value
....
22 Sheet2.Cells(ContDbRow, .Cells(ContRow, ContCol + 25).Value).Value = .Cells(ContRow, ContCol).Value
Nonetheless, that garbage would cause line 22 to throw "Run time error 13: Type Mismatch"
But you said you got a "Run time error 1004" which probably means ContDbRow contains Empty.
I have looked at your code carefully, and cannot understand how line 22 could throw a 1004 so I think your EE post isn't showing the whole picture.
So, before you change ContDbRow from a Variant to a Long, it would be instructive to put the following code between line 21 and line 22.
msgbox typename(ContDbRow) & "=" & CountDbRow & "." ' <=== I predict this will show the typename is Empty.
Separate subject:
Be careful with Empty because it is both a "0" and a "". Google for "vba Empty Keyword" for more details.
For instance
If 0 = Empty Then MsgBox "Surprise."
If "" = Empty Then MsgBox "Surprise."
You should always declare your variables (which is why Martin suggested Option Explicit.)
I also agree with Martin that line 22 is probably throwing the error.
But I see other problems.
It is clear that you expect CountDBRow to be a number but your code causes it to be a variant which can contain garbage values like "A" or " ".
Line 15 will not exit and the garbage value and line 16 will put the garbage into ContDbRow which could cause line 22 to throw an error.
15 If .Range("B9").Value = Empty Then Exit Sub ' <== I usually avoid Empty because it is a "Tricky" coding technique. it is equivalent to saying If cell = 0 Or cell = "" Then Exit Sub
16 ContDbRow = .Range("B9").Value
....
22 Sheet2.Cells(ContDbRow, .Cells(ContRow, ContCol + 25).Value).Value = .Cells(ContRow, ContCol).Value
Nonetheless, that garbage would cause line 22 to throw "Run time error 13: Type Mismatch"
But you said you got a "Run time error 1004" which probably means ContDbRow contains Empty.
I have looked at your code carefully, and cannot understand how line 22 could throw a 1004 so I think your EE post isn't showing the whole picture.
So, before you change ContDbRow from a Variant to a Long, it would be instructive to put the following code between line 21 and line 22.
msgbox typename(ContDbRow) & "=" & CountDbRow & "." ' <=== I predict this will show the typename is Empty.
Separate subject:
Be careful with Empty because it is both a "0" and a "". Google for "vba Empty Keyword" for more details.
For instance
If 0 = Empty Then MsgBox "Surprise."
If "" = Empty Then MsgBox "Surprise."
ASKER
thank you, I did initially declare option explicit, I just failed to include it in my initial post. Indeed, there may be more to my original post but my request for help has turned into a bigger project. I will certainly review your suggestions and advise. Thank you for the suggestions as always.
You have still failed to tell us the typename of ContDbRow.
Option explicit forces you define the variables, but you can still say Dim ConDbRow, anotherVar, another
I am almost sure that is what you did.
Option explicit forces you define the variables, but you can still say Dim ConDbRow, anotherVar, another
I am almost sure that is what you did.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
You should also always use Option Explicit and define all your variables.