Solved

Reference Cell in Excel VBA

Posted on 2014-09-23
22
559 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
ID: 40338758
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
ID: 40338760
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
ID: 40338763
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
ScreenConnect 6.0 Free Trial

At ScreenConnect, partner feedback doesn't fall on deaf ears. We collected partner suggestions off of their virtual wish list and transformed them into one game-changing release: ScreenConnect 6.0. Explore all of the extras and enhancements for yourself!

 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 40338764
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
ID: 40338766
You can simply write like this:

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

Open in new window

0
 
LVL 6

Expert Comment

by:johnb25
ID: 40338772
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
ID: 40338778
@ 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
ID: 40338789
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
ID: 40338791
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
ID: 40338793
Dim Workday as String also works....
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 40338797
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
 
LVL 37

Assisted Solution

by:Harisha M G
Harisha M G earned 150 total points
ID: 40338806
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
ID: 40338813
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
ID: 40338816
You don't need the "2" at the end.
0
 
LVL 1

Author Comment

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

Expert Comment

by:johnb25
ID: 40338826
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
ID: 40338827
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
ID: 40338829
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
ID: 40338833
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
ID: 40338851
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
ID: 40338853
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
ID: 40338901
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Finding a closest match in Excel 7 48
Rename multiple files in folder from list in table/excel. 4 37
Dynamic Chart Range 13 37
Msgbox tickler 13 29
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

803 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