1

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!'.

victorwestmann
  • 173
  • 1
  • 12

1 Answers1

1

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

user3598756
  • 28,893
  • 4
  • 18
  • 28
  • Hmmmm. Interesting! And how should I test this? Should I put all of your code into a new module? And then assign it to a button? – victorwestmann Feb 20 '17 at 07:18
  • 1
    you could do that. or you could substitute your `For i = 2 To current_row ... Next i` block with my code. BTW once you found my answer solved your question you could also mark it as accepted! thank you! – user3598756 Feb 20 '17 at 07:25
  • I'm impressed! It's really close to a solution! Thanks for your help so far! I get column C, however, full of "#DIV/0!". But I believe we're really close. – victorwestmann Feb 20 '17 at 07:29
  • 1
    I guess you are dividing by some zero...! Try commenting the `Range("H1").ClearContents` line and see what's in there after the macro runs – user3598756 Feb 20 '17 at 07:34
  • It's the same. Yes. The number I need to multiply all rows in column A by is 0.351988736. Do you know how can we fix this? – victorwestmann Feb 20 '17 at 07:43
  • 1
    I don't get you. Does the code put in H1 the correct `proportion_width` value? – user3598756 Feb 20 '17 at 07:48
  • @user402429, did you get through it? – user3598756 Feb 20 '17 at 08:04
  • I guess it appears the number zero at H1 and when this sub module execution finishes it didappears – victorwestmann Feb 20 '17 at 09:34
  • 1
    Then it means that at the time the macro runs 'proportion_width' has not been assigned a value or it has been assigned a "zero". Check where you assign a value to 'proportion_width' – user3598756 Feb 20 '17 at 09:43
  • 1
    And, please, eventually take your time to give proper feedback (accept and/or upvote answers) to people trying at helping you as I saw you didn't do in any of your previous question. Thank you. – user3598756 Feb 20 '17 at 09:45
  • Sorry about not upvoting your questions. This is now fixed. The proportion_value is not completely ZERO value. It starts with zero (e.g. 0.78349). Is this a problem for Excel ? Thanks for your help so far. – victorwestmann Feb 21 '17 at 05:33
  • Here is the .xlsm file if you want to further inspect it. Thank you! https://mega.nz/#!4lA0SSTa!f_b8_qF37Qj64WBKD40zq5L_PCGD96m3CgqrZy4GBdk – victorwestmann Feb 21 '17 at 05:52
  • 1
    I don't get you: `0.78349` is NOT zero! And I'd _never_ download any xlsm file from the web. Just step through the code: while in VBA IDE place the cursor in any valid statement and press F8 to start the macro and have the next statement to be executed highlighted in yellow. At any time you can use the Immediate Window (CTRL+G to pop it out) to query your relevant variables by typing, say ,`?proportion_width` an pressing return and see the outcome. You can also switch between Excel UI and VBA IDE to see what's going on – user3598756 Feb 21 '17 at 07:22
  • You wrote _"It starts with zero (e.g. 0.78349)"_ : you can't blame me for thinking you were taking `0.78349` as a "zero"! That said I tested my code before posting it and it worked. Step through your code (as per my previous comment _instructions_) and see what's actually happening. What's for sure is that my code takes for granted that `proportion_width` is a non zero divisor but its setting is out of the same code reach while it belongs to your code previously setting it. Be sure `proportion_width` is <> 0 (add a line `proportion_width=1.2` right before my code) and test it yourself – user3598756 Feb 22 '17 at 15:22