CODEWITHSUNDEEP
vbaexcel
Rachel Chia
Rachel Chia

Reputation: 259

Declaring number of variable based on the value of a variable

I'm trying to compare n numbers to see which one is the minimum value. n is a value from a cell 

n = Sheet1.Range("C6").Value

So if n is equal to 2 then I have to declare two variables and use min function. The data will be arranged in two different columns as shown below. I would have no idea where the data ends down. 

 1    2 
 3    4 
 '    '
 '    '

I want to use array to declare the variables based on n and with the loop but I don't know how and I am not familiar with array in VBA. I am open to all solutions. You don't have to use array but that is one of the way I thought of using. Currently, I have only done it by getting the two values which I have already declared. I need to use the loop to get two different values for every row to compare and find the minimum value. There are no error with my codes. 

x = 1
Do Until IsEmpty(Sheet9.Cells(x, 30).Value)
    no2 = Sheet9.Cells(x, 30).Value
    no1 = Sheet9.Cells(x, 31).Value
    result = WorksheetFunction.Min(no1, no2)
    Sheet9.Cells(x, 30).Value = result
    x = x + 1
Loop

Upvotes: 0

Views: 250

Answers (2)

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9976

If I understood your question, you just need to add one more condition in the Do Until Loop like this...

X = 1
Do Until IsEmpty(Sheet9.Cells(X, 30).Value) Or X > Sheet1.Range("C6").Value
    no2 = Sheet9.Cells(X, 30).Value
    no1 = Sheet9.Cells(X, 31).Value
    result = WorksheetFunction.Min(no1, no2)
    Sheet9.Cells(X, 30).Value = result
    X = X + 1
Loop

Upvotes: 1

Sivaprasath Vadivel
Sivaprasath Vadivel

Reputation: 541

Let the Cell containing the Number of values to consider be 'C6'.

If you have a value of 3 in 'C6' then you have find the minimum of the cells 'A30:A32' and paste the value in 'A30' this is what i have understood from your question.

Sub Minarr()
dim nos() as integer

n=int(sheet1.range('C6').value)
set cell=Sheet9.cells(1,30)
if not isempty(cell) then
for i=0 to n
nos[i]=cell.offset(i,0).value
next i
result=Application.worksheetfunction.min(nos)
cell.value=result
end if
end sub

Upvotes: 0

Related Questions