VBA Code for IF B1 <> "" then C1="FILLED"

I have a column that has a random number of entries in it (sometimes cell
B1-B20 are filled sometimes B1-B4 are filled, etc). I am looking to write a
macro that when run, will check to see if there is a value in column B, and
if so, fill the corresponding cell in Column C with "FILLED"

Hi Diane

You don't say how Column B gets it's data.
Could you not simply use the IF function in column c
=IF(B1<>"", "Filled","").

Nick



"Diane" <[email protected]> wrote in message
news:[email protected]...
>I have a column that has a random number of entries in it (sometimes cell
> B1-B20 are filled sometimes B1-B4 are filled, etc). I am looking to write
> a
> macro that when run, will check to see if there is a value in column B,
> and
> if so, fill the corresponding cell in Column C with "FILLED"

How about:

Range("b1").Select
Do Until ActiveCell.Value = ""
ActiveCell.Offset(0, 1).Value = "Filled"
ActiveCell.Offset(1, 0).Select
Loop

HTH

"Diane" wrote:

> I have a column that has a random number of entries in it (sometimes cell
> B1-B20 are filled sometimes B1-B4 are filled, etc). I am looking to write a
> macro that when run, will check to see if there is a value in column B, and
> if so, fill the corresponding cell in Column C with "FILLED"

Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "B").End(xlUp).Row
For i = 1 To iLastRow
If Cells(i, "B").Value <> "" Then
Cells(i, "C").Value = "FILLED"
End If
Next i


--
HTH

Bob Phillips

"Diane" <[email protected]> wrote in message
news:[email protected]...
> I have a column that has a random number of entries in it (sometimes cell
> B1-B20 are filled sometimes B1-B4 are filled, etc). I am looking to write
a
> macro that when run, will check to see if there is a value in column B,
and
> if so, fill the corresponding cell in Column C with "FILLED"

As long as you fill data sequentially (i.e., no holes) and you have at
least one entry (i.e., B1 is not empty), a loop-less solution:

Option Explicit

Sub testit()
With ActiveSheet
Range(.Range("b1"), .Cells(.Rows.Count, 2).End(xlUp)) _
.Offset(0, 1).Value = "filled"
End With
End Sub

For more see example 4 in
Beyond Excel's recorder
http://www.tushar-
mehta.com/excel/vba/beyond_the_macro_recorder/index.htm
--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <[email protected]>,
[email protected] says...
> I have a column that has a random number of entries in it (sometimes cell
> B1-B20 are filled sometimes B1-B4 are filled, etc). I am looking to write a
> macro that when run, will check to see if there is a value in column B, and
> if so, fill the corresponding cell in Column C with "FILLED"
>

Thank you all - very helpful!!!!

"Bob Phillips" wrote:

> Dim iLastRow As Long
> Dim i As Long
>
> iLastRow = Cells(Rows.Count, "B").End(xlUp).Row
> For i = 1 To iLastRow
> If Cells(i, "B").Value <> "" Then
> Cells(i, "C").Value = "FILLED"
> End If
> Next i
>
>
> --
> HTH
>
> Bob Phillips
>
> "Diane" <[email protected]> wrote in message
> news:[email protected]...
> > I have a column that has a random number of entries in it (sometimes cell
> > B1-B20 are filled sometimes B1-B4 are filled, etc). I am looking to write
> a
> > macro that when run, will check to see if there is a value in column B,
> and
> > if so, fill the corresponding cell in Column C with "FILLED"
>
>
>