Set range value to int of range value

Box Jockey · Jan 31st 2023

I feel like I have done something similar to this in the past, but now I am starting to think I am remembering wrong.

Assuming that rng1 and rng2 are identical lengths, is it possible to do something like the following? Output wise I am just getting #VALUE.

rng1.value = [Int(rng2.value)]

**Carim** · Jan 31st 2023

Hi,

With Excel ... everything is possible

It depends however on your final objective ...

You can test your formula as an Array Formula with Control+Shift+Enter instead of the standard Enter key

Box Jockey · Jan 31st 2023

Thanks for the quick response! So I am trying to achieve this in VBA, not within a worksheet formula situation.

**Carim** · Jan 31st 2023

OK ... within a macro... there are several possibilities ....

Would you mind posting attaching a sample file or, at least posting your macro ....

Box Jockey · Jan 31st 2023

Apologies as its a big rats nest, let me know if this helps to clarify or not! The line below is what has worked for years, but recently the report I am importing has changed to include the time stamp in the date column I am using, so trying to kill that. This is the line I was hoping to replace with something more elegant like the above, but worst case I am just going to create new formula based columns in the imported report and will copy the INT'd values from there.

PS this is within a with (With wbOpenBook.Worksheets(1) )

Sheet2.Range("G" & dToolRow & ":G" & dToolRow + (dExtractRow - 2)).Value = _

.Range(.Cells(2, rFindRange.Column), .Cells(dExtractRow, rFindRange.Column)).Value

Box Jockey · Jan 31st 2023

PPS the above is to "copy" the dates from an imported file into a date column in the workbook containing the VBA. Works fine as is, just want to INT the date/time data now.

**Carim** · Jan 31st 2023

Without interfering with your current process ...

Code

Sub ExtractIntegers()
' Array to get Integers for Range starting in cell G2
Dim i As Long, lr As Long
Dim arr() As Double
lr = ActiveSheet.Range("G" & Rows.Count).End(xlUp).Row
ReDim arr(0 To lr - 2)
    For i = 0 To lr - 2
        arr(i) = Int(Range("G" & i + 2))
    Next i
ActiveSheet.Range("G2").Resize(UBound(arr) + 1).Value = Application.Transpose(arr)
End Sub

Display More

Hope this will help

Box Jockey · Jan 31st 2023

Gracias!!

**Carim** · Feb 1st 2023

Quote from Box Jockey

Gracias!!

De Nada ... Señor

Thanks for your Thanks AND for the Like

Set range value to int of range value

Box Jockey Jan 31st 2023

Participate now!

Share

Similar Threads

ReDim Preserve - Subscript Out of Range

MSG Box to define range of Convert to Number

Adding Range of Cell Value to Comments

Vba to find a value from a range of returns values of index function

Update of Partial cell value

On selection range of data value show in another row in a range