asked on

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

ASKER CERTIFIED SOLUTION

Shanan212

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

deskchains

ASKER

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

deskchains

ASKER

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

deskchains

ASKER

Just changed all my Ifs' following the first one to ElseIf - no error, but no records are written either.

deskchains

ASKER

Thanks so much...

deskchains

ASKER

changed the irow statement to:

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

and it worked

Shanan212

Glad it worked. Sorry I was away from computer for a while :)