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.

1	75
2	15
3	0
4	0
5	0
6	3
7	0
8	0
9	0
10	0
11	0
12	0
13	0
14	0
15	0
16	16
17	84
18	185

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

 

[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