Use row number as factor in calculation, replace original values

[margieh204]

I'm trying to determine the VB for a macro that will replace the values in a column by the the results of the calculation of that same value + (row number/ 100000). At the present, I am creating an additional column and using the following formula:

IF(E2>=1, (E2+(ROW(E2)/10000000000)), "")

What I need is for the resulting value to replace the original value in E2. Sorry for the redundancy. This is needed so that vlookups can be preformed on the actual values as attained through a combination of Rankings and MIN/ MAX value determinations on multiple worksheets within the same workbook. I only want to alter the original values slightly, so that the data set will contain all unique values. I need this calculation to be done to three growing columns of data.

I plan to attach this macro to a form button on the sheet where the data is located.

Thanks in advance.

 

[sfpowell]

Like this?

Sub Test()
Dim x As Long
    For x = 2 To Range("E" & 1048576).End(xlUp).Row  'use 65536 instead of 1048576 if .xls format
        Range("E" & x) = Range("E" & x).Value + x / 1000000
    Next x
End Sub

 

[Rick Rothstein]

    For x = 2 To Range("E" & 1048576).End(xlUp).Row  'use 65536 instead of 1048576 if .xls format

Actually, just use Rows.Count and it will work in any version of Excel automatically...

For x = 2 To Range("E" & Rows.Count).End(xlUp).Row

I prefer to avoid the concatenation whenever possible which can be done by using Cells instead of Range...

For x = 2 To Cells(Rows.Count, "E").End(xlUp).Row

 

[margieh204]

Thank you both! This literally works perfectly. I was completely over complicating things.

 

[sfpowell]

Actually, just use Rows.Count and it will work in any version of Excel automatically...

Good tip. Thanks!

I prefer to avoid the concatenation whenever possible which can be done by using Cells instead of Range...

I'm curious as to why you avoid the concatenation? I've always used it mostly because I think in terms of "A1" instead of (1,A). Does it make any difference in how the program runs or is it just a matter of preference? ...just trying to learn anything I can.

 

[Rick Rothstein]

I'm curious as to why you avoid the concatenation? I've always used it mostly because I think in terms of "A1" instead of (1,A). Does it make any difference in how the program runs or is it just a matter of preference? ...just trying to learn anything I can.

Pretty much a personal preference. Range using string concatenation is slower than Cells, but the time difference is nearly non-existent for today's computers. My showing both methods was more for informational purposes (let's call it a teaching moment) as opposed to offering truly more efficient code.