Is it possible to create an IF argument based on the filename?
Example - A2=IF(Filename="template",A1,Filename)
Reason - A1 is the result of a MAX function on the template WB but once template is saved as a different file name I want A2 to = the new filename?
I have below code apearing couple times in my macro and I'd like to make a variable.
Plese see belowe
Workbooks.OpenText Filename:= _
"S:ReportsMy ReportsGuidefilename20080430", Origin:=xlMSDOS, _ ' filename should be another variable as it referes to different files
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=True, _
Space:=False, Other:=False, FieldInfo:= Array(Array(1, 1), Array(2, 1), Array( _
3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10 _
, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), _
Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), Array( _
23, 1), Array(24, 1), Array(25, 1), Array(26, 1), Array(27, 1), Array(28, 1)), _
TrailingMinusNumbers:=True
In my current worksheet, IF(0 = "CS") returns TRUE, but on any other file this same argument returns FALSE. The cells' formats are both general. I want it to be FALSE.
View 2 Replies View RelatedI have a requirement to download a lot of historical data files from the archieve of an website. The url goes something like this: [URL]....
This downloads file for 21022014 ie. 21-Feb-2014. I need to be able to have a facility to have a selection criteria on my user form (in excel) where I specify a date range and the macro automatically downloads all the valid excel files available within that date range (files for Saturday, Sunday and some holiday dates will not be available in the website archive database) one after the other (like at single clcik of button) into a specific location (predefined viz.. c:/Users/EOD files/) on my laptop. Best would be if I am able to select the save location run time by using a 'Browse' like feature where I go and choose my local laptop folder...same type when we try uploading a file from our laptop to the web..
I have attached the file I am working on. I am attempting to create a link to an external file based on the value of cells in column A. Then I would like to simply copy the formula down, lets say in Column B, the rows and as I do the external file reference will change depending on the value within the cell in Column A. I hope that I am making sense.
I am using Windows Vista with Excel 2007. The files will all be within the same file folder, however, there are hundereds of files so I won't be able to open them all for the indirect to work.
I have a Workbook that already has a macro in it that will generate multiple sheets based on certain criteria.
For each of these newly generated sheets (numbered 1-6 in the attached example), I need to be able save each of them to a PDF file based on a unique name contained in a certain cell (in this case, each named is referenced in cell Q1 of each sheet). As such, I should end up with 6 pdfs based on the attached sample files.
The PDFs would ideally need to be landscaped and should be just 1 page per sheet.
In my attached sample workbook, you'll notice that I have a "PracticePrint" macro that doesn't quite get the job done. One other criteria involves not printing any sheet with the characters "1010" in it (my PracticePrint macro is setup to do that, but it fails to do much else).
I need to open an excel file based on a date mentioned in the file name, but minus 1 day. The file name is composed with Statistics_date_time.xls
Example: statistics_20140423_142754.xls
Once I need to open the file (only in the mornings), it has to be the one from the previous day to see the yesterdays performance. How can I create a macro that will look at the date in the file and take the one from yesterday?
This will be a part of a macro I'm making. The rest of the macro will just format the file and combine a few ones from different departments.
im having trouble with an IF argument as you can see from example the data sheet is where i need the result,the first formula i have done in B24 but cant understand why the 2nd argument will not go in and i keep getting a value error as shown in C24.
i need say If in the sunday tab G8="A1"then insert G32.Then do it for "A2"=G32 and so on.all in Data B24
Is there a shorter way as i have to go up to A9 B9 C9 D9.
I have a sheet that has 2 fields that i need a yes/no answer from. Basically if b = "P1" then a should be less than or equal to 4 to reply good otherwise it should reply no. but if b isnt "P1" but instead is P2 P3 or P4 then it should => 10, 30, 40 respectively.
View 8 Replies View Relatedhow to create a function argument containing my own defined formula?
View 9 Replies View Relateddoes IF in excel formula take 27 Arg and ( AND, OR) in IF ststement take the same Arg
and does SumProduct Take the same Arg .
The second subroutine below selects a range of cells, in this case a column. It selects a range that ends with the last cell that has data and starts with either the first cell that has data or the second cell has data (needed for instance if the first cell is just the heading and doesn't contain data). You'll notice I have hardcoded the "includeFirst" option right now. I don't know how to pass this as an argument when calling this subroutine.
In the first subroutine updateAll() I need to call the second subroutine and want to pass an argument to it. Maybe my approach using subs and application.run is the wrong way to do this?
Sub updateAll()
includeHeader = False
Application.Run "SelectColumn"
' need something like:
' Application.Run "SelectColumn(includeHeader)"
' where subroutine below has something like:
' Sub SelectColumn(includeFirst)
End Sub
Sub SelectColumn()
'Select a Column or Row of Cells in a Used Range Quickly
'http://msdn2.microsoft.com/En-US/library/aa155432(office.10).aspx
includeFirst = False
'includeFirst = True
Dim UpBound As Range
Dim LowBound As Range
If ActiveCell.Row > 1 Then
If IsEmpty(ActiveCell.Offset(-1, 0)) Then
Set UpBound = ActiveCell........................
I need a code to open a file automatically based on file name, for example if the active file name is "trial", then it need to open another file named "trial1" based on which the macro will run, because I have two set of files and I want to compere daily with two users. for example the file names in Set a- iron, cement, stone, sand where as the set b-iron1, cement1, stone1, sand1
View 1 Replies View Relatedformula to calculate the commission based on 9 cases each case is subjected to 4 differant arguments.
here is the formula for the first case (the answer if true 1.4) & the last case( if all the casses are fails it should show 0.6) u will find it in cell K5
=IF(AND(E5>=20,H5=1,I5>=80,J5>=80),1.4,0.6)
this is only 1 case HOW can i calculate it basd on 9 casses??
I'm fairly new to VBA (exactly 8 hours of paid experience ) and I've stumbled upon the following.
Say I wanted to select a certain range of cells (which later have to be merged, don't ask why, they just have to. It's in the job description and it's not in my power to change things around here).
When I try a MATCH function, I substituted the range through the ADDRESS function. But, it returns #VALUE error. When I manually typed the same range address, it produces the results. I've been behind this more than 8 hours still I can resist the heat on my cool head! But, I thought you coolest head guys need some heat to warm up for to-day's challenge.
View 9 Replies View Relatedi have a calendar with the dates in a row
I want to count instances of an entry under the dates using 'countif' from 01JAN until today.
My problem is that today changes value [up by one] each day so the 'countif' function will need to extend its range to the right by one cell each day.
I am trying to write a macro that has multiple if arguments.
An example is as follows:
If cell b11 had fill colour red, then colour fill cell B8 green,
If cell b11 had fill colour green, then colour fill cell B8 red,
If cell b11 had fill colour yellow, then colour fill cell B8 blue,
If cell b11 had fill colour blue, then colour fill cell B8 yellow,
end if.
There are 100 loans, each one of them need to be tested on 11 different tests, the results are either "pass" or "fail". All the test results are stored in the Boolean array. g_sel_tag(). For instance, if g_sel_tag(2,7) = true that means the 2nd loan has failed on the 7th test.
But I got stock with the following code, I think there should be a loop for the public variable rep_num in order to loop through the 11 test results for each loan. I have searched the entire project but did not find any value was assigned to the variable rep_num. The following code is part of a huge project, I can't test run it to see if the following code is valid at this point.
I am new to VBA therefore wondering if it is valid with no specific value assigned to rep_num. (I think there should be something like "for rep_num = 1 to 11" in order to get it running).
There are several codes in the project with similar structure where they just use rep_num without assigning any specific values.
Code:
Dim rep_num As Interger
Sub test_results_reporter (rep_num As Interger)
Dim i As Interger
Dim g_sel_tag(1 to 100, 1 to 11) as Boolean
For i = 1 to 100
If g_sel_tag(i,rep_num) Then Msgbox "Loan" & i & "has failed on test" & rep_num
Next
End sub
In the Formula below I am attempting count records that are not "Closed" or "Cancelled"
But records with those two status values are still being counted I have a feeling that my attempt to create a "not equal to" condition may be the problem, even though Excel did not error when I put in this formula. I am certain that the worksheet and columns that these conditions are pointing to are correct. Note that the first 2 conditions are working correctly.
=COUNTIFS('QC Extract'!$C:$C,$B34,'QC Extract'!$I:$I,F5,'QC Extract'!$H:$H," Closed",'QC Extract'!$H:$H," Cancelled")
I need a simple IF AND formula. I just can never manage to get them right. I need a formula (in Column M) to evaluate a date count in Column L (which is a count of business days since another date, fyi). It needs to check to see if the number of days since the date in Column L is greater than 10 AND if the value of Column I is one or zero. If the argument returns true (more than ten days and a zero in Column I) it should return a vaue of "Yes" and "No" if false.
I want to be able to call a procedure and pas 2 variables that it will need to it.
The code looks like this:
Private Sub UpdateScreen(StartLine As Byte, EndLine As Byte)
End Sub
UpdateScreen(104 , 140)
For some odd reason I get an "Expected: = "error when I enter the last line. I works if I send only one variable, but not when I send 2.
I am trying to compute correlations using Correl function available in Excel. Get "Argument Not Optional" Error. Cannot understand what I am doing wrong.
Sub test()
With Worksheets("Corr")
Range("H1").Value = WorksheetFunction.Correl(Range("A1:A252, B1:B252"))
End With
End Sub
i want to create a lot of buttons, one in each line, that, when you click on them, open a file. The filename is in a cell in the same row as the button.
Of course every button should open a different file, and that is the problem. As far as i know (not very much) the buttons can only call other subs, and not with an argument.
What should i put into the "onAction" property of each button, so that each button opens a different file? I really dont want to create hundreds of subs just to get this done, there must be another way.
PS: By the way, how can i delete all but one buttons of a sheet with a vba script? Or shall I open another post for that?
I have a 100 element header row. Each cell represents an account. I want to sum the amounts of each subsequent row if the word "saving" shows up in the header. For instance, there may be "saving 1" "Savings for Xmas" "Savings 2" in the header row, and I want those to trigger the sumif function.
Basically I want something like sumif(B2:B101,exact(left(B2:B101,6),"saving"),A2:A101)
This error occured me, and i clicked "Debug" to see where was the error and i found a strange thing that i don't know how to solve it:
The line has the following
I have an attached file and I am trying to build a VBA function to calculate total values. First row is "RollingTime" and for example if I am trying to calculate the "Total" value. For "RollingTime" = 2 it should be
RollingTime(2)*Percentage(2)+RollingTime (1)*(1-Percentage(1))*Percentage(2)+RollingTime(0)*(1-Percentage(0)*(1-Percentage(1))*Percentage(2)
Which is 109732508*0,3 + 1017508995*(1-0,2)*0,3+1587172158*(1-0,1)*(1-0,2)*0,3
And here is the code I have tried to produce:
spreadsheet has daily hours worked per employee and a weekly total. What is the formula to take the weekly total per employee and separate the regular hours (first 40) and have the overtime show up in the next column (without getting negative numbers for the totals under 40).
View 4 Replies View RelatedWith the UDF below and the portion highlighted in red, how can the red part be made part of the arguments instead of specifying it in the code?
Code:
Function Countcolor(rColor As Range, rCountRange As Range)
Dim rCell As Range
Dim iCol As Integer
Dim Result
Application.Volatile
[Code]....
I have hit the limit with IF statements in a formula I have in a questionnaire that I made in Excel 2010. How would I create this formula in VBA?
"TRUE" is already returned in those cells where a "Yes" radio button is selected.
I would like the macro to run when a button is selected, and for it to post the return (i.e., "BP Owned Circuit") in cell B6.
Here's the formula:
= IF(AND(C3=TRUE,C4=TRUE,C9=TRUE,C10=TRUE,C11=TRUE,C14=TRUE,C15=TRUE,C16=TRUE,
C17=TRUE,C18=TRUE,C19=TRUE),"BP Owned (Circuit)",
IF(AND(C6=TRUE,C9=TRUE,C10=TRUE,C11=TRUE,C14=TRUE,C15=TRUE,C17=TRUE,C18=TRUE,C19),"BP Owned (VPN)",
IF(AND(C6=TRUE,C7=TRUE,C8=TRUE,C9=TRUE,C10=TRUE,C18=TRUE,C19=TRUE),"Shared VPN (IPSec)",
[Code] .........