Using Cases in Code

  • Thread starter Thread starter David
  • Start date Start date
D

David

I am having a little trouble with a nested if statement
that is first looking for the last empty cell, then it is
saying that if combobox1 is equally to "Airguard", then
place the value of textbox1 in to B(lastcell). if
combobox1 is equally to "ETC..", then place the value of
textbox1 into C(last cell) and so on. Is there a way to do
this as Cases, such as Case1, Case2, etc.

Any input, or direction to places where I can find info.
I appreciate it.
David
 
Try:

' declare variables
Dim strCol as String
Dim strSeeker as String
Dim i as Integer
Dim j as Integer

' determine column based on value
' selected in combo box
Select Case ComboBox1
Case "Airguard"
strCol = "B"
Case "Etc"
strCol = "C"
Case Else
strCol = "D"
End Select

' find last record/first empty cell
i = 1
j = 0
Do Until j < 0
strSeeker = Range(strCol & i).Text
If IsNull(strSeeker) or strSeeker = "" Then
j = i
Exit Do
Else
i = i + 1
End If
Loop

' populate cell with value from text box
Range(strCol & j).FormulaR1C1 = TextBox1.Text

Hope this helps!

Howard Brody
 
Back
Top