If B1 = 0 then D1= True - I need to write this in a macro

[Guest]

Hi All

I was hoping that someone may be able to help me.
Basically if I have cell with a value 0, with a click of
a button a macro will check all the values in that column
(B1) for the value 0, and place a value (True) in column
D1. can you lep?

 

[Jack Schitt]

To test (say) rows 1 to 20:

Sub mytest()
Dim rRow As Long
For rRow = 1 To 20
If Not (IsEmpty(Cells(rRow, 2))) And _
Cells(rRow, 2).Value = 0 Then Cells(rRow, 4).Value = True
Next rRow
End Sub

 

[Tom Ogilvy]

Private Sub CommandButton1_Click()
Dim rng as Range, cell as Range
if activecell.Column = 4 then exit sub
if activeCell.Value = 0 and not isempty(activecell) then
set rng = Range(Cells(1,activecell.column), _
cells(rows.count,activecell.column).End(xlup))
for each cell in rng
if not isempty(cell) and cell.value = 0 then
cells(cell.row,4).Value = True
end if
Next
End if
End Sub

Put a commandbutton on the sheet from the control toolbox toobar.

right click on the sheet tab and select view code.

Put in code like the above (rename commandbutton1 to match the name of the
commandbutton) in the resulting module.

 

[Guest]

A massive THANK-YOU!!!!

You're a star and a great help!!!!

it worked a treat!

Cheers
Sanj