0

First Question: So I'm sorry if the formatting etc is awful.

I have one very long column of integers and I need to multiply every element by a constant (ex:2) and fill the next column over.

I know how to make it work by looping through each element but I was hoping there was a single command for it. 

[D1:D25].Value = [C1:C25*2]

works for me, but it prints 0 where there were null spaces, so I need to define the range more specifically.

How can I define the both ranges in terms of variables and keep the multiplication functioning.

i.e. 

.Range(.cells(firstRow, 1),.cells(lrow,1))*2

gives a type mismatch error.

Cindy Meister
  • 25,071
  • 21
  • 34
  • 43
Stephen Bean
  • 1
  • 1
  • You'll have to more clearly define your ranges. Compute the [last row of data](https://stackoverflow.com/a/38882823/4717755) and establish the range of source data in a variable AND the range of the destination results. THEN use that defined range to perform your multiplication and assignments. – PeterT Jun 17 '19 at 21:12
  • @PeterT I know/find all the sizes before I perform the multiplication. I just don't know how to define the range with those sizes in a form that I can still multiply. – Stephen Bean Jun 17 '19 at 21:16
  • In truth, this technique is not used much. [This answer](https://stackoverflow.com/a/44602801/4717755) describes how it uses the [EVALUATE](https://learn.microsoft.com/en-us/previous-versions/office/developer/office-2003/aa223886(v=office.11)) method, which means Excel has to convert the string input to a range object, which can be more costly in terms of resources. IMO there's no real advantage to the notation except brevity, but for my work I'd write a loop to make sure the logic is doing exactly what I want in an efficient manner. – PeterT Jun 18 '19 at 13:36
  • @PeterT I was hoping to avoid a loop, but if there's no better way around it, then I can write the loop easily. Thanks for your responses – Stephen Bean Jun 18 '19 at 13:45

0 Answers0