Newbie vba if question

[zealottoyou]

Hi, here is an example of what i try to do. If the cell value is 1 then the cell 2 column to the right will return 1, if not it should return 2. However I just can't get it to work. Any help is greatly appreciated.

For p = 12 To 20
For i = 12 To 20
        If Cells(i, "Q").Value = "1" Then
        
            Cells(p, "S").Value = "1"
    
        Else
            Cells(p, "S").Value = "2"
        
        End If
        
Next i
Next p
End Sub

 

[VoG]

Try without the ""

For p = 12 To 20
For i = 12 To 20
        If Cells(i, "Q").Value = 1 Then
        
            Cells(p, "S").Value = 1
    
        Else
            Cells(p, "S").Value = 2
        
        End If
        
Next i
Next p
End Sub

 

[AlphaFrog]

If Q and S are on the same row, you only need the one For-Next loop.

[color=darkblue]For[/color] i = 12 [color=darkblue]To[/color] 20
    [color=darkblue]If[/color] Cells(i, "Q").Value = 1 [color=darkblue]Then[/color]
        Cells(i, "S").Value = 1
    [color=darkblue]Else[/color]
        Cells(i, "S").Value = 2
    [color=darkblue]End[/color] [color=darkblue]If[/color]
[color=darkblue]Next[/color] i

 

[zealottoyou]

Hi Alpha, this is only an example, they are on different rows so I would need two For-Next.

thx.



Vog, the code still return 1 on column S no matter what the value I have on Q.

 

[Rick Rothstein]

ADVERTISEMENT

Try without the ""

For p = 12 To 20
  For i = 12 To 20
    If Cells(i, "Q").Value = 1 Then
      Cells(p, "S").Value = 1
    Else
      Cells(p, "S").Value = 2
    End If
  Next i
Next p

While I think VoG's code is probably easier to understand, I would note that the code highlighted red can be replaced by this single line of code...

Cells(p, "S").Value = 2 + (Cells(i, "Q").Value = 1)

 

[VoG]

I don't understand why you have two loops.

Also, this is probably solvable using a formula.

 

[zealottoyou]

It was just my test code but I just couldn't figure out why it doesn't work. Here is what I plan to use it for. Sorry should have post the entire thing in the first place:

        For I = 12 To 151
        
        If Sheets("Fund").Cells(I, 17) = "1" Then
        
            For p = 1 To 150
            For T = 1 To 260
        
            If Sheets("Fund").Cells(p + 11, 4) * Sheets("Fund").Cells(p + 11, 5) - Sheets("Fund").Cells(p + 11, 8) * Sheets("Fund").Cells(p + 11, 9) * T > 0 Then
                Sheets("Backup").Cells(p + 1, T + 1) = Sheets("Fund").Cells(p + 11, 4) * Sheets("Fund").Cells(p + 11, 5) - Sheets("Fund").Cells(p + 11, 8) * Sheets("Fund").Cells(p + 11, 9) * T
            Else
                Sheets("Backup").Cells(p + 1, T + 1) = 0
            End If
    
            If Sheets("Fund").Cells(p + 11, 4) * Sheets("Fund").Cells(p + 11, 5) - Sheets("Fund").Cells(p + 11, 8) * Sheets("Fund").Cells(p + 11, 9) * T > 0 Then
                    Sheets("Backup").Cells(p + NumberofRows + 4, T + 1) = Sheets("Fund").Cells(p + 11, 4) * Sheets("Fund").Cells(p + 11, 5) - Sheets("Fund").Cells(p + 11, 8) * Sheets("Fund").Cells(p + 11, 9)  * T
            Else
                Sheets("Backup").Cells(p + NumberofRows + 4, T + 1) = 0
            End If
        Next T
        Next p
       
        End If
        Next I

 

[AlphaFrog]

Hi Alpha, this is only an example, they are on different rows so I would need two For-Next.

The way you've nested the two For-Next loops in your original code would mean that all S rows are set depending on Q20 only because the last p loop is 20 and i is dependent on P.

I don't follow what you're trying to achieve though.