VBA range reference

[on_an_island]

Hello,


I'm running a knapsack type of problem with my data located in multiple columns (i.e., A2:A10, B2:B:10, etc.) and can't figure out the correct argument to use that references the value in a cell for the algorithm to analyze and select or leave out. As currently configured, I can get the algorithm to work if I use the Range("cell").value argument. However, this only references the value in that specific cell and does not work on a range when used like this: Range("A1:A10").value. Manually entering a reference for each cell would be too time consuming and make for very messy code. I'm looking for an argument that will read cells in a specified range (A1:A10) and reference the value in each respective cell as the algorithm needs to crunch each cells value and compare it to others (see sample of code below).


Dim limit As Double, weight As Double,
Dim i, j As Integer

Dim weighti, weightj As Integer
Dim valuei, valuej As Integer


weighti = Range("C2").value <=== Range("C2:C10") will not work returning Run-Time Error '13' Type Mismatch error
weightj = Range("C3").value


valuei = Range("D2").value
valuej = Range("D3").value


For i = 0 To 1
weight = weighti * i + weightj * j <===number crunching based on referenced cell value
value = valuei * i + valuej






Any thoughts are appreciated!

 

[baitmaster]

Welcome to the board

First things first, when you write

dim i,j as integer

you're only declaring j as an integer, i is being declared but only as a variant (the default type). You need to write

dim i as integer, j as integer

In this case if you've done the same thing with weighti, which you want to be an integer value, but it can't be if you're referring to multiple cells. The only way it can be is to loop through them one at a time

So you'll need an extra loop, or to reconfigure the existing one:

dim x as integer
for x = 2 to 10
    weighti = cells(x,3).value
    weightj = cells(x+1,3).value
    valuei = cells(x,4).value

etc etc

If you don't want to use CELLS(..).value you can use e.g. RANGE("C" & x).value