type mismatch 13

LMjdm

New Member
Joined
Oct 20, 2014
Messages
38
Hey Guys,

Im having a slight issue with a type mismatch

Code:
Do While placeHolder <> 0
                
                    placeHolder = placeHolder - 1
                    
                    [COLOR=#ff0000]Set CAMX = Sheets("Distribution").Range("D" + contactCounter)[/COLOR] 'declared as a variant.. 'if there is a better type please let me know
                    
                    contactCounter = countactCounter + 1
                    
                    Application.ScreenUpdating = False
                
                    If camWelcomeMessage = 0 Then
                        message = "CAMs, the following is a reminder for the upcoming business rhythm:"
                        Set lync = Messenger.InstantMessage(CAMX)
                        lync.SendText (message)
                    End If

                    message = dayPlan.Offset(0, 2)
                    Set lync = Messenger.InstantMessage(CAMX)
                    lync.SendText (message)
                
                    If dayPlan.Offset(0, 3) = 0 Then
                        message = "As of: Today, " & asOf
                    ElseIf dayPlan.Offset(0, 3) = -1 Then
                        message = "As of: Yesterday, " & asOf
                    Else
                        message = "As of: " & asOf
                    End If
                
                    lync.SendText (message)
                
                    If dayPlan.Offset(0, 4) = 1 Then
                        message = "Due: Close of business, " & dueDay
                    ElseIf dayPlan.Offset(0, 4) = 2 Then
                        message = "Due: Tomorrow, " & dueDay
                    Else
                        message = "Due: " & dueDay
                    End If
                
                lync.SendText (message)
                
                On Error Resume Next
                lync.Close
                Application.ScreenUpdating = True
                
              Loop

Basically what the code is doing is referring to a contact distribution list(emails), and everytime it uses one it wants to go to the one under it, then use that one . It will repeat this process as many times as there are contacts using a separate counter(within the distribution sheet, it basically just counts how many emails are in the list and then feeds it into the code).

I was wondering if the syntax for the statement in red was incorrect. I can include more code if necessary however I wanted to keep it to the point.

Many thanks,

LMjdm
 
@alphafrog bingo! that did the trick. I removed the set function since it is a variant. That wont affect anything will it? Could I set CAMX as a range if the it is suppose to hold an email address as a value?

Use the Set command to define the range object.

Don't use the set command (or declare it as type Range) to define the value.
 
Upvote 0
Sorry I didnt say that clear enough. But would it be the best option to declare CAMX as a range instead of a variant. All these variables sometime get me confused at their purpose.

Also, im not sure what is exactly mean by range object, isnt a range and an object two different variables? Im not exactly sure what the set command actually does I guess.

Forgive my inexperience I've been self teaching myself step-by-step so all the help you guys give is amazing. So thankful!!
 
Upvote 0
If you want the value in the cell you should be specific:

Rich (BB code):
CAMX = Sheets("Distribution").Range("D" & contactCounter).Value
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top