Solved

Reference Cell in Excel VBA

Posted on 2014-09-23
22
524 Views
Last Modified: 2014-09-23
I'm trying to reference the date value in a specific cell on my worksheet to use in my VBA code. But I can't seem to get it to work. Here is what I'm using to pull the data from that cell:

    Dim WorkDay As String

    Set WorkDay = Sheets("Sheet1").Range("G1")

Open in new window


Then I want to use that date value in a line of code that sends an email from excel vba. I want to put that value in the textbody portion of the email. Here is the line of code for that:

.TextBody = "As Of " & WorkDay

Open in new window



What am I doing wrong? It's not pulling the value. I'm getting a compile error "Object Required".
0
Comment
Question by:Lawrence Salvucci
  • 7
  • 6
  • 6
  • +1
22 Comments
 
LVL 6

Expert Comment

by:johnb25
Comment Utility
Try this; you need to pick up the Value property.
Also, you don't need the Set command for the Variable.

 
  WorkDay = Sheets("Sheet1").Range("G1").Value

Open in new window


john
0
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
Try this instead:

 Dim WorkDay As String

    WorkDay = Sheets("Sheet1").Range("G1").Value

Debug.Print "As Of " & WorkDay

Open in new window

0
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
The reason for the problem is you said:

Dim WorkDay As String

So it is expecting a String.

Then you say:

Set WorkDay = Sheets("Sheet1").Range("G1")

What is the result of this - a Range! So it is conflicted between expecting a String and getting a Range.
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
Comment Utility
Thanks John. Now I'm getting another error when I switch the code to your revised code. I'm getting "The specified dimension is not valid for the current chart type". Which has nothing to do with my email code or referencing that cell. When I click the "Debug" button it goes to that new line of code you changed for me. How and why would I get that message now?
0
 
LVL 37

Expert Comment

by:Harisha M G
Comment Utility
You can simply write like this:

WorkDay = [Sheet1!G1]
.TextBody = "As Of " & WorkDay

Open in new window

0
 
LVL 6

Expert Comment

by:johnb25
Comment Utility
Sorry, I did not mean the  Dim WorkDay As String line to be left out; that should still be there.
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
Comment Utility
@ mgh

I'm getting a "Type Mismatch" error now.

@ John

I had the Dim WorkDay as String line still in there when I got that error message.
0
 
LVL 37

Expert Comment

by:Harisha M G
Comment Utility
Try with this:

WorkDay = [Sheet1!G1].Value2
.TextBody = "As Of " & WorkDay

Open in new window


By the way, why do you have WorkDay defined? Do you have Option Explicit at the top?
0
 
LVL 6

Assisted Solution

by:johnb25
johnb25 earned 200 total points
Comment Utility
This works for me...can you try this on its own to eliminate anything else:

 Sub test()
  Dim Workday As Date
  
  Workday = Sheets("Sheet1").Range("G1").Value
  Debug.Print Workday
    
  End Sub

Open in new window

0
 
LVL 6

Expert Comment

by:johnb25
Comment Utility
Dim Workday as String also works....
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
Comment Utility
Ok it works when I test it using the subTest(). But it still won't work when I add it to my email code. Here's the entire email code function. Maybe you can see what is causing it to error out.

Sub CDO_Mail_Workbook()
    Dim WB As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileExtStr As String
    Dim iMsg As Object
    Dim iConf As Object
    Dim Flds As Variant
    Dim Workday As Date
  
    Workday = Sheets("Sheet1").Range("G1").Value
    Set WB = ActiveWorkbook
    Set WB = Workbooks("BC Region Sales.xlsx")

    'This code pulls all the email address from the hidden sheet SHEET1 and loads them into the TO line of code
    Dim cell As Range
    Dim strto As String
    On Error Resume Next
    For Each cell In ThisWorkbook.Sheets("Sheet1") _
        .Range("A1:A4").Cells.SpecialCells(xlCellTypeConstants)
        If cell.Value Like "?*@?*.?*" Then
            strto = strto & cell.Value & ";"
        End If
    Next cell
    On Error GoTo 0
    If Len(strto) > 0 Then strto = Left(strto, Len(strto) - 1)
    
    If Val(Application.Version) >= 12 Then
        If WB.FileFormat = 51 And WB.HasVBProject = True Then
            MsgBox "There is VBA code in this xlsx file, there will be no VBA code in the file you send." & vbNewLine & _
                   "Save the file first as xlsm and then try the macro again.", vbInformation
            Exit Sub
        End If
    End If

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    'Make a copy of the file/Mail it/Delete it
    'If you want to change the file name then change only TempFileName
    TempFilePath = Environ$("temp") & "\"
    'TempFileName = "Copy of " & wb.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")
    TempFileName = "BC Region Sales"
    FileExtStr = "." & LCase(Right(WB.Name, Len(WB.Name) - InStrRev(WB.Name, ".", , 1)))

    WB.SaveCopyAs TempFilePath & TempFileName & FileExtStr

    Set iMsg = CreateObject("CDO.Message")
    Set iConf = CreateObject("CDO.Configuration")

    iConf.Load -1    ' CDO Source Defaults
    Set Flds = iConf.Fields
    With Flds
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "larry@xyzcompany.net"
        .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "password"
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.mail.net"
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 587
        .Update
    End With

    With iMsg
        Set .Configuration = iConf
        .To = "larry@xyzcompany.net"
        .CC = ""
        .BCC = ""
        .From = """Reports"" <Reports@xyzcompany.com>"
        .Subject = "Daily BC Region Sales"
        .TextBody = "As Of " & Workday
        .AddAttachment TempFilePath & TempFileName & FileExtStr
        .Send
    End With

    'If you do not want to delete the file you send delete this line
    Kill TempFilePath & TempFileName & FileExtStr

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

Open in new window

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 37

Assisted Solution

by:Harisha M G
Harisha M G earned 150 total points
Comment Utility
Here you are trying to append a string and a date, which won't work:

.TextBody = "As Of " & Workday

So, change WorkDay to String as you had earlier (or remove altogether), and fetch the value using "Value" or "Value2"

Workday = [Sheet1!G1].Value2
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
Comment Utility
Ok I changed it to a string and then to the new code. Now I'm getting "Object Required" and it highlights this line of code:

Workday = [Sheet1!G1].Value2

Open in new window

0
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
You don't need the "2" at the end.
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
Comment Utility
Still getting the same error even without the 2 at the end.
0
 
LVL 6

Expert Comment

by:johnb25
Comment Utility
The date does not seem to be the issue:

?iMsg.textbody
As Of 12/05/2014

I can run it through to AddAttachment, and I get an automation error.
Probably due to my set-up, e.g. not having a library installed.

How far does your code run?
Test the variables up to that point by typing ?VariableName in the Immediate window

John
0
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 150 total points
Comment Utility
You are now using the wrong format; that's an Excel format, not an Excel VBA format.

Lines 9-11 should read:

    Dim Workday As String
  
    Workday = Sheets("Sheet1").Range("G1").Value

Open in new window

0
 
LVL 1

Author Comment

by:Lawrence Salvucci
Comment Utility
The code stops at that WorkDay line of code. When I remove that line and put the code back to the original way the email function works fine. It always worked fine until I added that line of code.
0
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
Are you saying that it stops in line 11? Is there a spreadsheet called Sheet1 in the Active Workbook?

Maybe you should post the entire spreadsheet.
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
Comment Utility
Yes the code stops at line 11. There is a sheet called Sheet1. That's where it also pulls the email addresses for that email code as well. The spreadsheet is rather large and has a lot of links that would be difficult to break and show as a sample. That's why I just posted the entire email code. I just took the WorkDay code out and it works like a charm. I just don't get why it won't work by just adding that line of code.
0
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
I just took the WorkDay code out

Can you please give line numbers. It's difficult to follow you with "that line of code".

Maybe line 11 should be expanded to:

Workday = ActiveWorkbook.Sheets("Sheet1").Range("G1").Value

Open in new window


or, if it is a different workbook

Workday = Workbooks("NAME OF WORKBOOK").Sheets("Sheet1").Range("G1").Value

Open in new window

0
 
LVL 6

Expert Comment

by:johnb25
Comment Utility
Hi,

Workday is also the name of an Excel formula; can you change the variable name to something else and then try it.
Workday worked ok for me, but there is obviously something different in your setup.

John
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now