How to check range value in VBA?

How to refer to a range in VBA?

I have a worksheet with about 20 columns, I need to check if the cell in the first column is filled with "Yes" or "No", if it's filled with "No", I need to set the value of the cell in column (2,5) to "red". I am using: if Cells(2,i).value="yes" then Cells(2,i).value="red"
But I keep getting this error: Run-time error '13': Type mismatch. You're using the VBA statement. If Cells(2, i).value = "yes" Then .

The Cells() function requires a worksheet reference, which in this case you are not supplying. Also, the error you're getting is because you are trying to use the value property on a Range.

To fix the error: Dim ws as Worksheet. Set ws = ActiveWorkbook.Worksheets("Sheet1") if ws.Cells(2, i).value = "yes" then
ws.Cells(2, i).Interior.Color = RGB(255,0)
End if. Another thing to be aware of is that Excel will evaluate Range objects for any non-empty cell, even if you specify that you only want to check for a specific value. In your case, there is no need for the If statement, but you can still set the color of the cell.

Set ws = ActiveWorkbook.Worksheets("Sheet1") ws.Cells(2, i).

How to set range variable in VBA?

I have a code where I need to set a range variable as.

Dim As Range. Set A = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
I need to set this variable with specific range as I dont know how many rows in column A? Just use Cells instead of Cells(Rows.Count, "A").End(xlUp).Row:
Set A = Range("A1:A" & Cells.End(xlDown).Row)

Also, if you want to set the A range to be the same size as the last row in column A, you can change the last line to: Set A = Range("A1:A" & Cells.End(xlDown).Row - 1)
Finally, if you want to be more explicit about your range selection, you can use: Set A = Range("A1:A" & Cells(Cells.End(xlDown).Row - 1, "A").End(xlDown).

How do you write a range of numbers in VBA?

I am using excel and I am trying to create a VBA to read a range of numbers in from a text file.

My current code is: Sub ReadRange()r. Dim arr As Variantr. Dim lastRow As Longr. Dim I As Longr. LastRow = Range("A1").End(xlDown).Rowr
Arr = Range("A1:A" & lastRow)r. For I = LBound(arr) To UBound(arr)r. Debug.Print i, arr(i)r Next ir. End Sub. My problem is that the array will only hold values from "A1" to "A" without being able to go past it. If I remove the "A" in the line, it just returns an error.

I have also tried using a For I = 1 to lastRow but this doesn't work either. This works (VB.Net): Sub ReadRange(). Dim arr() As Double. Dim s As String. Dim rng As Range. 'Open the file. OpenTextFile.OpenTextFile strFile,, True,, "ReadOnly" 'Loop until the file is closed. Do While Not OpenTextFile.AtEndOfStream s = OpenTextFile.ReadLine arr = Split(s, ",")