Code Help Needed

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

NEWER USER - 25 Jan 2008 20:46 GMT

I need to fill a Column (B) with a value of "1" in every cell that is null up
to the last row having a value in Column (A). There are no empty cells in
column A up to the last row with data in the spreadsheet. I can not
accomplish through a recorded macro. Can anybody help me with some coding to
accomplish this task? Thank you

Reply to this Message

Mike H - 25 Jan 2008 20:58 GMT

We may be at cross purposes over the definition of Null but try this

Sub Sonic()
Set myrange = Range("B1:B" & Cells(Rows.Count, "A").End(xlUp).Row)
For Each c In myrange
If IsNull(c) Then c.Value = 1
Next
End Sub

Mike

> I need to fill a Column (B) with a value of "1" in every cell that is null up
> to the last row having a value in Column (A). There are no empty cells in
> column A up to the last row with data in the spreadsheet. I can not
> accomplish through a recorded macro. Can anybody help me with some coding to
> accomplish this task? Thank you

Reply to this Message

NEWER USER - 25 Jan 2008 21:23 GMT

Problem with IsNull. I replaced with If (c) =2 Then c.Value = 1 and it
worked fine. So, I want to replace EMPTY cells with 1. What do I use or
change IsNull to? I( feel I am almost there. Thanks

> We may be at cross purposes over the definition of Null but try this
>
[quoted text clipped - 12 lines]
> > accomplish through a recorded macro. Can anybody help me with some coding to
> > accomplish this task? Thank you

Reply to this Message

Per Jessen - 25 Jan 2008 21:01 GMT

>I need to fill a Column (B) with a value of "1" in every cell that is null
>up
[quoted text clipped - 3 lines]
> to
> accomplish this task? Thank you

Hi

Sub FillColB()
Dim tRange As Range
Dim c
Set tRange = Range("A1", Range("A1").End(xlDown))

For Each c In tRange
If c.Value <> "" Then c.Offset(0, 1).Value = 1
Next
End Sub

regards,

Per

Reply to this Message

Jim Cone - 25 Jan 2008 21:09 GMT

Sub FillIn()
Dim rng As Range
Dim rBlanks As Range

Set rng = Cells(Rows.Count, 1).End(xlUp).Offset(0, 1)
Set rBlanks = Range("B1", rng).SpecialCells(xlCellTypeBlanks)
rBlanks.Value = 1
End Sub

Signature

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

"NEWER USER"
wrote in message
I need to fill a Column (B) with a value of "1" in every cell that is null up
to the last row having a value in Column (A). There are no empty cells in
column A up to the last row with data in the spreadsheet. I can not
accomplish through a recorded macro. Can anybody help me with some coding to
accomplish this task? Thank you

Reply to this Message

NEWER USER - 25 Jan 2008 21:44 GMT

Jim,

Thanks for the help. You nailed it. Have a GREAT weekend. Many Thanks

> Sub FillIn()
> Dim rng As Range
[quoted text clipped - 4 lines]
> rBlanks.Value = 1
> End Sub

Reply to this Message