I am starting to learn VBA for Excel for fun and I have a question. I am writing a form module Activate with the user form containing a list box. The list is supposed to capture all data in one column of a spreadsheet when the user form is activated.
Everything works but only if the range I select is of the type Range("A1:A100) for example. If I try to use the Columns("A:A") range it gives me a run-time error 13 type mismatch. I gather this is because the range has infinite characters and then a for/each
loop is used. I presume that if I use a Do until loop it will work, although I haven't tried. I am using the For/each loop because this is what the text book I am following proposes for this exercise.
Could anybody give me a bit more insight into this? I have copied below the code in the activate module.
Many thanks in advance.
Dim columnA As Range Dim cA As Range Dim checkValue As String Dim newItem As Variant
Private Sub UserForm_Activate()
Set columnA = Worksheets("Exercise 1").Range("A1:A100")
For Each cA In columnA
checkValue = CStr(cA.Value)
If checkValue <> "" Then newItem = cA.Value lstColumnA.AddItem (newItem) Else Exit Sub End If