Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-31-2018, 03:25 PM
Khwaja Khwaja is offline Entering Formula in VB Windows 10 Entering Formula in VB Office 2016
Novice
Entering Formula in VB
 
Join Date: Aug 2018
Posts: 12
Khwaja is on a distinguished road
Default Entering Formula in VB

I am trying to use the following formula as part of the code, but i keep getting error 1004. Application defined or object defined eror.

Range("K3").Formula = "=IF(H2="","",IF(AND(H2>TIMEVALUE(""5:00""),E2<TIM EVALUE(""9:00 AM"")),""10:00 AM"", IF(AND(H2>TIMEVALUE(""5:00""),E2>TIMEVALUE(""9:00 AM"")),""12:00 PM"","")))"



Will appreciate some help.
Reply With Quote
  #2  
Old 08-31-2018, 09:38 PM
NoSparks NoSparks is offline Entering Formula in VB Windows 7 64bit Entering Formula in VB Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

Try
Code:
Range("K3").Formula = "=IF(H2="""","""",IF(AND(H2>TIMEVALUE(""5:00""),E2<TIMEVALUE(""9:00 AM"")),""10:00 AM"", IF(AND(H2>TIMEVALUE(""5:00""),E2>TIMEVALUE(""9:00 AM"")),""12:00 PM"","""")))"
Reply With Quote
  #3  
Old 09-01-2018, 12:19 AM
Khwaja Khwaja is offline Entering Formula in VB Windows 10 Entering Formula in VB Office 2016
Novice
Entering Formula in VB
 
Join Date: Aug 2018
Posts: 12
Khwaja is on a distinguished road
Default

Thank you so much for your help.
Reply With Quote
  #4  
Old 09-01-2018, 12:34 AM
Khwaja Khwaja is offline Entering Formula in VB Windows 10 Entering Formula in VB Office 2016
Novice
Entering Formula in VB
 
Join Date: Aug 2018
Posts: 12
Khwaja is on a distinguished road
Default

A little question. How do I reflect a cell value as part of the file name when I am saving it?

MyFileName = CurrentWB.Path & "\" & "Queen St Roster - " & Format(Now(), "dd-mm-yyyy") & ".csv"

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=MyFileName, FileFormat:=xlCSV, CreateBackup:=False, Local:=True
ActiveWorkbook.Close SaveChanges:=False
Application.DisplayAlerts = True

MsgBox "CSV file has been created "
Reply With Quote
  #5  
Old 09-01-2018, 03:26 PM
p45cal's Avatar
p45cal p45cal is offline Entering Formula in VB Windows 10 Entering Formula in VB Office 2016
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

The Filename part is in cell A1 (for example) and that cell contain the likes of: Queen St Roster - and that cell is on the active sheet:

MyFileName = CurrentWB.Path & "\" & Range("A1").value & Format(Now(), "dd-mm-yyyy") & ".csv"

If that cell is on a sheet called, say, Sheet33, then:
MyFileName = CurrentWB.Path & "\" & Sheets("Sheet33").Range("A1").value & Format(Now(), "dd-mm-yyyy") & ".csv"
Reply With Quote
  #6  
Old 09-01-2018, 03:43 PM
Khwaja Khwaja is offline Entering Formula in VB Windows 10 Entering Formula in VB Office 2016
Novice
Entering Formula in VB
 
Join Date: Aug 2018
Posts: 12
Khwaja is on a distinguished road
Default

So very grateful. Lastly, how do I make the file to save the file in a sub directory of the folder where it is saving right now. Let us call it QBOUploads.
Reply With Quote
  #7  
Old 09-02-2018, 01:27 AM
p45cal's Avatar
p45cal p45cal is offline Entering Formula in VB Windows 10 Entering Formula in VB Office 2016
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

MyFileName = CurrentWB.Path & "\QBOUploads\" & Range("A1").value & Format(Now(), "dd-mm-yyyy") & ".csv"

If that cell is on a sheet called, say, Sheet33, then:
MyFileName = CurrentWB.Path & "\QBOUploads\" & Sheets("Sheet33").Range("A1").value & Format(Now(), "dd-mm-yyyy") & ".csv"
Reply With Quote
  #8  
Old 09-02-2018, 06:19 PM
Khwaja Khwaja is offline Entering Formula in VB Windows 10 Entering Formula in VB Office 2016
Novice
Entering Formula in VB
 
Join Date: Aug 2018
Posts: 12
Khwaja is on a distinguished road
Default

Thanks a lot. I am using the following code in the light of changes you suggested. But I am getting runtime error 9. Subscript out of range.

MyFileName = CurrentWB.Path & "\QBOUploads\" & Sheets("Analysis").Range("I1").Value & Format(Now(), "dd-mm-yyyy") & ".csv"
Reply With Quote
  #9  
Old 09-02-2018, 09:00 PM
Khwaja Khwaja is offline Entering Formula in VB Windows 10 Entering Formula in VB Office 2016
Novice
Entering Formula in VB
 
Join Date: Aug 2018
Posts: 12
Khwaja is on a distinguished road
Default

Apparently, following is the issue as otherwise file is saving fine in the sub directory. I checked the sheet name and it seems OK.


Sheets("Analysis").Range("I1").Value
Reply With Quote
  #10  
Old 09-03-2018, 12:13 AM
p45cal's Avatar
p45cal p45cal is offline Entering Formula in VB Windows 10 Entering Formula in VB Office 2016
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Put a break point (or a Stop instruction) directly after this line (or its equivalent):
MyFileName = CurrentWB.Path & "\QBOUploads\" & Sheets("Analysis").Range("I1").Value & Format(Now(), "dd-mm-yyyy") & ".csv"
Then when the code reaches that point and stops, type the following in the Immediate pane and press Enter:
?MyFileName
Do you get a correct file name and path?
Reply With Quote
  #11  
Old 09-04-2018, 01:52 PM
trevorc trevorc is offline Entering Formula in VB Windows 7 32bit Entering Formula in VB Office 2013
Competent Performer
 
Join Date: Jan 2017
Posts: 174
trevorc will become famous soon enoughtrevorc will become famous soon enough
Default

Could it be the format of Now(), does this represent the time and date?, try using date() is stead.
Reply With Quote
  #12  
Old 09-05-2018, 10:46 PM
Khwaja Khwaja is offline Entering Formula in VB Windows 10 Entering Formula in VB Office 2016
Novice
Entering Formula in VB
 
Join Date: Aug 2018
Posts: 12
Khwaja is on a distinguished road
Default

Thanks. I tried checking the file name and nothing happens after I added ?MyFileName in the immediate window. Changing to date format did not work either. Same error.
Reply With Quote
  #13  
Old 09-06-2018, 05:07 AM
p45cal's Avatar
p45cal p45cal is offline Entering Formula in VB Windows 10 Entering Formula in VB Office 2016
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Quote:
Originally Posted by Khwaja View Post
I tried checking the file name and nothing happens after I added ?MyFileName in the immediate window.
Confirm the following:

1. you "Put a break point (or a Stop instruction) directly after this line (or its equivalent)"
2. That you left the yellow highlighting as it was (didn't stop the debugger).
3. That after 'adding' ?MyFileName you pressed Enter on the keyboard.

If you still get nothing, there is a problem with assigning a value to MyFileName.


Another way to check for a realistic filename is to forget all of the above and add a code line just after the line:
MyFileName = CurrentWB.Path & "\QBOUploads\" & Sheets("Analysis").Range("I1").Value & Format(Now(), "dd-mm-yyyy") & ".csv"

being:
MsgBox MyFileName

This will cause a message box to appear which will allow you to check the filename. Clicking the OK button on the message box will allow the code to continue.
Reply With Quote
  #14  
Old 09-07-2018, 06:14 PM
Khwaja Khwaja is offline Entering Formula in VB Windows 10 Entering Formula in VB Office 2016
Novice
Entering Formula in VB
 
Join Date: Aug 2018
Posts: 12
Khwaja is on a distinguished road
Default

I apologise if I am not doing what I need to. Tried but still no luck as you can see from the screen shot. Looks like that the 'Path' is somehow reading a different path name than the one I identified. If I use the commented out line above, file is saved correctly. may be some issue with Path.
Attached Files
File Type: pdf error.pdf (345.2 KB, 9 views)
Reply With Quote
  #15  
Old 09-08-2018, 04:33 AM
p45cal's Avatar
p45cal p45cal is offline Entering Formula in VB Windows 10 Entering Formula in VB Office 2016
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

I said to put the break point AFTER the line, not on it.
You could leave it there and press F8 on the keyboard once to execute that line, then ?MyFileName and Enter in the Immediate pane.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Entering Formula in VB Entering a column value in a cell, to be used as part of a reference in a formula in another cell paulkaye Excel 4 02-26-2017 04:18 AM
entering name in outlook does not autofill Franksplace2 Word 0 03-08-2015 04:10 PM
Entering Formula in VB entering duration in months ketanco Project 1 12-17-2014 01:09 PM
Entering Formula in VB entering lump sum costs ketanco Project 1 12-24-2011 12:56 PM
Entering Contact details GemmaD Outlook 0 12-18-2008 06:41 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:24 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft