Cells(#,#).Value conversion

Hey I want to grab a serialized date and turn it into an integer in my
vba
what is the proper conversion for this ?

exampel:

Dim startDate As Integer
Dim endDate As Integer

startDate = ActiveRow.Cells(1, 2).Value
endDate = ActiveRow.Cells(1, 3).Value

0
8/2/2005 4:09:02 PM
excel 39879 articles. 2 followers. Follow

6 Replies
452 Views

Similar Articles

[PageSpeed] 41

Hi Alexandre,

In addition to Bob's suggestion, change:

> startDate = ActiveRow.Cells(1, 2).Value
> endDate = ActiveRow.Cells(1, 3).Value

to

     startDate = Cells(1, 2).Value
     endDate = Cells(1, 3).Value


---
Regards,
Norman



"Alexandre Brisebois (www.pointnetsolutions.com)" 
<alexandre.brisebois@gmail.com> wrote in message 
news:1122998941.948843.88220@g47g2000cwa.googlegroups.com...
> Hey I want to grab a serialized date and turn it into an integer in my
> vba
> what is the proper conversion for this ?
>
> exampel:
>
> Dim startDate As Integer
> Dim endDate As Integer
>
> startDate = ActiveRow.Cells(1, 2).Value
> endDate = ActiveRow.Cells(1, 3).Value
> 


0
normanjones (1047)
8/2/2005 4:07:36 PM
Hi Alexandre,

And, in VBA. change the dimming from Integer to Long,


---
Regards,
Norman



"Norman Jones" <normanjones@whereforartthou.com> wrote in message 
news:uJJ8aK4lFHA.3144@TK2MSFTNGP12.phx.gbl...
> Hi Alexandre,
>
> In addition to Bob's suggestion, change:
>
>> startDate = ActiveRow.Cells(1, 2).Value
>> endDate = ActiveRow.Cells(1, 3).Value
>
> to
>
>     startDate = Cells(1, 2).Value
>     endDate = Cells(1, 3).Value
>
>
> ---
> Regards,
> Norman
>
> 


0
normanjones (1047)
8/2/2005 5:02:57 PM
problem is well here is my full script

Sub WritePoints(ByRef RawPoints As Range)
    Dim objSheet As Worksheet
    Set objSheet = ActiveSheet
    Dim ActiveRowOffset As Integer

    ActiveRowOffset = 1

    objSheet.Cells(1, 1).Value = "Task description"
    objSheet.Cells(1, 2).Value = "Date completion schedule"

    Dim ActiveRow As Range
    Dim startDate As Integer
    Dim endDate As Integer
    Dim task As String

    For Each ActiveRow In RawPoints.Rows
        tast = ActiveRow.Cells(1, 1).Value2

        startDate = CInt(ActiveRow.Cells(1, 2).Value)
        endDate = CInt(ActiveRow.Cells(1, 3).Value)

        If (startDate = endDate) Then

            Debug.Print ActiveRow.Cells(1, 1).Value
            objSheet.Cells(1, 1).Activate
            ActiveCell.Offset(ActiveRow, 1).Value = task
            ActiveRowOffset = 1 + ActiveRowOffset

        End If
        
        
        
    Next ActiveRow
        
End Sub

0
8/2/2005 6:15:06 PM
I now get overflows, but i really need my values as ints i need to
trucate the decimals...

0
8/2/2005 6:16:39 PM
I have use Clng() but now the problem is that it rounds uo or down i
simply want to trucate the last digits after the decimal...

any thoughts ?

0
8/2/2005 6:24:07 PM
Alexandre Brisebois (www.pointnetsolutions.com) wrote...
>I have use Clng() but now the problem is that it rounds uo or down i
>simply want to trucate the last digits after the decimal...

Dim foobar As Long

foobar = Fix(YourNumericExpressionHere)

0
hrlngrv (1990)
8/2/2005 6:35:41 PM
Reply:

Similar Artilces:

Creating a group of cells. Need Help Please.
Havn't used excel in a while and I need to create a group of cell corresponding to an input of a min and a max. Here are the details. On one sheet I have a box where you enter th min and a box where you enter the max. In another sheet I want column starting at A2 to output (MIN,A2+1000,A3+1000,....MAX) ho would I do this -- Thundersix ----------------------------------------------------------------------- Thundersixx's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3055 View this thread: http://www.excelforum.com/showthread.php?threadid=50207 Name the...

Excel 2003 extract repeating cells
I have successfully sorted my data to show repeating entries in relation to two specific columns. I can't seem to figure out how to select these repeating entries (without doing it manually, of course) and putting them either into their own column(s) or an entirely different spreadsheet altogether. The goal is to save time in managing THOUSANDS of documents in this manner so that my colleagues can easily pick up repeating entries and take according action. Doing it manually is very time-inefficient. Nit Wit, You don't really describe enough of your layout or what you actually mean...

Combining cells to create a formula
Hi, I have two cells that I want to combine to have a working formula B1 = sum B2 = d3 b3 = =b1&"("&b2&")" D3 = 10 The result in b3 is : sum(d3) How do I get this to result in the actual value in D3. I know I can simply write =D3, but the actual reason for combining the two cells is more complicated than explained here. Thanks Hi I think you are perhaps looking for the Indirect function =INDIRECT("D"&ROW(3:3)) would return the value contained in D3 As you copy down, the formula would alter to 4, 5 etc. represent D4, D5 etc. -- Regards R...

changing the cell information #2
Hi I have files I tried to change the cell information to another cel that have to be the blank for a row that contains director name in cel A and for the movie titles I have to have director names instead of th title names.Please help me, the data in cell B sholuld have to be, A 1 D:B.Jane 2 T:Back to the future 3 T:Aliens 4 T:X-files 5 D:Adam Mathew 6 T:Core 7 T:Titanic B 1 (empty) 2 D:B.Jane 3 D:B.Jane 4 D:B.Jane 5 (empty) 6 D:Adam Mathew 7 D:Adam Mathew thanks -- Message posted from http://www.ExcelForum.com ...

cell looses name after sorting
Can someone help me with the following problem in Excel 2000: in a table I have attached serveral cells with unique cell names, the values in these cells are used in other sheets. the problem is that when I sort the table, the cell names stay in the original rowposition; they are not sorted! while their values are. So Cell names get different values, and other calculations on my other sheets get messed up! How can I make the cell names relative instead of absolute? thankx in advance, Jim --- Message posted from http://www.ExcelForum.com/ "jimfx >" <<jimfx.109zcv@exc...

How can I wrap text across merged cells?
I'm using Excel 2000. I have a set of merged cells A5- E5. I have several lines of text in the cells and I want them to wrap across the merged cells and it is not working. All I get is the first line of text showing and the rest is cut off. It works if the cells are not merged, but I really need to do it in my merged cells. Is there a way to this other than manually resizing the height of the row? Instead of merging cells, have you tried the "Center Across Selection" option ? The appearence is just about identical to what you would get using merged cells, although I d...

Every cell is highlighted
This is a new one for me. Afer opening up Excel '98 and go to a cell, every cell is highlighted when I move the mouse. I can not put any information in any cell. The only way to quit Excel is control, alt, delete and then it gives me "can not quit excel". It does though after a while. Wherever I move the mouse, the cells are highlighted. What gives? Thanks f8 key -- Don Guillett SalesAid Software donaldb@281.com "5hulses" <5hulses@discussions.microsoft.com> wrote in message news:3B8F2E7E-8957-4B26-B64B-AEC88B6099BE@microsoft.com... > This is a new...

Custom cell formatting
I need to create a custom format for a series of cells that will begin like this. I can't figure out what the code character is fora volitile potentially alpha character. Can anyone help me?? -Monica, Dallas 000000 000001 000002 .... 000009 00000A 00000B 000010 000011 ...... Monica something like this might work for you but you'll need to put all the leading digits in for the entry with the alpha character Regards Trevor "MDavison" <davison@fr.com> wrote in message news:#SD0tUzTEHA.1652@TK2MSFTNGP09.phx.gbl... > I need to create a custom format for a series ...

Can the column index in a cell address be made variable?
Hi, To refer to a cell with a variable row number, we can just code it as, for example, Dim i As Interger i=234 Range("A" & i).Select To refer to a cell with a variable column index, it seems not that easy because the column index must be explicitly specified in a cell address. So, if I want to go to the j th column on the 2nd row or j columns to the right of cell AA3, is there a quick and easy way to do it? Thank you in advance. David You may be able to use R1C1 terminology, but you'll need to get someone else to help from here. "cyberdude" <honc...

Top values
Dear friends, need your help again please. I have a table: tbl_Plots (PlotID is the primar key - number byte) and tbl_Data (ID is the primary key - autonumber, PlotID related to tblePlots, height - number byte and diameter - number integer). I need to select the 20 bulkiest trees of each plot, i.e. having the biggest diameter. Also, perhaps in a plot less than 20 trees will be present so I will need all of them. Any suggestions? Thanking you in advance, GeorgeCY hi Geroge, George wrote: > I have a table: tbl_Plots (PlotID is the primar key - number byte) and > tbl_Data (ID is...

Using Sumproduct when some of the values are null
I am having a problem using Sumproduct when some of the cells have null values. I will try to describe the problem. Please let me know if you need additional information. Do any of you have any ideas of how I can fix that sumproduct to get it to work? A B C Row Trend Claims 5 Emergency $0 6 Emergency $0 7 Emergency $0 8 Emergency 81.68% $24,444 9 Emergency 35.00% $164,758 10 Emergency 35.00% ...

Change <value> in outlook
We Have Exchange 2003 sp2 and outlook 2003 sp2. I have renamed 2 AD accounts. Changed all display names, accounts name etc with the 2 accounts. When users open outlook and send to renamed account they see account name <oldaccountname> in the autocomplete list. I have cleared nk2 files in profile , but it still shows newaccount <oldaccountname> in drop down box. Is there a way to edit the <oldaccountnam> value of the renamed account? I tried the same thing with a freshly loaded PC. with the same result. Thanks You can delete the nickname files. -- Ed Crowley MVP - E...

How do I get text to copy from one cell to another ?
Type = in the target cell. MouseClick the cell containing text. Pres Enter key -- Brian ----------------------------------------------------------------------- BrianB's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=5 View this thread: http://www.excelforum.com/showthread.php?threadid=27034 ...

Count unique values
Hi, I've some problems to count unique items (Invoice #) in a pivot table. There is the default solution "Add-a-new-calculated-column" as mentioned on http://contextures.com/xlPivot07.html#Unique, but in my case, it doesn't works. My problem is, that these values aren't in a Excel worksheet; it's a external data source - a SQL-Select via DAO/ADO. Because of that, I don't have the possibility to add a new column. Another reason is, that the pivot table should always be dynamic: Group over this field, group over another field - and always show the number of unique...

Sorting Alphanumeric values in a text field
I'm using Access 2003 for a database for my company. I have a field in a table that has both text and numbers. They are part numbers, for example 21BC124. I kept the field as text because of the text with in the numbers and didn't figure that a numeric field would alow the text. In my part numbers table it sorts correctly (first by number then by letter then by number again), but in my reports and queries there are a few number that sort in the wrong place. Like this... 20D10-3 21BC123 21BC128 22D10 25TD47 21FA101 21FA200 25FA203 38FA601 21FP604 38WS100 I can't quite f...

Locking cell color while allowing data changes in cell
In excel 2000, I created an attendance worksheet for my classes.(Alphabetized names down left vertical column. Dates across top of horizontal row.) I added a different color to all cells in every other row to make for easier reading of each student's name and absences. Every other row stays with a white background. My question: I wondered if it was possible to lock row colors while allowing data to change on top of them. If a new student is added to my class in alphabetical order, the alternating color pattern is often lost. It is a pain to rechange row and cell colors. Any shortcut ...

Highlighted Cell
Hi all If I highlight A1 is there a way to have the cell fill colour yellow, So I can see where I am better, when I move to say A8 etc. A1 go's back to its normal colour and the new cell is yellow. Thanks in advance Dave See if this helps: http://www.cpearson.com/excel/RowLiner.htm -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Dave" <tuco@tuco.karoo.co.uk> wrote in messag...

Convert double value 1.7E+20 to 1.722222222222222222222.
Hi All, I have double value 1.7E+20. I need to convert it and show it in textbox as below 1.722222222222222222222. how do we convert it? do we have any formats (string.format) in C#? Thanks in Advance VijayRama wrote: > Hi All, > > I have double value 1.7E+20. I need to convert it and show it in > textbox as below > > 1.722222222222222222222. 1.7E+20 isn't even close to 1.722222222222222222222, at least not by floating point standards. Why do you want the latter when given the former? > how do we convert it? do we have any formats (strin...

Passing a variable as a parameter value instead of a literal string to a child report?
In the Navigation section (Jump to URL), I have the following: ="javascript:void(window.open('http://localhost/reportserver?/Reports +Folder/My +Report&rs:Command=Render&StartDate=01/01/2009&FinishDate=12/31/2010'))" This works as designed - a new window pops up with the "My Report" report and the StartDate and FinishDate of 01/01/2009 and 12/31/2010 are passed respectively. But what is the syntax for changing the "01/01/2009" and "12/31/2010" to variables that point to the StartDate and FinishDate parameters of the PAR...

Implicit type conversion
In Access 97, Access would perform implicit conversion of data in a text box to, e.g., a Currency when used in an expression like so: If txt_price > txt_sellprice Then However, in Access 2003 this test doesn't perform as expected unless I explicitly cast the text box value to Currency, like so: If CCur(txt_price) > CCur(txt_sellprice) Then Is there a setting or option somewhere that governs this behavior? Or am I going to have to go through my entire app looking for expressions like this and explicity casting them? "Ron Hinds" <billg@microsoft.com>...

Formatting Cells in Excel 97
Hi Guys, Sorry if I sound real stupid but is there anyway that we can control the column formatting in Excel 97 like let's say column A = GENERAL(6), column B = GENERAL(4), column C = TEXT(18) etc? Appreciate any form of advice, thanks!! I replied in the programming group. Are you seeking a programming answer? It's usually best to only post to one group, and include the remark "Please tell me if I should ask this in another group." On Thu, 28 Aug 2003 00:56:47 -0700, "Daryl" <daryl.ho@tnt.com> wrote: >Hi Guys, > >Sorry if I sound real stupid b...

Getting Cell Value from the Concatenate formula
I built a concatenate formula that returns the following result: =Jul! $D27 I am looking for the cell contents of Sheet: July Column D Row 27. I tried to use offset, but I am stumped. Can I add something to the front of the concatenate to not only build the reference to the cell, but also return the value instead of the =Jul!$D27 ? Thanks John =indirect(yourformulahere) Don't include the equal sign in your formula. And match the name correctly (Jul or July???). Depending on the name of the worksheet, you may need to have a string that looks like: 'Sheet 99'!d27 =indirec...

HOW DO I COPY FORMULA WITHOUT CHANGING CELL REFERENCE
My cell c1 contains the formula b1-a1.when i copy this formula to cells d1 and e1 the cells d1 and e1 have the following formula : d1=c1-b1 e1=d1-c1 but i want the following d1 should be b2-a2 and e2 should be b3-a3 how do i do this? One way... In C1 enter & copy across: =INDEX($A:$A,COLUMN()-COLUMN($C$1)+1)-INDEX($B:$B,COLUMN()-COLUMN($C$1)+1) anantth wrote: > My cell c1 contains the formula b1-a1.when i copy this formula to cells d1 > and e1 the cells d1 and e1 have the following formula : > d1=c1-b1 > e1=d1-c1 > > but i want the following > d1 should be b2-...

ComboBox passing values from selected
In my form (Items_frm) I have a combo box (Itemcbx) that displays 4 columns (Item, Makebuy, Revision, IsActive) but is bound to the first column. However, I would like to pass the other values from the remaining columns to other fields in my form after the selection. Is this possible? Can someone provide a sample code? You can use the Column() attribute. Me.Itemcbx.Column(1) etc. Note that the index for combo box columns is zero-based so 0 is the first, 1 is the second and so on... Steve "Angel G" wrote: > In my form (Items_frm) I have a combo box (Itemcbx) that displ...

Deleting Unique Values
How do you delete unique values in a column? I need to filter 7500+ rows to only display duplicate values. The VBA code below will delete the entire row when the value in col. "A" will be uniqe. Consider to make a copy of your entire sheet in order to test the code and see if this is what you need. ------------------------------- Sub Delete_Uniques() LR = Cells(Rows.Count, 1).End(xlUp).Row For R = LR To 1 Step -1 If Application.CountIf(Range("A:A"), Cells(R, 1)) = 1 Then Cells(R, 1).EntireRow.Delete End If N...