# Newbie vba if question

#### zealottoyou

##### New Member
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.

Code:
``````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``````

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

#### VoG

##### Legend
Try without the ""

Code:
``````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

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

Code:
``````[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

##### New Member
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.

Last edited:

#### Rick Rothstein

##### MrExcel MVP
Try without the ""

Rich (BB code):
``````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...
Code:
``Cells(p, "S").Value = 2 + (Cells(i, "Q").Value = 1)``

#### VoG

##### Legend
I don't understand why you have two loops.

Also, this is probably solvable using a formula.

#### zealottoyou

##### New Member
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:

Code:
``````        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

##### MrExcel MVP
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.

Replies
1
Views
263
Replies
4
Views
303
Replies
13
Views
879
Replies
1
Views
292
Replies
0
Views
360

1,195,831
Messages
6,011,843
Members
441,650
Latest member
ceyoung75

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back