Getting VBA to loop through an array

Question

I have a database with a set of numbers from 1~100. I would like VBA to run through this list in column A and, on areas where A is between 20 and 40, type McFly on column B.

The code I have gives me a mismatch error 13 on the line that starts with "If list >="

Dim list As Range
Dim list_readthru As Range
Set list = Range("A2", Range("A50").End(xlUp))
For Each list_readthru In list
If list >= 20 And list <=40 Then Range("B:B") = "McFly"
Next list_readthru
End Sub

I am having problems identifying what I am doing wrong.

I think you want list.value but range("B:B") needs more work... — findwindow
– findwindow, Commented Oct 5, 2015 at 22:34

SierraOscar · Accepted Answer · 2015-10-05 22:42:22Z

2

If you want an array, you can have an array:

Sub MM()

Dim list, i

list = WorksheetFunction.Transpose(Range("A2", [A50].End(xlUp)))

For i = 1 To UBound(list)
   If list(i) >= 20 And list(i) <= 40 Then Cells(i + 1, 2).Value = "McFly"
Next

End Sub

However I don't see how this is any different from just putting a formula in column B:

With Range("B2", [A50].End(xlUp))
    .FormulaR1C1 = "=IF(AND(RC[-1]>=20,RC[-1]<=40),""McFly"","""""")"
    .Value = .Value
End With

answered Oct 5, 2015 at 22:42

SierraOscar

17.7k6 gold badges44 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tim · Accepted Answer · 2015-10-05 22:35:04Z

1

You dim list as a Range object (not an array), so work with it as a range. Eg, list.value Same goes for list_readthru.

answered Oct 5, 2015 at 22:35

Tim

2,9123 gold badges30 silver badges51 bronze badges

Comments

Brumder · Accepted Answer · 2015-10-05 22:41:06Z

1

You've got a few things mixed up here. Try this:

Dim list As Range
Dim list_readthru As Range
Set list = Range("A2", Range("A50").End(xlUp))
For Each list_readthru In list
    If list_readthru >= 20 And list_readthru <=40 Then 
        Cells(list_readthru.row, 2) = "McFly"
    End If
Next list_readthru
End Sub

answered Oct 5, 2015 at 22:41

Brumder

2123 silver badges10 bronze badges

Collectives™ on Stack Overflow

Getting VBA to loop through an array

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related