Date Conversion And Use A Variable Of Type Long As A Search Criteria
Jul 22, 2008
How to assign a variable of type Long to the below code.
1) Dim searchField as LOng
ActiveSheet.Range("N2").FormulaArray = "=MAX(IF($A$2:$A$200=""" & searchField & """,$C$2:C$200))"
2) ActiveSheet.Range("N2").FormulaArray = "=MAX(IF($A$2:$A$200= searchField,$C$2:C$200))"
1) code failed because I want the variable searchField to have a data type of LONG.
2) code failed because it seems like excel thought the search criteria is of same searchField.
How to convert date to general format and store it in a variable of data type Long. I.E if I convert 3/15/2008(data type of Date) to data type of General, it should be 39522. What is the VBA code for doing this conversion?
I have an existing code that runs almost flawlessly but I am running in to formatting cells error (or at least I think that is the problem).
For example, here is a line of code where my error lies:
If Worksheets(FirstSheet).Cells(Counter, ColtoMove) = MoveIf Then 'DO SOME ACTION
If I put my cursor on MoveIf it shows the expect number of 4500016239…….BUT it errors!
When I step through the code and go to the worksheet (or go to the left side of the = in the above code the data in the cell is “4.5E+09“.
I’ve attempted to change the formatting of this cell range to “Text” by the following With statement:
With Worksheets("CreateOrderFormsData") .Range("AC:AC").NumberFormat = "Text" End With
Note: if I verify the cells formatting after the above code is ran by rt click on the cell, format cells the Number field is set to “CUSTOM” and the Type: field is set to “TEXT”. Is this the problem? Please read on.
But that did not seem to do the trick…..actually if I now go to the left of the = again (which is now set to text) the data is “###########” in the cell, in the formula bar the number is the expected 4500016239 though but the CODE STILL FAILS!
Am I on the right track…..can anyone assist with this error please?
I'm trying to use application.match() but I'm running into a issue where I don't know if I'll be matching a string or long data type...
Here's the snippet of code:
Code: ' UNIT DATA ' Set Unit# s1 = ActiveSheet.Pictures(Application.Caller).Name iLen = Len(s1) s1 = Mid(s1, 2, iLen - 2) ' Set Sel Ex Work Date iCheck = Application.Match("MACHINE_NUMBER", Sheets("Allocation").Columns(1), 0)
[code]....
How can I get application.match to look for strings if s1 = "ABC123" or numbers if s1 = "123456"
I've tried dim variant and s1 + 0... But, haven't come up with a solution.
My difficulty: I have an Excel 2007 DB xlsx Workbook file (mydb.xlsx) made up of several (8) Worksheets. I can't modify the content of this DB. For this reason I 've created another Workbook (MyGUI.xlsm) implementing a user interface for db querying.
The userform let the user making searches using the find method (criteria: "Text contains...") In the DB file there is one particular Sheet with thousands of entries. This makes the searchs too long (30 seconds). During this period the entire form is blocked.
My question: when the user realizes the search is too long I'd like to stop the current search introducing for example a new button "stop current Search" to click it in these situations.
Attached is the mock excel spreadsheet. I want to read "sam" from the lookup column's long text "sam is good" and then write "4" in the next column. Similarly read "white" from the long text "white is tired" and write "1". And so on,,,,, For more criteria, see box highlighted in yellow,,,,I used VLOOKUP but what am able to make it work only when there is one word "sam" in the lookup column. It returns #NA when the text is "sam is good". It should write nothing if none of the criteria is met and should keep doing until the last cell in the lookup column.
1. Is there a VBA Function equivalent to the FIND() function, If so What is it? 2. Let's say Im Putting a Date into a inputbox, what is the type # for date (Type:=?)??
I have a large spreadsheet, within which i am trying to remove commas from all cells. I get the error 'formula is too long' when I carry out the search. Some of the cells are >1024 characters in length and contain dates, text etc.
I am trying to convert numbers from three cells into a date in one cell. I tried using the =DATE function, but it did not work. In A1, B1, C1, I have 1, 15, 2002. I would like cell D1 to take the info from the preceeding three cells and display "January 15, 2002"
Presently, I have A1...C1 formatted for "General." D1 is formatted for "Date." Yet I get the answer "August 23, 1907" (or 2792 as 'General').
Whenever aiming to open, copy, or delete files, the following VBA code is rather a must
Dim SOMETHING Set SOMETHING = CreateObject("Scripting.FileSystemObject") in that we may then continue with either
SOMETHING.CopyFile (source), destination, True or
SOMETHING.DeleteFile what_to_delete, True etc, etc The question: which type a variable is SOMETHING from above? Furthermore, could anybody please help me to understand why never seen in literature a complete declaration such as
I'm trying to convert a number from word to a number in excel. Example: 10-12 input in word to a cell in excel. Unfortunately it treats it as a date an gives me 39002. I want to be able to use both numbers and add them together to get -2 not 39002. How to format the excel spreadsheets in order to complete this calculation.
I have come across an issue and I am not sure what the best way is to achieve my goal or if it's even possible. We are importing dates that are in IBM DB2 Date format. Example: the date is imported as 1060318(C/YY/MM/DD), which is is actually 2006 March 18th. I would like to convert 1060318 to something that Excel can format into March 18th 2006.
One of my workbooks won't allow me to use the VBE variable types (VBProject, VBComponent, etc.) They aren't on the popup list that appears when I type " as ". If I just type in "VBProject", I get a compile error "User-defined type not defined". I have to define the variable as object, and the macro still works. I do not have this problem in any other workbook.
Is there an alternate to this? The selection is every alternate cell starting M3, O3 & so on.
Also, All of the above reference columns have date stored in "2002 Aug 02" format. Is there a quick way of selecting the columns automatically & converting it to DD-MMM-YY?
I tried various methods but nothing works unless I manually enter the date. I am looking for an easy way because I have 100's of sheets to fix with 30+ columns. It will be a big and tedious job if I try to create a separate column beside each and then convert using a formula.
I am currently working in an application in which I use the following user defined types:
Public Type correlationstruct corr As Variant corrstd As Variant lower As Variant upper As Variant support As Variant End Type
I have a public variable called myctafund, of type ctafund:
Public myctafund As ctafund
My problem is the following: When I need to update myctafund, I first want to clean it entirely before putting new values, so as to be sure older values do not stick around (I do not necessarily overwrite all the values of the mycta type).I can't figure out a way of clearing the entire myctafund variable in one shot. I tried:
myctafund=empty
but I am granted by a nasty excel error. I'd like to avoid writing 200 lines of code just to clean the entire variable.
In the attached sheet, one macro is developed which is not working properly. My req is to convert date from dd-mon-yyyy to yyyy/mm/dd format. (23-jan-2010--> 2010/01/23)
If incomplete date is there, then hyphen should place the gap i.e.
For date like APR-2014------> Output should be 2014/04/-- For date like 1998 -----> Output should be 1998/--/--
The below macro works only for row number 11, 15 and 16.
I have a question about conversion of a text format into date format.
2 computers, the first windows vista, the second Windows 8, both Excel 2007.
The purpose is to calculate the difference between 2 dates with hour:minutes:seconds, like for example: 18/12/2012 19:20:00 and 18/12/2012 19:23:03.
So in this example the result should be 00:03:03.
Both dates are formatted with 'personalized' : dd/mm/yyyy hh/mm.
In the first computer everything works like it should, but in the second it is apparently impossible to transform the first of those date h:m:s which is originally in a text format into a date format. I tried all the methods which i found to transform a text date format into a date , like multiplication with 1, Datevalue and others, but nothing seems to work, the result is always "#Value".
I need this: 38842.8287152778 to be converted into 05052006_075321PM. This is the date for May 5th 2006 @ 7:53:21 pm in VBA. How do i get there from 38842.8287152778?
Here is an example of my spreadsheet: Excel example.xlsx
I would like to get all the bid/ask quotes for the different currency pairs in sheet 2 into sheet 1 sorted by the right date and time.
I need a formula which recognize the time + the currency from sheet 1 and search for it in sheet 2 then brings the right bid/ask quote back into sheet 1.
Here's what I'm doing: I'm using a macro to assign a cell value to a variable then set another cell value to the variable instead of copy/paste (because even pasting values only was affecting other formulas in the file for some reason)
The problem: in using the macro, the number being 'copied' is acquiring a few extra decimal places IE 38334.61 is the original number and 38334.609375 is what I end up with. The numbers come from a CSV with only 2 decimal places and I checked by adding decimal places in the format so it's not a formatting/visible digits issue. Since the values are hour meter readings, the extra decimal places end up with very small values outside 0-24 hrs which messes with sorting and usage %. The values are so small all of them together in a month add up to a fraction of a cent but it's one of those things that bugs my OCD by not being right.
My VBA book explains the min/max capabilities, content type, memory bits but not fiddly details like this.
So my question is this: right now I'm declaring the clipboard-substitue variable as an single, is there a different one that would work better without adding anything? Criteria are: numeric, 6 digits before the decimal, 2 after, all positive values. (Ie 123456.12)
I have the following codes in my spreadsheet, they are pretty much the same and both used to work perfectly. However, now the first code returns a Type Mismatch '13' on line beginning mynum= and the second one works perfectly.
