# Loop or array or both? to assign value

Hi experts,

This should be easy, but again i don't have the knowledge to pull it off...

In the attached example col A lists a number of samples, cols B - BB list the results of tests done to the sample.  What I would like to do is perform a bunch of if, then, else statements to assign a value to col BC.  for example

if cell B3 >=0.5 then cell bc3 = textvalue1
else
if cell C3 >=.45 then cell bc3 =  textvalue2
else
if cell C3+B3>cell d3 then cell bc3 =  textvalue3
else
if cell K3+Q3+O3 >=1 then cell bc3 = textvalue4
else....
so on and so forth until im out of if then(s)

once it assigns a value to cell BC3 then it would move down to row 4 to assign a value to BC4 using the same if, then, else all the way down until the last record.  The number of rows will be variable, could be 5 like the example, could be 500+.

Provided an example I am confident that i could handle all the if then else(s).  Assigning the value/result to the cell(s) and handling "variable" number of records is beyond my abilities...
Example.xlsx
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Here is an example

``````if range("B3").value >=0.5 then

range("BC3").value = "textvalue1"

else
....
end if
``````

so you can call a value in a cell as

range("B3").value

If you want to add mutliple cells then it would be

``````if range("B3").value + range("C3").value + range("D3").value > 3 then
``````

You can assign a text value inside quotes. For number valeus, you don't need quotes.

I would recommend formulas for this. But if you insist macros, then you can try something like this

in here, you can see I am using

``````range("B" & i).value
``````

The 'i' is a variable and would change for each row.

So lets say if you want to add multiple variables considering that i = 3 (3rd row)

``````if range("B" & i).value + range("C" & i).value + range("D" & i).value > 3 then
``````

``````Sub sample()

Dim iRow as long, i as long

iRow = activesheet.Cells(Rows.Count, 2).End(xlUp).Offset(0, 0).Row

for i = 3 to irow

if range("B" & i).value >=0.5 then

range("BC" & i).value = "textvalue1"

else
....
end if

next i

End sub
``````
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
Worked like a champ...until i put it into my real workbook.  I added about half of the if/then statements and then decided to test it. Now I get a "compile error - next without for" error.  Here's what i entered:

Sub SetCov()

Sheets("StandSppBA").Select

Dim iRow As Long, i As Long
Dim NH As Double
Dim NM As Double
Dim Asp As Double
Dim SH As Double
Dim SC As Double

iRow = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Offset(0, 0).Row

For i = 5 To iRow

NH = Range("BG" & i).Value + Range("AJ" & i).Value
NM = Range("BJ" & i).Value + Range("BH" & i).Value + Range("BC" & i).Value + Range("BD" & i).Value + Range("AS" & i).Value + Range("AI" & i).Value
Asp = Range("L" & i).Value + Range("M" & i).Value + Range("N" & i).Value + Range("O" & i).Value
SH = Range("BJ" & i).Value + Range("BC" & i).Value + Range("AE" & i).Value + Range("AK" & i).Value + Range("M" & i).Value
SC = Range("AB" & i).Value + Range("Z" & i).Value + Range("Y" & i).Value + Range("S" & i).Value + Range("R" & i).Value + Range("Q" & i).Value

If Range("W" & i).Value >= 0.5 Then

Range("BM" & i).Value = "PR"

Else

If Range("AA" & i).Value >= 0.5 Then
Range("BM" & i).Value = "PW"

Else

If Range("T" & i).Value >= 0.5 Then
Range("BM" & i).Value = "PJ"

Else

If Range("BD" & i).Value >= 0.5 Then
Range("BM" & i).Value = "OR"

Else

If Range("BA" & i).Value >= 0.5 Then
Range("BM" & i).Value = "BW"

Else

If Range("BJ" & i).Value >= 0.5 Then
Range("BM" & i).Value = "BY"

Else

If Range("AB" & i).Value + Range("Q" & i).Value > 0.35 And Range("Q" & i).Value > 0.1 And Range("AB" & i).Value > 0.1 And Range("Q" & i).Value + Range("AB" & i).Value + Asp + Range("BA" & i).Value > 0.6 And NM < 0.3 Then
Range("BM" & i).Value = "FS" 'SPRUCE FIR

Else

Range("BM" & i).Value = "unk"

End If

Next i

End Sub
0
Author Commented:
Continued from above...

I put in a bunch of end ifs and the error went away, but I don't get any results put into cell BM (i):

Sub SetCov()

Sheets("StandSppBA").Select

Dim iRow As Long, i As Long
Dim NH As Double
Dim NM As Double
Dim Asp As Double
Dim SH As Double
Dim SC As Double

iRow = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Offset(0, 0).Row

For i = 5 To iRow

NH = Range("BG" & i).Value + Range("AJ" & i).Value
NM = Range("BJ" & i).Value + Range("BH" & i).Value + Range("BC" & i).Value + Range("BD" & i).Value + Range("AS" & i).Value + Range("AI" & i).Value
Asp = Range("L" & i).Value + Range("M" & i).Value + Range("N" & i).Value + Range("O" & i).Value
SH = Range("BJ" & i).Value + Range("BC" & i).Value + Range("AE" & i).Value + Range("AK" & i).Value + Range("M" & i).Value
SC = Range("AB" & i).Value + Range("Z" & i).Value + Range("Y" & i).Value + Range("S" & i).Value + Range("R" & i).Value + Range("Q" & i).Value

If Range("W" & i).Value >= 0.5 Then

Range("BM" & i).Value = "PR"

Else

If Range("AA" & i).Value >= 0.5 Then
Range("BM" & i).Value = "PW"

Else

If Range("T" & i).Value >= 0.5 Then
Range("BM" & i).Value = "PJ"

Else

If Range("BD" & i).Value >= 0.5 Then
Range("BM" & i).Value = "OR"

Else

If Range("BA" & i).Value >= 0.5 Then
Range("BM" & i).Value = "BW"

Else

If Range("BJ" & i).Value >= 0.5 Then
Range("BM" & i).Value = "BY"

Else

If Range("AB" & i).Value + Range("Q" & i).Value > 0.35 And Range("Q" & i).Value > 0.1 And Range("AB" & i).Value > 0.1 And Range("Q" & i).Value + Range("AB" & i).Value + Asp + Range("BA" & i).Value > 0.6 And NM < 0.3 Then
Range("BM" & i).Value = "FS"

Else

Range("BM" & i).Value = "unk"
End If
End If
End If
End If
End If
End If
End If

Next i

End Sub
0
Author Commented:
Just changed all my Ifs' following the first one to ElseIf - no error, but no records are written either.
0
Author Commented:
Thanks so much...
0
Author Commented:
changed the irow statement to:

iRow = Range("K" & Rows.Count).End(xlUp).Row

and it worked
0
Commented:
Glad it worked. Sorry I was away from computer for a while :)
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.