Simple Copy/Paste code

[adamgram]

Can someone help me debug this code? Posted below, what I am trying to do is run a calculation with 400 combinations of variables, listed in columns A and B from Row 4 to 404. I want to copy AX into F1 and BX into F2, at which point C3 becomes the answer that I want to copy to CX and repeat. Seems pretty simple, but clearly I don't know VBA...


Sub Radiation()
Dim X As Integer
For X = 4 To X = 404
Range("AX").Select
Selection.Copy
Range("F1").Paste
Range("BX").Select
Selection.Copy
Range("F2").Paste
Range("C3").Select
Selection.Copy
Range("CX").Paste
Next X
End Sub

[Teeroy]

Hi adamgram,

This code looks like you've started from a macro recording and, while this is a good way to learn, it produces inefficienct code.

Firstly debugging the code, Range("AX") should be Range("A" & X), and similar for B and C. Your code supplies a literal string where the second concatenates "A" and the variable. A clean coding of what you are doing (note I'm not endorsing this just showing the difference) is;


Sub Radiation()
Dim X As Integer
For X = 4 To 404
Range("A" & X).Copy Range("F1")
Range("B" & X).Copy Range("F2")
Range("C1").Copy Range("C" & X)
Next X
End Sub

Secondly what you are actually doing with this is not copying the value that is calculated in "C3" but the formula and since the values of the relative references keep changing you'll get some strange answers. You could use pastespecial to only paste the answer to the caluculation but there is a better way;
Drag cell F2 to G1, and C3 to H1 to get the calculation relative to the data correct with respect to the data columns then the VBA will be simply:

Sub radiation()
Range("C4:C404").FormulaR1C1 = Range("H1").FormulaR1C1
End Sub

Have fun learning VBA.

[adamgram]

Thanks.

I actually switched the project over to MATLAB which is why I didn't respond sooner, but I wanted to thank you for your help.