How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask John D Your Own Question

John D
John D, Bachelor of Science - Engineering Consultant
Category: Programming
Satisfied Customers: 9658
Experience:  Vast experience in Excel programming and business solutions
53785
Type Your Programming Question Here...
John D is online now
A new question is answered every 9 seconds

I have wrksht 1 as an entry page for an invoice, I have an

Customer Question

I have wrksht 1 as an entry page for an invoice, I have an array formula that copies
relevant cells entered by user to another hidden wrksht used as a database, problem is
i want to have the array copy a protected cell with a formula in it ( the "Total" cell for the invoice) by i get a #Ref! on the database worksheet and a copy of the formula, not the result (total amount) i assume I cant use an array to copy cells with formulas in them, is this true ?
Submitted: 4 years ago.
Category: Programming
Expert:  John D replied 4 years ago.

Hi,

 

What you can do is add a line of code to unprotect the sheet that the macro attempts to change then with another line of code at the end it will re protect the sheet when the macro is done.

 

I can add these lines for you if you can give me the name of the sheet that is protected and the password.

 

 

 

Customer: replied 4 years ago.
by unprotecting my sheet, that will allow the array to copy the sum value and not the
formula as it is doing now??

also, though my sheet is protected, the array has no problem copying the unprotected cells that are empty before my user types something in, it's just that the array is copying the formula from my total cell to my database and i get something like this..
=sum(#ref!,#ref) etc. instead of the value of the cell, (example $ 400.00 ).....
Expert:  John D replied 4 years ago.

Ok, I think I am a little confused here. Maybe I am not understanding the question because the code that you have posted came through gabbled. I suggest you send me the file so I can see the code and the data in the worksheet.

 

 

Customer: replied 4 years ago.
I will send you my file with my sheets unhidden, i hide them because they are all unprotected by necessity. only the office sheet is seen by the user
how do i send you my file
Expert:  John D replied 4 years ago.

Ok to send the file go to www.wikisend.com and upload it there. You will then get a page that has the download link. Copy the download link and come back here and paste it in your reply.

 

 

Customer: replied 4 years ago.
http://wikisend.com/download/467382/yard_invoice.xls
Expert:  John D replied 4 years ago.

Got it, thanks.

 

There are several sheets and several macros in that file. Now that I have the file could you restate your question making reference to the specific cells in question and the sheet names

 

 

 

Customer: replied 4 years ago.
I am concerned only with the Database() and the Data worksheet
Database() is called when the PRINT commandbutton is pressed
the array copies the cells i need from the Office sheet to the datasheet
except for when it trys to copy AQ42 on office to the "M" column on the data
sheet, then I get the #ref! Instead of copying the sum value of that cell it copies the
formula in that cell. I am trying to understand how i can copy the sum of the formula
and not the formula. As you can see it has to happen automatically and at the same time.

Any Ideas?
thanks
Expert:  John D replied 4 years ago.

Ok, you have incorrect formulas in the Data sheet, i.e.formulas in cells that refer to themselves causing circular reference. It is hard to diagnose that workbook with all the protection and the all functionality hidden/disabled. Took me 30 min. just to get to this point.

 

 

 

Customer: replied 4 years ago.
Sorry for the trouble and yes, the release date or column c in the data worksheet is
intentionally set up as a circular reference but is not called in the array in Databae(),but
rather enters todays date when the corresponding "b" column has data put in it by the array.
Expert:  John D replied 4 years ago.

Ok, I am finding that the code has many problems. I can try to fix it for you but considering the time spent on it so far and the time to fix it please make an offer if you want me to proceed further. Just letting you know that so far all assistance has been for free since you have not clicked the accept button yet.

 

 

 

 



Edited by John D on 5/1/2010 at 11:00 PM EST
John D, Bachelor of Science - Engineering Consultant
Category: Programming
Satisfied Customers: 9658
Experience: Vast experience in Excel programming and business solutions
John D and 3 other Programming Specialists are ready to help you
Customer: replied 4 years ago.
Eventually i will have my "print" button also email a copy of the invoice to my boss as well as putting it in the database and saving a pdf copy in a directory as it does now.
if i can ever get my database to record a total for the transaction,

by the way, I don't want the save button to save anything, I want the user to think all he's done is print out an invoice when he hits print, thus all the programing and calling from the
print button.

If you think you can help me while still allowing me to do this than great,
ps. I am doing this for free, if you can believe it .

I can pay you Fifty now and additional if you have any ideas about automatic emailing to
an msn account. I have some ideas and have also surfed extensively as you can tell by some of the code.

If this is acceptable let me know.

thanks
Expert:  John D replied 4 years ago.

Thanks for the accept and the offer.

 

I will first try to fix the database() macro, and you don't have to pay until it has been fixed to your satisfaction.

 

We will then move to the email issue.

 

I will be start rewriting the database macro in a short while. Will keep you posted.

 

 

Customer: replied 4 years ago.
Thanks.
I will be headed home soon, reachable by e-mail from then on...
I check the email at least once or twice a day, I will check more
often till I hear from you
XXX@XXXXXX.XXX, but I'm sure you have that.

Expert:  John D replied 4 years ago.

Ok, here goes.

 

First, the confusion is coming from the way the variables are assigned to the array elements. Then when using the 'Copy' command in the code the the whole 'content' of the cell is copied with the formula. So for cell AQ42 it copies the formula itself , i.e, "=SUM(AQ21,AQ24,AQ27,AQ30,AQ33,AQ36,AQ39)" and places it in the first available row in M of Data sheet. Naturally placing this formula in, say, cell M5, will produce a ref error since there aren't 20 or so rows available above row 5 in that sheet (try copying cell AQ42 manually and pasting it in cell M5 or M6 in Data sheet, you will get the same error)

 

To fix the problem just assign one cell to be equal to the other cell (i.e. using a=b) instead of copying the contents of the cell.

 

Here is that command as used in a simplified version of the code. Just copy the code below and paste it in place it to replace your entire Sub Database() code, and it should work fine.

 

Code:

Sub database()
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim r1 As Range, r2 As Range
Set sh1 = Worksheets("office")
Set sh2 = Worksheets("data")
rw = sh2.Cells(Rows.Count, "b").End(xlUp).Offset(1, 0).Row
sh1.Range("AQ5").Copy sh2.Range("B" & rw)
sh1.Range("K5").Copy sh2.Range("D" & rw)
sh1.Range("O5").Copy sh2.Range("E" & rw)
sh1.Range("K7").Copy sh2.Range("F" & rw)
sh1.Range("L23").Copy sh2.Range("G" & rw)
sh1.Range("C23").Copy sh2.Range("H" & rw)
sh1.Range("C30").Copy sh2.Range("I" & rw)
sh1.Range("G30").Copy sh2.Range("J" & rw)
sh1.Range("P30").Copy sh2.Range("K" & rw)
sh1.Range("L32").Copy sh2.Range("L" & rw)
sh2.Range("M" & rw) = sh1.Range("AQ42")
End Sub

 

 

Hope it is ok. Let me know how it goes.


Customer: replied 4 years ago.

this seems to work fine, will take to work tommorrow just to be sure,

thanks,

Expert:  John D replied 4 years ago.

Sure. Let me know how it goes.

 

 

John D, Bachelor of Science - Engineering Consultant
Category: Programming
Satisfied Customers: 9658
Experience: Vast experience in Excel programming and business solutions
John D and 3 other Programming Specialists are ready to help you
Expert:  John D replied 4 years ago.

Thanks for the accept and bonus :)

 

Now for the automated emailing procedure I will need some details, such as which sheet do you want to send, what is the email address to send it to, and what text do you want to have in the email subject line. Also I am assuming that you have Outlook already set up as your default email program on your system.

 

 

 

Customer: replied 4 years ago.
now it gets complicated, at least for me.
My pdfPrint() macro uses the free program Pdfcreator to copy the INVOICE
worksheet to the tow invoices directory as a pdf file the same time as the
invoice is printed.

I have a macro from the net that will email an excel file automatically,
but i have not tested it and would much rather email the pdf version instead.

the idea is the the user pushes "print" and an invoice is printed, copied to a database,
and then emailed to my bosses computer at home.
All this without the user knowing or having to press any additional
buttons to make it happen.

so far i am two for three
the saving the excel sheet as pdf and to a folder works fine.
the saving to a database works fine thanks to you.

The reason to send the pdf file is for the unchangible nature of it.
the email subject line could be blank or the invoice number.
the email is "XXXXX@XXXXXX.XXX"

the worksheet i would like to send is the INVOICE2, since it has only one copy
of the invoice on it.

thanks,
Expert:  John D replied 4 years ago.

I am not sure if the pdf file can be sent through the sendmail procedure, but here is what I can write the macro to do:

 

When you issue the command the macro will first create a temporary copy of Invoice2, in that same workbook, then it will convert the formulas in all cell into fixed values so the sheet will be totally independent of the other sheets and the recipient won't have to have to worry about any links to other sheets (much like a pdf version). Then it will create a new temp workbook and put the copy of Invoice 2 in it. It will then send that temp workbook (which will have one sheet in it) as an attachment to he specified recipient.Then it will go back to the main workbook and remove the temp Invoice 2 from it.

 

If that is ok let me know so I can write that macro for you.

 

 

Customer: replied 4 years ago.

how about the pdf as an attachment, look at the following code

I can't make it work, maybe you can ???

 

if not let me know

 

Private Sub SendEmail()

Dim olApp As Outlook.Application
Dim myNameSp As Outlook.Namespace
Dim myInbox As Outlook.MAPIFolder
Dim myExplorer As Outlook.Explorer
Dim NewMail As Outlook.MailItem
Dim OutOpen As Boolean

' Check to see if there's an explorer window open
' If not then open up a new one info line only
OutOpen = True
Set myExplorer = olApp.ActiveExplorer
If TypeName(myExplorer) = "Nothing" Then
OutOpen = False
Set myNameSp = olApp.GetNamespace("MAPI")
Set myInbox = myNameSp.GetDefaultFolder(olFolderInbox)
Set myExplorer = myInbox.GetExplorer
End If
myExplorer.Display ' You don't have to show Outlook to use it

' Create a new mail message item. info line only
Set NewMail = olApp.CreateItem(olMailItem)
With NewMail
'.Display ' You don't have to show the e-mail to send it hokey display line only
.Subject = "Look at this!"
.To = "XXXXX@XXXXXX.XXX"
.Body = "new release"
.attachments.add ("c:\documents and settings\compaq_administrator\mydocuments\tow invoices\email\invoice2.pdf")

End With

NewMail.Send
If Not OutOpen Then olApp.Quit

'Release memory.
Set olApp = Nothing
Set myNameSp = Nothing
Set myInbox = Nothing
Set myExplorer = Nothing
Set NewMail = Nothing

End Sub

Expert:  John D replied 4 years ago.

No I do think can make it work. Many errors come up. Besides it is more efficient to write a new code than try to debug somebody else's code.

 

If you want to go with my suggestion I will be happy to comply.

 

 

Customer: replied 4 years ago.

okay then, go ahead

 

thanks

Expert:  John D replied 4 years ago.

Ok, I should have it ready by noon tomorrow (pacific time).

 

Thanks.

 

John D, Bachelor of Science - Engineering Consultant
Category: Programming
Satisfied Customers: 9658
Experience: Vast experience in Excel programming and business solutions
John D and 3 other Programming Specialists are ready to help you
Expert:  John D replied 4 years ago.

Ok, here is the file with the macro called SendInvoice2() which is triggered by pressing Ctrl + Q from any sheet. In this file I removed all other macros so you can clearly focus on this one.

 

FILE

 

Before running the macro please make sure you enter the recipient's email address in this line of code, between the quotes .

Recpt = ""

 

You can also change the text for the Subject line in you like. It is the line that follows

 

The macro does the same tasks that I mentioned before, i.e.

"...When you issue the command the macro will first create a temporary copy of Invoice2, in that same workbook, then it will convert the formulas in all cell into fixed values so the sheet will be totally independent of the other sheets and the recipient won't have to have to worry about any links to other sheets (much like a pdf version). Then it will create a new temp workbook and put the copy of Invoice 2 in it. It will then send that temp workbook (which will have one sheet in it) as an attachment to he specified recipient.Then it will go back to the main workbook and remove the temp Invoice 2 from it.."

 

Hope it is ok. Try it and let me know if it needs any tweaking or if you have any questions.

 

 

Customer: replied 4 years ago.
I install your macro in module4 , i ran it off my pdfprint macro,
i made sure that outlook is the default e-mail program thru control panel / internet options
but when i run the macro, enternet explorer opens, msn live opens the msn e-mail. the e-mail IS addressed correctly to the recipient but has no attachments and is waiting for me to press "Send"

We were trying to email with attachment and then return to the program, so far its not working unless I've done something wrong.
Expert:  John D replied 4 years ago.

That is most likely because you still have msn Windows Live as your default email program.

 

Open IE, go to Tools > Internet Options > Programs tab, then make sure the email is set to Microsoft Office Outlook as shown below.

 

 

graphic

Customer: replied 4 years ago.
for some reason when i changed outlook thru the control panel, it did not work,but
when i went thru the motions you described (outlook was already picked so i didn't need to change anything. it worked go figure.

one last question ,,,
everything works, BUT
is there anything to do to stop the outlook security window from popping up, making me
wait 5 seconds, than making me press "yes" to send my email.
anyway to answer yes automatically

thanks,

Expert:  John D replied 4 years ago.

That is a security alert which is dependent on your Outlook settings and your system security in general. Unfortunately it is not that easy to get rid off this alert if you are using Outlook - you may even need a third party utility to disable it. Here is a good article explaining how to do remove it.

 

http://www.rondebruin.nl/mail/prevent.htm

 

Hope this helps.

 

JustAnswer in the News:

 
 
 
Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.
 
 
 

What Customers are Saying:

 
 
 
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
< Last | Next >
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY
  • Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know. Robin Elkton, Maryland
 
 
 

Meet The Experts:

 
 
 
  • ATLPROG

    Computer Software Engineer

    Satisfied Customers:

    7463
    MS in IT.Several years of programming experience in Java C++ C C# Python VB Javascript HTML
< Last | Next >
  • http://ww2.justanswer.com/uploads/SP/spatlanta2010/2011-6-23_12450_photo.64x64.gif ATLPROG's Avatar

    ATLPROG

    Computer Software Engineer

    Satisfied Customers:

    7463
    MS in IT.Several years of programming experience in Java C++ C C# Python VB Javascript HTML
  • http://ww2.justanswer.com/uploads/ComputersGuru/2010-02-13_051118_Photo41.JPG LogicPro's Avatar

    LogicPro

    Computer Software Engineer

    Satisfied Customers:

    5603
    Expert in C, C++, Java, DOT NET, Python, HTML, Javascript, Design.
  • http://ww2.justanswer.com/uploads/unvadim/2010-11-15_210218_avatar.jpg unvadim's Avatar

    unvadim

    Computer Software Engineer

    Satisfied Customers:

    1158
    Good knowledge of OOP principles. 3+ years of programming experience with Java and C++. Sun Certified Java Programmer 5.0.
  • http://ww2.justanswer.com/uploads/lifesaver333/2010-10-17_191349_ls.jpeg lifesaver's Avatar

    lifesaver

    Computer Software Engineer

    Satisfied Customers:

    950
    Several years of intensive programming and application development experience in various platforms.
  • http://ww2.justanswer.com/uploads/EH/ehabtutor/2012-8-2_202016_1.64x64.jpg ehabtutor's Avatar

    ehabtutor

    Computer Software Engineer

    Satisfied Customers:

    864
    Bachelor of computer science, 5+ years experience in software development, software company owner
  • http://ww2.justanswer.com/uploads/RA/rajivsharma086/2012-6-6_17128_displaypic.64x64.jpg Raj's Avatar

    Raj

    Computer Engg.

    Satisfied Customers:

    860
    BE CS, 4+ Experience in Programming and Database (ERP)
  • http://ww2.justanswer.com/uploads/eljonis/2010-01-06_130406_eljon2.jpg Eljon's Avatar

    Eljon

    Consultant

    Satisfied Customers:

    590
    11 yrs of programming (PHP, WordPress, XSL, SQL, JavaScript)