It's getting late and i have a report due tomorrow

[Castyel]

is there a way to reference a cell with a known column but a changing row.

ie

For Each i In Range("S47:S50")
If i = 1 Then
For Each cell In Sheets("sheet1").Range(D?,F?,H?,G?)

 

[Rick Rothstein]

ok that sounds great but I'm a little tired as its 1 am here.
how does it search each row in column S to find the those that have a 1 to run what you have posted?[
/QUOTE]
Just loop the first argument instead of the second one....

Col = "S"  ' The second argument to Cells can be either a column number or column letter
LastRowInS = Cells(Rows.Count, Col).End(xlUp).Row
For i = 1 To LastRowInS
  If Cells(i, Col).Value = 1 Then
    ' Whatever
  End If
Next

Also if I tell it as a test to run line 48 it picks up cells that have text in them as empty does that code work also for text or just values?

If you are testing like I did...

If Cells(RowNumber, i).Value = "" Then

then the only way it could do that is if the cell had something in it... perhaps a blank space? Without seeing your workbook, it is hard to say.

 

[Castyel]

comes up witha debug error on LastRowInS = Cells(Rows.count, Col).Enx(x1Up.Row

[FONT=Verdana][SIZE=2][COLOR=#333333]Sub Mandatory()[/COLOR][/SIZE][/FONT]
[FONT=Verdana][SIZE=2][COLOR=#333333]col = "S"[/COLOR][/SIZE][/FONT]
[FONT=Verdana][SIZE=2][COLOR=#333333]LastRowInS = Cells(Rows.Count, col).End(x1Up).Row[/COLOR][/SIZE][/FONT]
[FONT=Verdana][SIZE=2][COLOR=#333333]For i = 1 To LastRowInS[/COLOR][/SIZE][/FONT]
[FONT=Verdana][SIZE=2][COLOR=#333333]    If Cells(i, col).Value = 1 Then[/COLOR][/SIZE][/FONT]
[FONT=Verdana][SIZE=2][COLOR=#333333]        For x = 4 To 17[/COLOR][/SIZE][/FONT]
[FONT=Verdana][SIZE=2][COLOR=#333333]            If Cells(RowNumber, x).Value = "" Then[/COLOR][/SIZE][/FONT]
[FONT=Verdana][SIZE=2][COLOR=#333333]            Application.Goto Cells(RowNumber, x)[/COLOR][/SIZE][/FONT]
[FONT=Verdana][SIZE=2][COLOR=#333333]            MsgBox "Mandatory feild not completed"[/COLOR][/SIZE][/FONT]
[FONT=Verdana][SIZE=2][COLOR=#333333]            Cancel = True[/COLOR][/SIZE][/FONT]
[FONT=Verdana][SIZE=2][COLOR=#333333]            Exit For[/COLOR][/SIZE][/FONT]
[FONT=Verdana][SIZE=2][COLOR=#333333]        End If[/COLOR][/SIZE][/FONT]
[FONT=Verdana][SIZE=2][COLOR=#333333]        Next[/COLOR][/SIZE][/FONT]
[FONT=Verdana][SIZE=2][COLOR=#333333]    End If[/COLOR][/SIZE][/FONT]

[FONT=Verdana][SIZE=2][COLOR=#333333]Next[/COLOR][/SIZE][/FONT]
[FONT=Verdana][SIZE=2][COLOR=#333333]End Sub[/COLOR][/SIZE][/FONT]

 

[Rick Rothstein]

comes up witha debug error on LastRowInS = Cells(Rows.count, Col).Enx(x1Up.Row

[FONT=Verdana][SIZE=2][COLOR=#333333]Sub Mandatory()[/COLOR][/SIZE][/FONT]
[FONT=Verdana][SIZE=2][COLOR=#333333]col = "S"[/COLOR][/SIZE][/FONT]
[FONT=Verdana][SIZE=2][COLOR=#333333]LastRowInS = Cells(Rows.Count, col).End([/COLOR][SIZE=3][B][COLOR=#B22222]x1Up[/COLOR][/B][/SIZE][COLOR=#333333]).Row[/COLOR][/SIZE][/FONT]
[FONT=Verdana][SIZE=2][COLOR=#333333]For i = 1 To LastRowInS[/COLOR][/SIZE][/FONT]
[FONT=Verdana][SIZE=2][COLOR=#333333]    If Cells(i, col).Value = 1 Then[/COLOR][/SIZE][/FONT]
[FONT=Verdana][SIZE=2][COLOR=#333333]        For x = 4 To 17[/COLOR][/SIZE][/FONT]
[FONT=Verdana][SIZE=2][COLOR=#333333]            If Cells(RowNumber, x).Value = "" Then[/COLOR][/SIZE][/FONT]
[FONT=Verdana][SIZE=2][COLOR=#333333]            Application.Goto Cells(RowNumber, x)[/COLOR][/SIZE][/FONT]
[FONT=Verdana][SIZE=2][COLOR=#333333]            MsgBox "Mandatory feild not completed"[/COLOR][/SIZE][/FONT]
[FONT=Verdana][SIZE=2][COLOR=#333333]            Cancel = True[/COLOR][/SIZE][/FONT]
[FONT=Verdana][SIZE=2][COLOR=#333333]            Exit For[/COLOR][/SIZE][/FONT]
[FONT=Verdana][SIZE=2][COLOR=#333333]        End If[/COLOR][/SIZE][/FONT]
[FONT=Verdana][SIZE=2][COLOR=#333333]        Next[/COLOR][/SIZE][/FONT]
[FONT=Verdana][SIZE=2][COLOR=#333333]    End If[/COLOR][/SIZE][/FONT]

[FONT=Verdana][SIZE=2][COLOR=#333333]Next[/COLOR][/SIZE][/FONT]
[FONT=Verdana][SIZE=2][COLOR=#333333]End Sub[/COLOR][/SIZE][/FONT]

That is a lower case L in the red highlight above, not the number 1 (xl - is short for Excel)

 

[Castyel]

sorry its 2 am here. I'll try it again

 

[Castyel]

ok now it blows up in the IfCells(RowNumber, X).Value = ""Then

I'm starting to think it would be quicker just to write out 40 if statements for each possible row it could be in.

 

[Rick Rothstein]

ok now it blows up in the IfCells(RowNumber, X).Value = ""Then

I'm starting to think it would be quicker just to write out 40 if statements for each possible row it could be in.

In the code you posted...

[FONT=Verdana][SIZE=2][COLOR=#333333]Sub Mandatory()[/COLOR][/SIZE][/FONT]
[FONT=Verdana][SIZE=2][COLOR=#333333]col = "S"[/COLOR][/SIZE][/FONT]
[FONT=Verdana][SIZE=2][COLOR=#333333]LastRowInS = Cells(Rows.Count, col).End(x1Up).Row[/COLOR][/SIZE][/FONT]
[FONT=Verdana][SIZE=2][COLOR=#333333]For i = 1 To LastRowInS[/COLOR][/SIZE][/FONT]
[FONT=Verdana][SIZE=2][COLOR=#333333]    If Cells(i, col).Value = 1 Then[/COLOR][/SIZE][/FONT]
[FONT=Verdana][SIZE=2][COLOR=#333333]        For x = 4 To 17[/COLOR][/SIZE][/FONT]
[FONT=Verdana][SIZE=2][COLOR=#333333]            If Cells([/COLOR][SIZE=3][B][COLOR=#B22222]RowNumber[/COLOR][/B][/SIZE][COLOR=#333333], x).Value = "" Then[/COLOR][/SIZE][/FONT]
[FONT=Verdana][SIZE=2][COLOR=#333333]            Application.Goto Cells(RowNumber, x)[/COLOR][/SIZE][/FONT]
[FONT=Verdana][SIZE=2][COLOR=#333333]            MsgBox "Mandatory feild not completed"[/COLOR][/SIZE][/FONT]
[FONT=Verdana][SIZE=2][COLOR=#333333]            Cancel = True[/COLOR][/SIZE][/FONT]
[FONT=Verdana][SIZE=2][COLOR=#333333]            Exit For[/COLOR][/SIZE][/FONT]
[FONT=Verdana][SIZE=2][COLOR=#333333]        End If[/COLOR][/SIZE][/FONT]
[FONT=Verdana][SIZE=2][COLOR=#333333]        Next[/COLOR][/SIZE][/FONT]
[FONT=Verdana][SIZE=2][COLOR=#333333]    End If[/COLOR][/SIZE][/FONT]

[FONT=Verdana][SIZE=2][COLOR=#333333]Next[/COLOR][/SIZE][/FONT]
[FONT=Verdana][SIZE=2][COLOR=#333333]End Sub[/COLOR][/SIZE][/FONT]

you did not assign any value to RowNumber before you used it (see red above), so RowNumber equals 0 by default and Cells thinks you are asking for Row 0 (which, of course, doesn't exist).

 

[Castyel]

and we end up back where we started the rownumber is unknown when writing the code as I don't know which rows will have the 1 in it and that will change as the report is used.

Sorry if i have missed something else but it now looks like I will be not getting any sleep tonight as this report is due in about 5 hours

 

[Rick Rothstein]

and we end up back where we started the rownumber is unknown when writing the code as I don't know which rows will have the 1 in it and that will change as the report is used.

Sorry if i have missed something else but it now looks like I will be not getting any sleep tonight as this report is due in about 5 hours

I'm not 100% sure of what your code is ultimately trying to do, but I probably should have tried to read your overall code more carefully than I did. I think in place of RowNumber, you would want to use first loop counter... the i... that is what is iterating the rows, right?

 

[Norie]

Duplicate post http://www.mrexcel.com/forum/showthread.php?645377-Rammed-up-on-empty-cell-code.