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?)

 

[cstimart]

Wouldn't you want something like....

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

I assume you'd need some type of code after the "IF i" to determine the "?" row value.

 

[Castyel]

my issue is more with how do I get VBA to work on the row I want I have tried ("D" & ActiveCell.Row) but I don't think it is using the row where i = 1 but the row that was active on the sheet when the macro was run

 

[cstimart]

Wouldn't this do the trick?


For Each cell In Sheets("sheet1").Range("D" & i & ":G" & i)

</pre>

 

[Castyel]

still does not seem to work it runs through and even when the cell is empty acts as if it wasn't

Sub manditory2()

        For Each i In Range("S47:S82") 
            If i = 1 Then 
                For Each cell In Sheets("sheet1").Range("D" & i) 
                    If IsEmpty("D" & i) Then 
                        MsgBox "Mandatory field not filled" 
                        Application.Goto cell 
                        Cancel = True 
                        Exit For 
                    End If 
                Next cell 
                
            End If 
       Next i
end sub

 

[Castyel]

My code still seem to look at D1 not which ever D on the row where i = 1.

Can some one please help it's midnight and I have to be at work in 7 hours

 

[luvbite38]

bud,

hope this might help

use the named dynamic range.

for dynamic named ranges checkout the link below

http://www.ozgrid.com/Excel/DynamicRanges.htm

ta

 

[Rick Rothstein]

Don't use Range for what you are doing, rather, use Cells (an alternate to Range that takes row and column arguments). For what you appear to be doing, something like this...

RowNumber = 8
For i = 4 To 7  ' the column numbers for Columns D, E, F and G
  If Cells(RowNumber, i).Value = "" Then
    MsgBox "Mandatory field not filled" 
    Application.Goto Cells(RowNumber, i) 
    Cancel = True 
    Exit For 
  End If
Next

 

[Castyel]

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?

sorry if I don't make sense I have been building this report most of the day

 

[Castyel]

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?