#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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"","""")))" |
#3
|
|||
|
|||
Thank you so much for your help.
|
#4
|
|||
|
|||
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 " |
#5
|
||||
|
||||
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" |
#6
|
|||
|
|||
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.
|
#7
|
||||
|
||||
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" |
#8
|
|||
|
|||
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" |
#9
|
|||
|
|||
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 |
#10
|
||||
|
||||
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? |
#11
|
|||
|
|||
Could it be the format of Now(), does this represent the time and date?, try using date() is stead.
|
#12
|
|||
|
|||
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.
|
#13
|
||||
|
||||
Quote:
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. |
#14
|
|||
|
|||
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.
|
#15
|
||||
|
||||
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. |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
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 duration in months | ketanco | Project | 1 | 12-17-2014 01:09 PM |
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 |