vba question

[flynpat46]

Hi,
Please help..
I am having trouble finding right code for my project. There are three rows. The third rows is empty. I need vba to evaluate B1, if less than 0, put an X in C1. Repeat on down until there isn't a value in column B.
[TABLE="width: 98"]
<colgroup><col width="44" style="width: 33pt; mso-width-source: userset; mso-width-alt: 1609;"> <col width="43" style="width: 32pt; mso-width-source: userset; mso-width-alt: 1572;"> <col width="44" style="width: 33pt; mso-width-source: userset; mso-width-alt: 1609;"> <tbody>[TR]
[TD="width: 44, bgcolor: transparent"]1[/TD]
[TD="width: 43, bgcolor: transparent"]75[/TD]
[TD="width: 44, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]15[/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]6[/TD]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]7[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]8[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]9[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]10[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]11[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]12[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]13[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]14[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]15[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]16[/TD]
[TD="bgcolor: transparent"]16[/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]17[/TD]
[TD="bgcolor: transparent"]84[/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]18[/TD]
[TD="bgcolor: transparent"]185[/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]

 

[Logit]

.
None of the numbers are less than zero.

 

[flynpat46]

Sorry about that, I meant if it equals 0, place an X in the next cell to the right of the 0.

 

[Trebor76]

Here's two possible ways (as the second doesn't loop it's probably the preferred):

Option Explicit
Sub Macro1()

    Dim rngMyCell As Range
    Dim xlnCalcMethod As XlCalculation
    
    With Application
        .ScreenUpdating = False
        xlnCalcMethod = .Calculation
        .Calculation = xlCalculationManual
    End With

    For Each rngMyCell In Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row)
        If Val(rngMyCell) = 0 Then rngMyCell.Offset(0, 1).Value = "X"
    Next rngMyCell
    
    With Application
        .Calculation = xlnCalcMethod
        .ScreenUpdating = True
    End With

End Sub
Sub Macro2()

    Dim lngLastRow As Long
    Dim xlnCalcMethod As XlCalculation
    
    With Application
        .ScreenUpdating = False
        xlnCalcMethod = .Calculation
        .Calculation = xlCalculationManual
    End With
    
    lngLastRow = Cells(Rows.Count, "B").End(xlUp).Row
    
    With Range("C1:C" & lngLastRow)
        .Formula = "=IF(B1=0,""X"","""")"
        .Value = .Value
    End With
    
    With Application
        .Calculation = xlnCalcMethod
        .ScreenUpdating = True
    End With

End Sub

Hope that helps,

Robert

 

[Fluff]

Another option

Sub AddX()
   With Range("C1", Range("B" & Rows.Count).End(xlUp).Offset(, 1))
      .Value = Evaluate(Replace("if(@=0,""X"","""")", "@", .Offset(, -1).Address))
   End With
End Sub

 

[flynpat46]

Perfect! That's exactly what I was looking for.
Thank you!

 

[Fluff]

Glad we could help & thanks for the feedback

 

[flynpat46]

I have another question
instead of starting at "C1", Range("B",
I want to start at "F4", Range("B", will that work or will the next line need changes. I coded in VB, learning VBA and it hasn't clicked yet.

 

[flynpat46]

correction on , Range("B", it should be , Range("E" but starting with an "X" in E4 if its cell is 0.

 

[Fluff]

Try

Sub AddX()
   With Range("[COLOR=#ff0000]E4[/COLOR]", Range("B" & Rows.Count).End(xlUp).Offset(, [COLOR=#ff0000]3[/COLOR]))
      .Value = Evaluate(Replace("if(@=0,""X"","""")", "@", .Offset(, -1).Address))
   End With
End Sub