Storing Result Of Concatenation Of String And Date And Using It
Feb 18, 2013
I have two cells. One of which contains a String and other contains a date value. I need to concatenate both and use it as a key for vlookup function.
Cells(x,y)= "=VLOOKUP($B7&TEXT($F6,"mm/dd/yyyy"),'Copy Data'!H2:J161,2,FALSE)"
OR
Cells(x,y)= "=VLOOKUP(CONCATENATION($B7&TEXT($F6,"mm/dd/yyyy")),'Copy Data'!H2:J161,2,FALSE)"
In my resultant cell formula looks like =VLOOKUP($B7&TEXT($F6,"mm/dd/yyyy"),'Data'!H2:J161,2,FALSE) itself and the expected result is not achieved.
Eg: B7=04152AB F6=21/12/2010 I need to merge both values and use it in vlookup for finding match from another sheet named 'Data'
View 2 Replies
ADVERTISEMENT
Dec 2, 2008
I have two columns that consist of strings that I want to combine in a certain way. The first column has a string that consist of numbers and letters and the second column just has 3 letter codes. What I want to do is get rid of the letters in the numbers string and then add the 3 letter code on to the end of the numbers, and have this placed in a seperate column. For example: if column 1 contains "12F51Q" and column 2 contains "ABC" then the result would be "1251 ABC". Is this possible??
View 3 Replies
View Related
Apr 8, 2008
I am trying to write a bit of code for the purpose of creating a dynamic email distibution list going to department managers in the event of an outage. There are checkboxes used to activate different headers to be added to the primary distribution list. I set the DIM statement variables to string type but it keeps telling me I need to set them as objects then if I switch to object type I cannot assign the email addresses in a string. I will list the section of my code giving me headaches: email addresses will be supressed to protect the innocent
Private Sub CommandButton1_Click()
Dim rng As Range
Dim DistList As String
Dim PriHeader As String
Dim Header1 As String
Dim Header2 As String
Dim Header3 As String
Dim Header4 As String
Dim OutApp As Object
Dim OutMail As Object
View 9 Replies
View Related
May 29, 2003
(see spreadsheet below) ....
View 9 Replies
View Related
Feb 19, 2012
I have the following in VBA:
Code:
ActiveCell.FormulaR1C1 = _
"=mod_a1&"" ""&mod_b1&"" ""&mod_c1&"" ""&mod_d1&"" ""&mod_e1&"" ""&mod_f1&"" ""&mod_f1&"" ""&mod_g1&"" ""&mod_h1&"" ""&TEXT(mod_i1,""m/dd/yyyy"")&"" ""&mod_j1&"" ""&TEXT(mod_k1,""h:mm:ss"")"
I only want the user to see the string, not the concatenation formula. So how do I get VBA to turn this formula into an actual string.
I have seen some functions using ByVal and Evaluate with this sort of thing but this is just different enough that I can't figure it out.
View 4 Replies
View Related
Apr 1, 2009
I've created a workbook that I use daily. The workbook has two sheets. One for entering information. The other for storing information.
On the first sheet, I enter health information about myself. I also enter the date.
On the second sheet, I have rows dated from January 1, 2009 to December 31, 2009. If the date entered on the first sheet matches January 1, 2009, then the information is copied to that respective row. If the date entered on the first sheet matches January 2, 2009, then the information is copied to the corresponding January 2, 2009 row, etc, etc.
So far so good. I enter the information, the date, and instantly the information is copied over. I save the workbook and go to sleep happy................
View 3 Replies
View Related
May 19, 2008
I have an Add-in that creates and formats reports for various users.
Within the add-in I have rules set to disable some buttons on a userform untill a process is run to enable them. I am doing this through the use of a variable - set as "0" untill such a time when the process is run that it will set the variable to "1". A function is then run to enable all of the buttons.
However, once excel is shutdown and re-openned - it resets the variable to "0" for no apparent reason. Is this normal? Is there a way around this - so that the Add-in remembers what the variable was set to before closure?
View 9 Replies
View Related
May 27, 2009
I'm trying to concatenate quite a few cells together. One of the items being included in the concatenation is a date, however when I do this the output is the numerical version of the date that Excel uses to mathematically calculate with. My question is how do I do a cell concatenation with the desired output showing the date, not the numerical equivalent used by Excel? Book1.xls attached is what I'm talking about.
View 2 Replies
View Related
Jan 30, 2014
I deal with a rather large excel database (the range is A1:AV168266) that contains customer information. Some of these various customers are affiliated with a group called "ascend" and I need to be able to filter all my list by every customer affilliated with Ascend. The trouble is that the word "ascend" can appear in 8 different columns and usually when it appears in one column it doesn't appear in the others and their can also be more text in the cell beyond the word "ascend". I want to create a formula that will simply look for the word "ascend" in any cell of a row and return a result I can filter by. I will be putting the formula in column AW and put it in all 170,000 rows of that column, that way I can just filter by a single column.
I've played around with combining COUNTIF with SEARCH and MATCH with SEARCH but nothing seems to work.
View 7 Replies
View Related
Feb 27, 2008
I’m trying to resolve this issue and would appreciate some help.
Scenario
I have a tracking sheet that tracks development of work to be completed by individuals.
I have a due date column that shows when the work should be completed.
Once the work has been completed, the user enters his completion date.
I have a formula that provides an overdue warning (Completion date – today’s date) and some conditional formatting.
Problem
When I copy the formula through all the cells in the column I get a number (example 39504) and this changes everyday.
How can I eliminate this being shown as it throws out my average development day calculation?
View 4 Replies
View Related
Oct 19, 2012
I have a spread sheet with a date colume that reads: 2012-06-27-19 I need to have this read like 06/27/2012 but nothing I do is working I have tried to go to the formatting process and backing the hr:mm out and that doesn't work. I really don't want to go line by line to manually correct this issue.. HENCE ... over 2000 lines
Second question: If I have a column that reads 02/15/2012 and another column that reads 3/27/2012 how to a format a 3rd column to make it read total number of days between 1st date and 2nd date?
View 1 Replies
View Related
Jan 17, 2012
I have these as input entered as text:
January 01, 2011 January 01, 2011 February 28, 2010 May 12, 2011 June 02, 2010 February 28, 2010 May 12, 2011 June 02, 2010 February 28, 2010
but I need it in this DATE format:
MM/DD/YYYY
I need it as a formula to be entered into a cell in excel (not VBA)
View 9 Replies
View Related
Aug 10, 2009
As part of a larger macro, the code below is supposed to start the separation of data into 4 columns. It doesn't work perfectly though and I have to manually inspect data and correct. The problem is that 22: 8- 3- 3 becomes 8- 3- 3 in the second column, which is what I want. However 60:12- 7-10 becomes 7/12/2010. Excel appears to recognise it as a date, when the extra number is in the second column.
View 4 Replies
View Related
Feb 28, 2014
I am trying to make a document that counts some results within a range. The criteria of that sum is that it needs to be within a date range. I made a small example in a new document to make things clearer.
Link if it does not work for image: Image - TinyPic - Gratis foto's delen en afbeeldingen & video's hosten
The results between D10 and lower need to be count towards the total on top in one of the cells of January or february (And more in the real document). I need it to automatically stick it in the total of January or February. I need excel to check the Cells in C10 and lower for the date. If the date is in January i want it to automatically write it in total of the corresponding month.
View 1 Replies
View Related
Jun 17, 2009
I want to create & store ad-hoc tables in excel.... can anyone suggest the best way of doing this? Maybe can i use some sort of marker to distinguish when one table ends, and another begins? For eg, maybe i want to store 3 tables - 1 of different fruits and fruit volumes, the 2nd of employees and their shift times, and the third of various different leaflets, paper codes, volumes and dates.... eg, no particular theme
View 9 Replies
View Related
May 18, 2006
I have solution that uses userforms, the intro/menu has a company logo on it. What I want to be able to do is use this logo on actually worksheets that I have set up as templates for sending out invoices etc.
The person I am doing this for sometimes uses pre-printed headed paper and sometime plain papper. So I have a check box that asks whether headed paper is used. If so not a problem, if not, I need to add the logo at the top of the page, ideally from within the workbook.
View 4 Replies
View Related
Feb 20, 2007
Here's the code linked with excel spreadsheet:
Function Function1(DailyClose, EFBillsYield)
TradingDays = Application.WorksheetFunction.Count(DailyClose)
For i_cnt = 1 To TradingDays - 1
DailyReturn = Application.WorksheetFunction.Ln(DailyClose(i_cnt) / DailyClose(i_cnt + 1))
Next i_cnt
AnnualReturn = Application.WorksheetFunction.Average(DailyReturn) * TradingDays
AnnualVolatility = Application.WorksheetFunction.StDev(DailyReturn) * Sqr(TradingDays)
RiskFreeRate = Application.WorksheetFunction.Ln(1 + EFBillsYield)
Function1 = (AnnualReturn - RiskFreeRate) / AnnualVolatility
End Function
When I enter the parameters DailyClose (which is an array of numeric), and EFBillsYield, which is a numeric also, the function will calculate the DailyReturn (in array) for each array element in DailyClose.
The problem is, I don't know how to catch the DailyReturn for each DailyClose element. The above code only loops and calculates DailyReturn one by one, but not storing it as an array.
How should it be modified so that the DailyReturn can be numeric array also, so that it can perform average and SD functions below the loop?
View 4 Replies
View Related
Dec 8, 2012
I want to activate a specific cell on a specific day...namely today.
I have a excel spreadsheet with various dates that when it reaches today i want another cell to activate and be shifted to the active cell.
I have the following columns:
Date of transaction Earned Points this transactionActive PointsActive date of pending points Pending Points
07 November 2012 R 40.00 08 December 2012 R 40.00
Now, i want the pending points to activate and be shifted to active points on 08 december 2012. my active date of pending points columns is automatically calculated from 07 november 2012 by formula =C3+31
View 1 Replies
View Related
May 21, 2007
way to get a formula result to freeze or remain unchanged AFTER a certain time?
I tried using a macro which runs when the file is opened and does a crude 'copy/paste values' operation and replaces the Formula results with their values, but its is not very 'nice'!
I need a table to change a pattern of values but not in the past, but still to remember what that value was....!
View 9 Replies
View Related
Dec 13, 2007
I am trying to display the result of a custom VBA function as a label on a userform. The function is called and takes arguments from three text boxes on the userform. I attached it to the exit parameter. (This may be part of the problem; I'm not sure).
There seem to be two problems, first all the text boxes don't always contain data, but because the arguments are not optional, I get a type mismatch error when the code runs (I tried switching some of them to optional, but it didn't help as you can see from my 'commented code).
The other problem is that even when all the arguments seem to be met (i.e. the text boxes all have data), I still get a compile error that the argument is not optional. This only happens if I try and pass the result to some aspect of the userform. As you can see from the last coded line in the custom function, if I assign it to a range on the worksheet, it functions fine.
Private Sub txtStartDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim StartDate As Date
Dim EndDate As Date
If IsDate(Me.txtStartDate.Value) Then
StartDate = Me.txtStartDate.Value
Me.txtStartDate.Value = Format(StartDate, "m/d/yyyy")
Else: MsgBox "Please enter a date"
End If.............
View 5 Replies
View Related
Feb 26, 2009
I have a worksheet that has data in cells A3 - J3. One value will be placed in one of the cells directly below A3-J3. Is there a way to look at the cells A4-J4 and determin what cell has the value and then preform a concatenation on that cell pluss the one directly above it.
I have included a worksheet as an example.
View 11 Replies
View Related
Oct 7, 2009
why this code continues to give an error?
Sub TakeOut()
'
'
Windows("EXTRACT2.xls").Activate
Sheets("Sheet1").Select
For i = 1 To 100
Range("A" & i).Formula = "=CONCATENATE(""Trip "", ""=RIGHT('[EXTRACT.xls]Sheet1'!A & i,3)"", "" = "", '[EXTRACT.xls]Sheet1'!E & i)"
Range("A" & i).Value = Range("A" & i).Value
Next i
End Sub
View 9 Replies
View Related
Nov 23, 2009
I am trying to store a variable (I will highlight it in red below) to be used later in the same formula. The formula I have now works, but it puts the variable in cell G20, and I do not need this variable anywhere on the sheet. However, I do need it to perform some calculations later in the code.
View 14 Replies
View Related
Jul 10, 2013
My code needs to find the last date listed in column A, which it already can do. And calculate how many days have passed between today and that date it finds in the column. Which I think my code can already do.
How do I store the number for later use?
Meaning, the next part of this code is going to send a request to a website to retrieve however many days of data that number is to be above ^. So it might be 2 days, 12 days 7 days, who knows...but the http request part will come after.
Code:
Sub date()
Dim lngLastRow As Long, lngRow As Long
Dim strColumn As String
strColumn = "A"
With ActiveSheet
lngLastRow = .Cells(.Rows.Count, strColumn).End(xlUp).Row
For lngRow = 2 To lngLastRow
[Code]...
View 4 Replies
View Related
Jul 11, 2014
I'm setting up a type of form in excel where I have some validated drop down lists and I'm trying to use a macro that will, upon a button click, take the data from the drop down lists that are entered into the boxes, and organize them into a chart on a different sheet of the document. This would be the form with some drop down lists.
Juice Type
Apple
Juice Size
8 oz.
Juice Buyer
Ronald
I want to add it in to a table with every button click filling in a new row in a table like this,
Juice Type
Juice Size
Juice Buyer
Apple
8 oz.
Ronald
View 3 Replies
View Related
May 17, 2007
Can I store a value with out storing it in a field? -with the macro ending?
Ex macro1 runs and gets A1.value
Stores the value (Not in a cell)
I later run macro2
Gets the stored value
View 9 Replies
View Related
Jun 26, 2013
I'm trying to make a summary page that will provide a monthly report based on the data in a second sheet.
I have attached an example with dummy data.
I know this could be easily done with a pivot table but the page format has to remain static and reflect current practises.
At the moment I can only return data from the whole range - not just the desired month.
Fruit Example.xlsx‎
View 2 Replies
View Related
Sep 29, 2006
Instead of calculating a time difference manually I want to have a formula do it for me. What I want to achieve goes as followed:
08/06/2006 04:33
12/06/2006 01:05
Time difference is 92:32 [hh:mm]
another example:
09/06/2006 12:42
12/06/2006 11:35
Time difference is 70:53 [hh:mm]
So instead of getting 92:32 and 70:53 by calculating it myself I would like to have a formula do it for me. Otherwise I'll have to invest a lot of time to get the information I need.
View 9 Replies
View Related
Apr 27, 2007
I use datediff to calculate the difference between 2 dates in Months.
Result = Datediff("M", Date1,Date2)
I need the result to be formatted to 2 decimal places ie 23.52 (months)
Not neccessary to use datediff if this doesnt work.
Please note I have cross posted this HERE because I couldnt get connected to ozgrid for some reason, I will montior both sites for a solution and post back results. Apologies for the inconvenience.
View 5 Replies
View Related
Jun 24, 2008
I'm trying to find a formula that will give me a count of all programs by month that have an evaluation result. So, in column A1:A24 I have a list of programs with. In cell A25, I have the total number of programs. In column B1:B24, only 7 of those programs have evaluation results, some of the rows are blank.
I know that I can get a sum of the programs by month, but how do I only get a count of those with an evaluation result? What formula can be used to get a year to date (a running count), not a count based on 12 months? Meaning that we would need a year to date for each month, so currently for October to June (based on fiscal). If we based it on year to date, then I think we would have to divide be 12 and that would make the results inaccurate.
View 2 Replies
View Related
