Drop Down List Value?

[K4rm4]

How do I test the value of a listbox that I have in a cell? "row" is a running variable inside a do while loop.


Range("row,2").Validation _
.Add xlValidateList, xlValidAlertStop, xlBetween, "=$O$4:$O$7"

If Range(row, 2).Value = "Plate" Then

Cells(row, 9) = Cells(row, 5) * Cells(row, 6) * Cells(row, 7) * Cells(row, 8)

End If

 

[joerd]

The cell's value is the value of the listbox, so your code looks OK to me, except for a syntax error in the first line: don't use quotes around the row,2 in the Range() statement:
[tt]Range(row,2).Validation _
.Add xlValidateList, xlValidAlertStop, xlBetween, "=$O$4:$O$7"[/tt]


Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[K4rm4]

Hmmm its still not working. I think the range function beleives its two different cells with just a comma inbetween. The .value function is also not highlighted when I enter the code which leads me to believe I have the incorrect function. Here is my full code in case its something other than the value.

Sub AutoWeight()
'
' AutoWeight Macro
' Macro recorded 10/31/2006 by jwilliamson
'
' Keyboard Shortcut: Ctrl+w
'

Dim x As Integer
Dim row As Integer
Dim col As Integer

x = Cells(16, 3) 'currently at 50
row = 4

Do While row <= x

Range(row, 2).Validation _
.Add xlValidateList, xlValidAlertStop, xlBetween, "=$O$4:$O$7"

If Range(row, 2).Value = "Plate" Then

Cells(row, 9) = Cells(row, 5) * Cells(row, 6) * Cells(row, 7) * Cells(row, 8)
End If

If Range(row, 2) = "Angle Bar" Then

Cells(row, 9) = (Cells(row, 6) + Cells(row, 7)) * Cells(row, 5) * Cells(row, 8)
End If

If Range(row, 2) = "Round Bar" Then

Cells(row, 9) = Cells(row, 5) * Cells(row, 3) ^ 2 * Pi / 4 * Cells(row, 8)

End If

If Range(row, 2) = "Tube" Then

Cells(row, 9) = (Cells(row, 3) ^ 2 * 3.1415 / 4 - Cells(row, 4) ^ 2 * 3.1415 / 4) * Cells(row, 5) * Cells(row, 8)
End If

row = row + 2

Loop

End Sub

 

[handleman]

"Range" and "Cells" do not use the same arguments. "Range" requires a string with A1 style referencing. In your case, instead of

Range(row, 2) 'invalid

you would use

Range("B" & row) 'valid

However, when referring to a single cell, the easiest way is usually with "Cells", which requires a row number integer, comma, and column number integer, as in

Cells(row, 2) 'also valid

If you replace each instance of "Range" with "Cells" your code it should work fine.

 

[joerd]

Got it.
Instead of Range(row, 2) you need to use Cells(row, 2). Range will accept only a string as an argument, as in Range("P12").
While we're at it, I can't help giving you some tips:
I would use [tt]For row = 4 To x Step 2[/tt] instead of a Do...Loop construction.
I would set up the validation only once, inside the spreadsheet, not in the code. You'll find out why when you run the code multiple times.
I like to explicitly use the .Value property when you access the value of a cell, so instead of [tt]Cells(row, 9) =[/tt], I would use [tt]Cells(row, 9).Value =[/tt].
Instead of the multiple If's a Select Case statement is much more structured:
[tt] Select Case Cells(row, 2).Value
Case "Plate"
Cells(row, 9) = Cells(row, 5) * Cells(row, 6) * Cells(row, 7) * Cells(row, 8)
Case "Angle Bar"
Cells(row, 9) = (Cells(row, 6) + Cells(row, 7)) * Cells(row, 5) * Cells(row, 8)
Case "Round Bar"
Cells(row, 9) = Cells(row, 5) * Cells(row, 3) ^ 2 * Pi / 4 * Cells(row, 8)
Case "Tube"
Cells(row, 9) = (Cells(row, 3) ^ 2 * 3.1415 / 4 - Cells(row, 4) ^ 2 * 3.1415 / 4) * Cells(row, 5) * Cells(row, 8)
Case Else
'if you want to trap invalid input
End Select[/tt]

Happy programming!

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[K4rm4]

Ok so it works but only for the top cell or row 4 its not filtering down. The rows I am now using are 4,7,10,13,16 ect, so I am using a step 3 instead of 2. Here is the new code. Anyone know why this won't filter down correctly?

Sub AutoWeight()
'
' AutoWeight Macro
' Macro recorded 10/31/2006 by jwilliamson
'
' Keyboard Shortcut: Ctrl+w
'

Dim x As Integer
Dim row As Integer

x = Cells(16, 3)
row = 4


For row = 4 To x Step 3

Select Case Cells(row, 2).Value

Case "Plate"

Cells(row, 9).Value = Cells(row, 5) * Cells(row, 6) * Cells(row, 7) * Cells(row, 8)

Case "Angle Bar"

Cells(row, 9).Value = (Cells(row, 6) + Cells(row, 7)) * Cells(row, 5) * Cells(row, 8)

Case "Round Bar"

Cells(row, 9).Value = Cells(row, 5) * Cells(row, 3) ^ 2 * 3.1415 / 4 * Cells(row, 8)

Case "Tube"

Cells(row, 9).Value = (Cells(row, 3) ^ 2 * 3.1415 / 4 - Cells(row, 4) ^ 2 * 3.1415 / 4) * Cells(row, 5) * Cells(row, 8)

End Select

Next
End Sub

 

[handleman]

One way to help debug your code and see what's going on is by using message boxes. For example, if you want to see whether the value of "row" is getting incremented correctly you can add the line

MsgBox row

inside the "for" loop. You could also add (as Joerd suggested):

Case Else
MsgBox "Unexpected value in row " & row

The reason for this is that if the value doesn't match one of your cases it won't do anything.

One possibility is that your case doesn't match. According to VBA, the strings "Plate" and "plate" are different. I'm not sure how your validation is set up, but it may be that your cells contain strings that the validation allows but don't match the case of the strings in your code.

 

[joerd]

What do you mean by "filtering down"? We were talking about validation, weren't we?

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[K4rm4]

Validation is taken care of, all of the cases work but they only work in the first row. It doesn't fill the rest of the spreadsheet, only the first row. I'll try the message boxes and see if I can figure it out. Thanks.

 

[K4rm4]

Hahaha great idea for the message boxes. It turns out I just didn't have enough coffe this morning and had the row/column switched for variable x.

Thanks for all the help guys.