I expect this is fairly simple...

[Peanutmc]

Hey gang,

Working on a few spreadsheets for a work project and in an effort to be more organized I find myself scratching my head on this one. It's been a while since I've done any excel macros so hopefully someone can help.

I'm trying to create a macro that will check for Ys in one worksheet, and, if all fields contain a Y, then an entry is filled in on another worksheet. Here is my code...

[vba]Sub Run()
Dim Multi As Range
Set Multi = Range("D3400")
For Each Row In Multi

Sheet1.Activate

If Row = "N" Then

Row.Offset(0, 1) = ""
Multi.Select
Multi.Offset(0, 1).Activate
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With

Row.Offset(0, 2) = ""
Multi.Select
Multi.Offset(0, 2).Activate
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With

Row.Offset(0, 3) = ""
Multi.Select
Multi.Offset(0, 3).Activate
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With

ElseIf Row = "Y" Then

Sheet2.Activate

If Row = "Y" And Row.Offset(0, 1) = "Y" And _
Row.Offset(0, 2) = "Y" And Row.Offset(0, 3) = "Y" And _
Row.Offset(0, 4) = "Y" And Row.Offset(0, 5) = "Y" And _
Row.Offset(0, 6) = "Y" And Row.Offset(0, 7) = "Y" Then

Sheet1.Activate

Row.Offset(0, 1) = "Y"
End If
End If
Next Row

End Sub[/vba]

[austenr]

First problem is that your code is looking for the variable Row in this line and its not defined, plus its a reserved word.

[vba]For Each Row In Multi [/vba]

Row is used as in the following:

[vba]Row.Offset(0, 3) = "" [/vba]

So in essence you are asking the macro to find the variable Row in the definded name range called Multi, Row is not defined. Turn on Option Explicit and then debug you will see what I mean.

Go to Tools>Options and under the first tab check Require Variable Declaration. Its in the VBE editor. This will catch things like this.

[Peanutmc]

Thanks, I couldn't figure out what part of my logic was incorrect.

I'll take what advice you've given me and try to get it working... if not I may have to ask again.

Also, your quote is amazing... glad to see that Red Green has made it to our neighbours (Canadian spelling!) down south.

[Peanutmc]

Well I've just about pulled out the few remaining strands of hair I have left trying to figure this one out. I'll try my best to explain my goals and hopefully someone can help tell me where I'm going wrong. I've literally spent way to long trying to figure out something that should be fairly simple.

Here are my issues...

I'm indifferent as to whether or not I use a loop. It would be nice to do without as it would be more efficient but i can't see how this would be possible otherwise.

I have 4 worksheets. The primary worksheet is called master and basically holds the really important stuff, all other sheets refer (are supposed to anyway) to this sheet.

On this master sheet, 1 column which takes a user entry of either Y/N should grey out the corresponding row on all 4 excel worksheets if the user selects N.

The other 3 column entries (Y/N) on the Master worksheet are determined by the entries from the other 3 work sheets. If all entries in the other sheet are Ys then the Master worksheet column updates to reflect this.

I've literally encountered most the errors in the book. Runtime errors have dominated...

I'm essentially back to the drawing board with this one... hopefully someone can shed some light, or at least tell me to take a break haha.

Cheers,
Cam

[Simon Lloyd]

Why not post the workbook so we can help you directly with that, in the meantime here's a shortened version of your code.[VBA]Sub Run()
Dim Multi As Range
Set Multi = Range("D3400")
For Each oRow In Multi

Sheet1.Activate

If oRow = LCase("N") Then

oRow.Offset(0, 1) = ""
Multi.Offset(0, 1).Interior.ColorIndex = 15
oRow.Offset(0, 2) = ""
Multi.Offset(0, 2).Interior.ColorIndex = 15
oRow.Offset(0, 3) = ""
Multi.Offset(0, 3).Interior.ColorIndex = 15

ElseIf oRow = LCase("Y") Then

Sheet2.Activate

If oRow = LCase("Y") And oRow.Offset(0, 1) = "Y" And _
oRow.Offset(0, 2) = LCase("Y") And oRow.Offset(0, 3) = LCase("Y") And _
oRow.Offset(0, 4) = LCase("Y") And oRow.Offset(0, 5) = LCase("Y") And _
oRow.Offset(0, 6) = LCase("Y") And oRow.Offset(0, 7) = LCase("Y") Then

Sheet1.Activate

oRow.Offset(0, 1) = LCase("Y")
End If
End If
Next oRow

End Sub[/VBA]