For Loop with Range

[mendreks]

Hi, I was wondering if someone could please give me a little help. I want to interate through the first column (A4:A20) and if the value of the cell is equal to a certain text then set the value of a specific variable equal to the numeric value of column B right next to that text. I know my sytax/code is wrong in the if statements.


Dim Bvar As Integer
Dim Zvar As Integer
Dim SSvar As Integer
Dim BLvar As Integer

For Each i In Worksheets("Sheet1").Range("A4:A20").Cells
If i.Value = "North" Then Bvar = ActiveCell.FormulaR1C1 = "=RC[1]"
ElseIf i.Value = "South" Then
Set Zvar = ActiveCell.FormulaR1C1 = "=RC[1]"
ElseIf i.Value = "East" Then
Set SSvar = ActiveCell.FormulaR1C1 = "=RC[1]"
ElseIf i.Value = "West" Then
Set BLvar = ActiveCell.FormulaR1C1 = "=RC[1]"
Exit For
End If
Next i

 

[mirabeau]

Hi, I was wondering if someone could please give me a little help. I want to interate through the first column (A4:A20) and if the value of the cell is equal to a certain text then set the value of a specific variable equal to the numeric value of column B right next to that text. I know my sytax/code is wrong in the if statements.

Dim Bvar As Integer
Dim Zvar As Integer
Dim SSvar As Integer
Dim BLvar As Integer
                 
For Each i In Worksheets("Sheet1").Range("A4:A20").Cells
        If i.Value = "North" Then Bvar = ActiveCell.FormulaR1C1 = "=RC[1]"
        ElseIf i.Value = "South" Then
            Set Zvar = ActiveCell.FormulaR1C1 = "=RC[1]"
        ElseIf i.Value = "East" Then
            Set SSvar = ActiveCell.FormulaR1C1 = "=RC[1]"
        ElseIf i.Value = "West" Then
            Set BLvar = ActiveCell.FormulaR1C1 = "=RC[1]"
            Exit For
        End If
    Next i

The code looks a bit odd to me.

You define (declare or Dim) say Zvar as integer, but later you Set Zvar = something. Setting something means defining or assigning a worksheet object, but you can't make part of a worksheet equal to an integer. You may however make its value equal to an integer.

Without analysing your code too closely, would leaving out the bits like that colored red make any difference?

I used the code tags for your posted code, which makes it much easier to read.

 

[mendreks]

Dim Bvar As Integer
Dim Zvar As Integer
Dim SSvar As Integer
Dim BLvar As Integer

For Each i In Worksheets("Sheet1").Range("A4:A20").Cells
If i.Value = "North" Then Bvar = i.ValueRC[1]
ElseIf i.Value = "South" Then Zvar =
ElseIf i.Value = "East" Then SSvar = ActiveCell.FormulaR1C1 = "=RC[1]"
ElseIf i.Value = "West" Then BLvar = ActiveCell.FormulaR1C1 = "=RC[1]"
Exit For
End If
Next i</PRE>
Thank you, thats good to know. Sorry, i'm extremely new to VBA. So I removed the Sets. What is the best way to reference column B, because I know that I have errors. I was trying to use the FormulaR1C1 method to reference the column to the right, but I know I had syntax errors.

 

[mendreks]

.

 

[mendreks]

Finally figured it out. thanks for help though


Dim Bvar As Integer
Dim Zvar As Integer
Dim SSvar As Integer
Dim BLvar As Integer
Dim i As Range

For Each i In Worksheets("Sheet1").Range("A4:A20")
If i.Value = "north" Then
Bvar = i.Offset(0, 1).Value
ElseIf i.Value = "south" Then
Zvar = i.Offset(0, 1).Value
ElseIf i.Value = "east" Then
SSvar = i.Offset(0, 1).Value
ElseIf i.Value = "west" Then
BLvar = i.Offset(0, 1).Value
Exit For
End If
Next i