Working with Ranges...Need help
 

If anyone could help me out it might save a bit of my hair. What I am
trying to do is work with a specific range (A1:K12). All of the Odd
row ranges A1,3,5,7,9,11 have text data in the cell that does not
change. The even rows are used as a sort of check list.

Example:
Column A B C D E F G H I J K
Row 1 AH BH CH DH EH FH GH HH IH JH KH
Row 2 X X X X X
Row 3 AH BH CH DH EH FH GH HH IH JH KH
Row 4 X


How do I get my userform command button to automatically first finish
looking for the available space on row 2 until there is no more spaces
and when K2 is filled with an "X" how do I get it to go to the next
line and loop through the range A2:K12? I have been putting the code
on the cmd_Click Sub. Then I want it to return a msgBox that says the
2 letters above the space. Can this be done and how?

Any help would be greatly appreciated.
Thank you

Working with Ranges...Need help
 

Maybe...

dim myCell as range
dim myRng as range
dim myRow as range

set myrng = worksheets("Sheet99").range("A1:K12")

for each myRow in myrng.rows
if myrow.row mod 2 = 1 then
'odd numbered row, skip it
else
for each mycell in myrow.cells
if mycell.value = "" then
'update
mycell.value = "X"
'and stop looking
exit for
end if
next mycell
end if
next myrow



Jitranijam wrote:

If anyone could help me out it might save a bit of my hair. What I am
trying to do is work with a specific range (A1:K12). All of the Odd
row ranges A1,3,5,7,9,11 have text data in the cell that does not
change. The even rows are used as a sort of check list.

Example:
Column A B C D E F G H I J K
Row 1 AH BH CH DH EH FH GH HH IH JH KH
Row 2 X X X X X
Row 3 AH BH CH DH EH FH GH HH IH JH KH
Row 4 X

How do I get my userform command button to automatically first finish
looking for the available space on row 2 until there is no more spaces
and when K2 is filled with an "X" how do I get it to go to the next
line and loop through the range A2:K12? I have been putting the code
on the cmd_Click Sub. Then I want it to return a msgBox that says the
2 letters above the space. Can this be done and how?

Any help would be greatly appreciated.
Thank you

--

Dave Peterson

Working with Ranges...Need help
 

I think this code is a lot closer than I have gotten. the only thing I
need it to stop doing is placing the "X" on every row. I want the
formula to search across the first row, and if it finds a "" cell, then
to paste the X only in that Cell, at this point I would need it to
return a msg box stating the Initials above where the x was placed.
Only one at a time. Any suggestions?


Dave Peterson wrote:
Maybe...

dim myCell as range
dim myRng as range
dim myRow as range

set myrng = worksheets("Sheet99").range("A1:K12")

for each myRow in myrng.rows
if myrow.row mod 2 = 1 then
'odd numbered row, skip it
else
for each mycell in myrow.cells
if mycell.value = "" then
'update
mycell.value = "X"
'and stop looking
exit for
end if
next mycell
end if
next myrow



Jitranijam wrote:

If anyone could help me out it might save a bit of my hair. What I am
trying to do is work with a specific range (A1:K12). All of the Odd
row ranges A1,3,5,7,9,11 have text data in the cell that does not
change. The even rows are used as a sort of check list.

Example:
Column A B C D E F G H I J K
Row 1 AH BH CH DH EH FH GH HH IH JH KH
Row 2 X X X X X
Row 3 AH BH CH DH EH FH GH HH IH JH KH
Row 4 X

How do I get my userform command button to automatically first finish
looking for the available space on row 2 until there is no more spaces
and when K2 is filled with an "X" how do I get it to go to the next
line and loop through the range A2:K12? I have been putting the code
on the cmd_Click Sub. Then I want it to return a msgBox that says the
2 letters above the space. Can this be done and how?

Any help would be greatly appreciated.
Thank you

--

Dave Peterson

Working with Ranges...Need help
 

Oops. I exited one for/next loop, but not the one for the .rows.

Option Explicit
Sub testme()

Dim myCell As Range
Dim myRng As Range
Dim myRow As Range
Dim WorkIsDone As Boolean

Set myRng = Worksheets("Sheet99").Range("A1:K12")

WorkIsDone = False
For Each myRow In myRng.Rows
If myRow.Row Mod 2 = 1 Then
'odd numbered row, skip it
Else
For Each myCell In myRow.Cells
If myCell.Value = "" Then
'update
myCell.Value = "X"
WorkIsDone = True
MsgBox myCell.Offset(-1, 0).Value
'and stop looking
Exit For
End If
Next myCell
End If
If WorkIsDone Then
Exit For
End If
Next myRow
End Sub



Jitranijam wrote:

I think this code is a lot closer than I have gotten. the only thing I
need it to stop doing is placing the "X" on every row. I want the
formula to search across the first row, and if it finds a "" cell, then
to paste the X only in that Cell, at this point I would need it to
return a msg box stating the Initials above where the x was placed.
Only one at a time. Any suggestions?

Dave Peterson wrote:
Maybe...

dim myCell as range
dim myRng as range
dim myRow as range

set myrng = worksheets("Sheet99").range("A1:K12")

for each myRow in myrng.rows
if myrow.row mod 2 = 1 then
'odd numbered row, skip it
else
for each mycell in myrow.cells
if mycell.value = "" then
'update
mycell.value = "X"
'and stop looking
exit for
end if
next mycell
end if
next myrow



Jitranijam wrote:

If anyone could help me out it might save a bit of my hair. What I am
trying to do is work with a specific range (A1:K12). All of the Odd
row ranges A1,3,5,7,9,11 have text data in the cell that does not
change. The even rows are used as a sort of check list.

Example:
Column A B C D E F G H I J K
Row 1 AH BH CH DH EH FH GH HH IH JH KH
Row 2 X X X X X
Row 3 AH BH CH DH EH FH GH HH IH JH KH
Row 4 X

How do I get my userform command button to automatically first finish
looking for the available space on row 2 until there is no more spaces
and when K2 is filled with an "X" how do I get it to go to the next
line and loop through the range A2:K12? I have been putting the code
on the cmd_Click Sub. Then I want it to return a msgBox that says the
2 letters above the space. Can this be done and how?

Any help would be greatly appreciated.
Thank you

--

Dave Peterson

--

Dave Peterson