# Quick Simple Newbie Question

#### Alfred12

##### New Member
Hi all --

Can someone please tell me what is wrong with this line of code:

If Cells(i, 4).Value = 1 Then

This line appears in a for next loop being counted by the variable "i". The idea is to look at the value in column D in all the rows, and if the cell contains the number 1, the "then" part is executed.

It compiles fine, and I've got messages inserted so that I know that this line is being executed. But it is not finding my "1"s to trigger the "then" action. (And in my test cases I certainly have some "1"s in the D column.) What super-basic obvious thing am i missing?

It is as if there is no tutorial basic enough for me!

Thanks!

Alfred

### Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

#### Dryver14

##### Well-known Member
is the value of i increasing in each loop

#### Alfred12

##### New Member
Yes, I've confirmed that.

#### Dryver14

##### Well-known Member
forgive me for asking but i have not seen the code

do you have the line for each cell in selection?

#### Alfred12

##### New Member
Thanks much for helping Dryver.

I am enough of a noob that I do not understand your question. Can you please elaborate / dumb it down a level?

#### Dryver14

##### Well-known Member
can we see the code?

#### Alfred12

##### New Member
Here is the whole thing, if context is helpful:

Dim S As Integer
Dim i As Integer
MsgBox "Hi!"
For S = 2 To Sheets.Count
Worksheets(S).Activate
For i = 1 To 5
If Cells(i, 4).Value = 1 Then
MsgBox "Hi Again"
If Cells(i, 5).Value = “yes” Then
Range(Cells(i, 1)).Copy
Sheets(“Master”).Range(“a65536”).End(x1Up).Offset(1, 0).Paste
Range(Cells(i, 4)).Copy
Sheets(“Master”).Range(“a65536”).End(x1Up).Offset(0, 1).Paste
Range(Cells(i, 6)).Copy
Sheets(“Master”).Range(“a65536”).End(x1Up).Offset(0, 2).Paste
Range(Cells(i, 7)).Copy
Sheets(“Master”).Range(“a65536”).End(x1Up).Offset(0, 3).Paste
Range(Cells(i, 8)).Copy
Sheets(“Master”).Range(“a65536”).End(x1Up).Offset(0, 4).Paste
End If
End If
Next i
Next S
End Sub

(Sorry, not sure how to maintain indenting in the post.)

#### Dryver14

##### Well-known Member
Ive indented the 2 lines to enter this only looks down 5 rows is that right?

If it where it says i = 1 to 5 change 5 to a number that covers all the rows

Code:
``````Sub WhatEver()
Dim S As Integer
Dim i As Integer
MsgBox "Hi!"
For S = 2 To Sheets.Count
Worksheets(S).Activate
For Each cell In Selection
For i = 1 To 5
If Cells(i, 4).Value = 1 Then
MsgBox "Hi Again"
If Cells(i, 5).Value = “yes” Then
Range(Cells(i, 1)).Copy
Sheets(“Master”).Range(“a65536”).End(x1Up).Offset(1, 0).Paste
Range(Cells(i, 4)).Copy
Sheets(“Master”).Range(“a65536”).End(x1Up).Offset(0, 1).Paste
Range(Cells(i, 6)).Copy
Sheets(“Master”).Range(“a65536”).End(x1Up).Offset(0, 2).Paste
Range(Cells(i, 7)).Copy
Sheets(“Master”).Range(“a65536”).End(x1Up).Offset(0, 3).Paste
Range(Cells(i, 8)).Copy
Sheets(“Master”).Range(“a65536”).End(x1Up).Offset(0, 4).Paste
End If
End If
Next i
Next cell
Next S
End Sub``````

#### Alfred12

##### New Member
I lowered the row count to 5 just for testing purposes. When I see it functioning properly I'll put that back up high.

I entered the text you suggested with no change in results. Sadly.

Is there something else wrong with the If Then statement? Do I need to use "Range" instead of "Cells"? Is it proper to have the ".Value" where I've got it? Should I replace the "1" with a variable set to equal 1?

Thanks yet again.

Alfred

#### Dryver14

##### Well-known Member
I only tested it using F8 to see if i could get the second message box to come up if a 1 was in a cell.

What is not working?

Replies
4
Views
1K
Replies
3
Views
315
Replies
3
Views
513
Replies
2
Views
370
Replies
8
Views
648

1,190,880
Messages
5,983,374
Members
439,841
Latest member
goodwillhunting

### 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