Ignore blank cells

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

brownti - 25 May 2007 14:32 GMT

I am using the below code and it works perfectly except for one thing. If
there is a blank in the range "cost", but there is a value in the offset
column, it will clear that value out. I want the code to ignore blank cells
in the range. I tried a couple different things to no avail. Please advise.
Thanks.

Sub hardpunch()
Dim msg1, style1, title1, response1
msg1 = "Are you sure you want to hardpunch all prices?"
style1 = vbOK + vbCritical
title1 = "Hardpunch..."
response1 = MsgBox(msg1, style1, title1)
If response1 = vbOK Then
For Each cell In Range("cost")
If cell.Interior.ColorIndex = 3 Then
If IsNumeric(cell.Value) Then
cell.Offset(0, 1).Value = cell.Value
End If
End If
If cell.Interior.ColorIndex = 55 Then
If IsNumeric(cell.Value) Then
cell.Offset(0, 1).Value = cell.Value
End If
End If
Next
Else
Exit Sub
End If
End Sub

Reply to this Message

Jim Rech - 25 May 2007 15:17 GMT

May something like this:

Dim intInterior As Integer
For Each cell In Range("cost")
If cell.Value <> 0 Then
intInterior = cell.Interior.ColorIndex
If intInterior = 3 Or intInterior = 55 Then
If IsNumeric(cell.Value) Then
cell.Offset(0, 1).Value = cell.Value
End If
End If
End If
Next

Signature

Jim

|I am using the below code and it works perfectly except for one thing. If
| there is a blank in the range "cost", but there is a value in the offset
[quoted text clipped - 25 lines]
| End If
| End Sub

Reply to this Message

Jim Cone - 25 May 2007 15:19 GMT

Sub hardpunch()
Dim msg1 As String
Dim style1 As Long
Dim title1 As String
Dim response1 As Long
Dim cell As Range

msg1 = "Are you sure you want to hardpunch all prices?"
style1 = vbOK + vbQuestion
title1 = "Hardpunch..."
response1 = MsgBox(msg1, style1, title1)

If response1 = vbOK Then
For Each cell In Range("cost")
If cell.Interior.ColorIndex = 3 Or _
cell.Interior.ColorIndex = 55 Then
If Len(cell.Formula) Then
cell.Offset(0, 1).Value = cell.Value
End If
End If
Next
End If
End Sub

Signature

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

"brownti via OfficeKB.com"
<u31540@uwe>
wrote in message
I am using the below code and it works perfectly except for one thing. If
there is a blank in the range "cost", but there is a value in the offset
column, it will clear that value out. I want the code to ignore blank cells
in the range. I tried a couple different things to no avail. Please advise.
Thanks.

Sub hardpunch()
Dim msg1, style1, title1, response1
msg1 = "Are you sure you want to hardpunch all prices?"
style1 = vbOK + vbCritical
title1 = "Hardpunch..."
response1 = MsgBox(msg1, style1, title1)
If response1 = vbOK Then
For Each cell In Range("cost")
If cell.Interior.ColorIndex = 3 Then
If IsNumeric(cell.Value) Then
cell.Offset(0, 1).Value = cell.Value
End If
End If
If cell.Interior.ColorIndex = 55 Then
If IsNumeric(cell.Value) Then
cell.Offset(0, 1).Value = cell.Value
End If
End If
Next
Else
Exit Sub
End If
End Sub

Reply to this Message

Ben McBen - 25 May 2007 15:21 GMT

you could simply try:

If cell.Value <> "" Then
cell.Offset(0, 1).Value = cell.Value
End If

Reply to this Message

brownti - 25 May 2007 15:57 GMT

I tried each of those with no luck. The problem is that some times there is
a "#N/A" in range "cost" or text. I do not want these copying over to the
next row. Any other thoughts?

Reply to this Message

Don Guillett - 25 May 2007 16:04 GMT

It often helps to mention all of your problems in the ORIGINAL post.

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

>I tried each of those with no luck. The problem is that some times there
>is
> a "#N/A" in range "cost" or text. I do not want these copying over to the
> next row. Any other thoughts?

Reply to this Message

brownti - 25 May 2007 16:35 GMT

WOW. Sorry. I had those problems already solved with the original code. I
was looking for some additional thoughts or additions to the code. The
suggestions, which I appreciated, changed the code slightly to accomplish
what i asked for, but gave me a new problem. I have since figured out a way
to do it (another if statement after IsNumeric). No need to get all hyped
about it...This is a helping forum.

>It often helps to mention all of your problems in the ORIGINAL post.
>
>>I tried each of those with no luck. The problem is that some times there
>>is
>> a "#N/A" in range "cost" or text. I do not want these copying over to the
>> next row. Any other thoughts?

Reply to this Message