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
deskchainsAsked:
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.

Shanan212Commented:
Here is an example

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

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

else
....
end if

Open in new window


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

Open in new window


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

Open in new window


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

Open in new window


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

Open in new window

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.

Start your 7-day free trial
deskchainsAuthor 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
deskchainsAuthor 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
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

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

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

and it worked
0
Shanan212Commented:
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.