count cells if ""
 

I need to check a row to see if there are at least 32 or 44 cell
specifically to the right that are ""
If so, I want to fill the cells with a text value "MyValue"

If there is any other number of cells including zero that are "",
want to skip that row and go to the next one.

Just can't get it

Thank

--
Message posted from http://www.ExcelForum.com

count cells if ""
 

Dim cell as Range, rng as Range
for each cell in Range("A1:A200")
set rng = Cell.offset(0,1).Resize(1,32)
if Application.countA(rng) = 32 then
cell.offset(0,1).Resize(1,32).Value = "MyValue"
End if
Next

for 44
Dim cell as Range, rng as Range
for each cell in Range("A1:A200")
set rng = Cell.offset(0,1).Resize(1,44)
if Application.countA(rng) = 44 then
cell.offset(0,1).Resize(1,44).Value = "MyValue"
End if
Next

--
Regards,
Tom Ogilvy



"hotherps " wrote in message
...
I need to check a row to see if there are at least 32 or 44 cells
specifically to the right that are ""
If so, I want to fill the cells with a text value "MyValue"

If there is any other number of cells including zero that are "", I
want to skip that row and go to the next one.

Just can't get it

Thanks


---
Message posted from http://www.ExcelForum.com/

count cells if ""
 

Thanks Tom
I'm trying to use this within a Public Sub and I', thinking that you
can't because I'm getting nothing but errors from the Set Range.

I'll keep working at it.

Here is the full Sub (2 parts)

Dim need As Integer

Application.Calculation = xlManual

skillx = 9
timeStart = -1
timeStart2 = -1

For period = 1 To 12
timeStart = timeStart + 8
timeStart2 = timeStart2 + 8

For skilly = 104 To 124
skillNam = Sheet236.Cells(skillx, skilly).Value
need = (Sheet236.Cells(period + 318, skilly - 97).Value -
Sheet236.Cells(period + 304, skilly - 97).Value) * 4
If skillNam = "ENG" Then
Eng period, timeStart, skilly, need
ElseIf skillNam = "IND" Then
Ind period, timeStart, skilly, need
ElseIf skillNam = "MMS" Then
MMS period, timeStart2, skilly, need
Else
OtherSkill period, timeStart, skilly, skillNam, need
End If
Next skilly

Next period
end sub


Public Sub MMS(ByVal period As Integer, ByVal timeStart As Integer,
ByVal skilly As Integer, ByVal need As Integer)
With Sheet236
For x = 11 To 298
If .Cells(x, skilly).Value = "x" Then
For y = timeStart To timeStart + 7
If .Cells(x, y).Value = "." _
And Cells(x, y).Offset(0, 32).Value = "." _
And counter < 16 _
And need 0 Then
..Cells.Offset(x, y).Resize(1, 32).Value = "MMS"
need = need - 1
End If
Next y
End If
Next x
End With

Thanks for your help

End Sub


---
Message posted from http://www.ExcelForum.com/

count cells if ""
 

Public Sub MMS(ByVal period As Integer, ByVal timeStart As Integer,
ByVal skilly As Integer, ByVal need As Integer)

' counter never takes a value or changes,
'so you can do away with that check
With Sheet236
For x = 11 To 298
If .Cells(x, skilly).Value = "x" Then
For y = timeStart To timeStart + 7
If .Cells(x, y).Value = "." _
And Cells(x, y).Offset(0, 32).Value = "." _
And need 0 Then
' you can't offset from cells so this raises an error
Cells.Offset(x, y).Resize(1, 32).Value = "MMS"
' probably want
' cells(x,y).Resize(1,32).Value = "MMS"
need = need - 1
End If
Next y
End If
Next x
End With

Your looping over 8 columns (timestart to timestart + 7) and in each column
you try to fill in 32 cells - overwriting 31 cells from the previous column.
However, if you write the cells with y = timestart , then on the next loop,
y = timestart + 1, and you test if .Cells(x,y:=timestart + 1) = "." -- well
it won't because cells(x,timestart +1) will equal MMS since you just wrote
it in that cell. So not sure why you are looping over Y.

As far as the code I posted, it works fine for me as written. Being in a
public sub should have no effect. But I am not sure how it would help you
here unless you want some other check than testing cells(x,y) = "." and
cells(x,y).offset(0,32) = "."

--
Regards,
Tom Ogilvy

"hotherps " wrote in message
...
Thanks Tom
I'm trying to use this within a Public Sub and I', thinking that you
can't because I'm getting nothing but errors from the Set Range.

I'll keep working at it.

Here is the full Sub (2 parts)

Dim need As Integer

Application.Calculation = xlManual

skillx = 9
timeStart = -1
timeStart2 = -1

For period = 1 To 12
timeStart = timeStart + 8
timeStart2 = timeStart2 + 8

For skilly = 104 To 124
skillNam = Sheet236.Cells(skillx, skilly).Value
need = (Sheet236.Cells(period + 318, skilly - 97).Value -
Sheet236.Cells(period + 304, skilly - 97).Value) * 4
If skillNam = "ENG" Then
Eng period, timeStart, skilly, need
ElseIf skillNam = "IND" Then
Ind period, timeStart, skilly, need
ElseIf skillNam = "MMS" Then
MMS period, timeStart2, skilly, need
Else
OtherSkill period, timeStart, skilly, skillNam, need
End If
Next skilly

Next period
end sub


Public Sub MMS(ByVal period As Integer, ByVal timeStart As Integer,
ByVal skilly As Integer, ByVal need As Integer)
With Sheet236
For x = 11 To 298
If .Cells(x, skilly).Value = "x" Then
For y = timeStart To timeStart + 7
If .Cells(x, y).Value = "." _
And Cells(x, y).Offset(0, 32).Value = "." _
And counter < 16 _
And need 0 Then
Cells.Offset(x, y).Resize(1, 32).Value = "MMS"
need = need - 1
End If
Next y
End If
Next x
End With

Thanks for your help

End Sub


---
Message posted from http://www.ExcelForum.com/

count cells if ""
 

I got it!

I just kept playing around with combinations of your posts and and I
came up with this:

For x = 11 To 298
counter = 0
If .Cells(x, skilly).Value = "x" Then
For y = timeStart To timeStart + 7
If .Cells(x, y).Value = "." _
And .Cells(x, y).Offset(0, 31) = "." _
And need 0 Then
..Cells(x, y).Offset(0, 0).Resize(1, 32).Value = "MMS"

need = need - 8

End If
Next y
End If
Next x

Thanks Tom ,you are the best!


---
Message posted from http://www.ExcelForum.com/