VBA Fill Empty Blanks In Column B if Value is Present in Column C - Help is Appreciated

[kescco]

I need for each Row in COLUMN B which is blank to be filled with "999"
if there is a positive value in COLUMN C (which can be negative).

So, in the example below, the blank spaces in Column B need to be filled with 999.

Please note this is part of a larger macro and all help is appreciated.

Column A	Column B	Column C
Row 1	311	44
Row 2	344	45
Row 3		-444
Row 4		99
Row 5	311	-44

<tbody>
</tbody>

 

[iggydarsa]

Sub PopulateB()
    Dim lr As Long
    Dim i As Long
    lr = Range("C" & Rows.Count).End(xlUp).Row
    
    For i = 1 To lr
        If Range("B" & i).Value = "" And Range("C" & i).Value >= 0 Then
            Range("B" & i).Value = 999
        End If
    Next i
End Sub

 

[kescco]

If Range("B" & i).Value = "" And Range("C" & i).Value >= 0 Then

Problem is that many of my entries in C will be Negative and thus the greater than or equal to will not work.


Could I use something like:

If Range("B" & i).Value = "" And Range("C" & i).Value <> "" Then

Kescco

 

[JoeMo]

If Range("B" & i).Value = "" And Range("C" & i).Value >= 0 Then

Problem is that many of my entries in C will be Negative and thus the greater than or equal to will not work.


Could I use something like:

If Range("B" & i).Value = "" And Range("C" & i).Value <> "" Then

Kescco

Your OP is a bit ambiguous. Do you want to fill blank cells in col B if the col C companion cell is not blank, or only if there is a positive number in the C cell?

 

[kescco]

Yes, you are correct that my original post was a bit confusing. First I say positive value and then say it can be negative. It has been a long day.

I only wanted to fill the blank values in B if there was anything present in C.

I went ahead and tested the code I replied with and it does work.

I appreciate all the help everyone.

Thank you.

 

[Rick Rothstein]

I only wanted to fill the blank values in B if there was anything present in C.

Here is a non-looping macro that should work for you...

Sub FillBlanksInColumnBifColumnCisFilled()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "C").End(xlUp).Row
  Range("B1:B" & LastRow) = Evaluate(Replace("IF((C1:C#<>0)*(B1:B#="""")" & _
                            ",999,IF(B1:B#="""","""",B1:B#))", "#", LastRow))
End Sub

 

[kescco]

Mr. Rothstein,

Thank you so much. Brilliant code there.

 

[afaizy]

Here is a non-looping macro that should work for you...

Sub FillBlanksInColumnBifColumnCisFilled()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "C").End(xlUp).Row
  Range("B1:B" & LastRow) = Evaluate(Replace("IF((C1:C#<>0)*(B1:B#="""")" & _
                            ",999,IF(B1:B#="""","""",B1:B#))", "#", LastRow))
End Sub

Hello Mr. Rick, could you please explain the above code. I have similar query where I want to fill the values in blank cell from another cell same row.