Excel 2011 vba: How to loop rows of a column and multiply all rows by a single value?

I just wanted to get 2 columns (imagine they will host values such as numbers for x and y coordinates) and divide ALL values (aka rows) from column (let's say) A(for x) by a certain value in the column C. And I'd like to do the same for column B (for Y) in column D.

This is how far I got.

(almost forgot to tell you guys that the proportion_height and proportion_width are obtained by the division of H3 and I3 (e.g. 1024 x 769) by H4 and I4 (800 x 600). Having these numbers H3/I3(which is stored in proportion_width) and H4/I4 (which is stored in proportion_height) I just need to know how to multiply these 2 values form column A to C and from column B to D. That's it!

Sub landmarks_resizer()

' Creating variables to store the proportion of the new map. Whatever (size) it is.
Dim proportion_width As Long
Dim proportion_height As Long
Dim size_of_column As Long
Dim current_row As Long


' Just checking for NON zero values to avoid errors...
If H3 > 0 And H4 > 0 And I3 > 0 And I4 > 0 Then
    proportion_width = H3 / H4
    proportion_height = I3 / I4
End If

' Changing headers of these columns to better identify them with new values
Range("C1") = "Resized X"
Range("D1") = "Resized Y"

' Go to the very last row of column A. And from there goes Up. Which will go to the last row of column A. :-)
Range("A" & Rows.Count).End(xlUp).Select
current_row = ActiveCell.Row

With Range("H1") '<--| reference a "helper" free cell (change "H1" to your needs)
.Value = proportion_width '<--| store the dividing factor in the "helper" cell 
.Copy '<--| store the dividing factor in clipboard
End With
With Range("A1", Cells(Rows.Count, 1).End(xlUp))
.Offset(, 2).Value = .Value '<--| copy column A values to columns C
.Offset(, 2).PasteSpecial Operation:=xlPasteSpecialOperationDivide '<--| divide column C values by the value in clipboard
End With
Range("H1").ClearContents '<--| clear the content of the "helper" cell
Application.CutCopyMode = False '<--| release the clipboard

End Sub

I added the code from my colleague here and I ALMOST got there! Whenever I ran the macro it says '#DIV/0!'.

Solution

you can exploit the PasteSpecial() method of Range object

this is the example for dividing column A values by proportion_width and placing the result in column C

With Range("H1") '<--| reference a "helper" free cell (change "H1" to your needs)
    .Value = proportion_width '<--| store the dividing factor in the "helper" cell 
    .Copy '<--| store the dividing factor in clipboard
End With
With Range("A1", Cells(Rows.Count, 1).End(xlUp))
    .Offset(, 2).Value = .Value '<--| copy column A values to columns C
    .Offset(, 2).PasteSpecial Operation:=xlPasteSpecialOperationDivide '<--| divide column C values by the value in clipboard
End With
Range("H1").ClearContents '<--| clear the content of the "helper" cell
Application.CutCopyMode = False '<--| release the clipboard

you can act similarly for column B to D