CODEWITHSUNDEEP
vbaexcelfor-loop
The Roman Seasonal
The Roman Seasonal

Reputation: 35

For loop to change formula in relation to range excel vba

Wow, I feel like I'm really close on this one.

I have this code in a module:

Range("R1400").Formula = "=(ROUND(AC1400, 0))+0.0625"
Range("R1401").Formula = "=(ROUND(AC1401, 0))+0.0625"
Range("R1402").Formula = "=(ROUND(AC1402, 0))+0.0625"
Range("R1403").Formula = "=(ROUND(AC1403, 0))+0.0625"
Range("R1404").Formula = "=(ROUND(AC1404, 0))+0.0625"
Range("R1405").Formula = "=(ROUND(AC1405, 0))+0.0625"
Range("R1406").Formula = "=(ROUND(AC1406, 0))+0.0625"
Range("R1407").Formula = "=(ROUND(AC1407, 0))+0.0625"
Range("R1408").Formula = "=(ROUND(AC1408, 0))+0.0625"
Range("R1409").Formula = "=(ROUND(AC1409, 0))+0.0625"
Range("R1410").Formula = "=(ROUND(AC1410, 0))+0.0625"
Range("R1411").Formula = "=(ROUND(AC1411, 0))+0.0625"
Range("R1412").Formula = "=(ROUND(AC1412, 0))+0.0625"
Range("R1413").Formula = "=(ROUND(AC1413, 0))+0.0625"
Range("R1414").Formula = "=(ROUND(AC1414, 0))+0.0625"
Range("R1415").Formula = "=(ROUND(AC1415, 0))+0.0625"
Range("R1416").Formula = "=(ROUND(AC1416, 0))+0.0625"
Range("R1417").Formula = "=(ROUND(AC1417, 0))+0.0625"
Range("R1418").Formula = "=(ROUND(AC1418, 0))+0.0625"
Range("R1419").Formula = "=(ROUND(AC1419, 0))+0.0625"
Range("R1420").Formula = "=(ROUND(AC1420, 0))+0.0625"
Range("R1421").Formula = "=(ROUND(AC1421, 0))+0.0625"
Range("R1422").Formula = "=(ROUND(AC1422, 0))+0.0625"
Range("R1423").Formula = "=(ROUND(AC1423, 0))+0.0625"
Range("R1424").Formula = "=(ROUND(AC1424, 0))+0.0625"
Range("R1425").Formula = "=(ROUND(AC1425, 0))+0.0625"
Range("R1426").Formula = "=(ROUND(AC1426, 0))+0.0625"
Range("R1427").Formula = "=(ROUND(AC1427, 0))+0.0625"
Range("R1428").Formula = "=(ROUND(AC1428, 0))+0.0625"
Range("R1429").Formula = "=(ROUND(AC1429, 0))+0.0625"

Now, let me be clear, this code works as I'd like it to. It might be obvious that I'm taking a range of numbers in one column, rounding them all to the nearest whole integer, then adding a sixteenth, and representing those values in a different column.

however

I feel like this could be 'better' executed in less code, and I'd like to become a more efficient programmer, so I try to write it like so:

Dim X As Integer
For X = 1400 To 1429
    Range("R" & X).Formula = "=(ROUND(""AC"" "&X", 0))+0.0625"
Next X

But this (and the variations I've tried) all throw '1004' errors or syntax errors. Most of the changes I've made have had to do with the quotation marks and their placement, and then I've also tried to implement advice from these questions here, here, here, here, and here, but none of them seem to offer quite the solution that I'm looking for, or they don't make sense to me in my inexperience.

It's possible (likely) that my searches haven't contained the proper verbiage I'm looking for, but that's probably because I'm just now taking my first stab at VBA with this :)

Thanks for any help or references. This is my 1st question on StackOverflow, but you folks have definitely taught me darn near everything I know about coding to date.

Upvotes: 2

Views: 7011

Answers (2)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35863

As I mentioned in comments above, you don't need loop, you can simply use:

Range("R1400:R1429").Formula = "=(ROUND(AC1400, 0))+0.0625"

Excel would adjust formula properly for each row, so you would have:

  • in R1400 formula =(ROUND(AC1400, 0))+0.0625
  • in R1401 formula =(ROUND(AC1401, 0))+0.0625
  • .....
  • in R1429 formula =(ROUND(AC1429, 0))+0.0625

Upvotes: 3

tmoore82
tmoore82

Reputation: 1875

You need to cast the X value in the Range function as a string. And you can pass the value of the referenced cell directly to ROUND using the same technique, rather than passing the cell reference. I don't know if that's an improvement, but it was my first instinct.

Range("R" & CStr(X)).Formula = "=(ROUND(" & Range("AC" & CStr(X)).Value & ", 0))+0.0625"

As @simoco has pointed out, you don't have to use the loop at all. But if you still wanted to, or run into similar problems in the future, the above should work.

Upvotes: 2

Related Questions