Ignore blank cells

[brownti via OfficeKB.com]

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

 

[Jim Rech]

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


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

 

[Jim Cone]

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

 

[Guest]

you could simply try:

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

 

[brownti via OfficeKB.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?

 

[Don Guillett]

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

 

[brownti via OfficeKB.com]

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.