formula or VBA

[VbaHell]

Hello all

I am trying to figure out this Formula or I can use VBA as the formula will but put into VBA.

This is what I am trying to do

If V2 = Blank then ignore cell U2
If V2 >"" then U2 = "Lost"

This formula or VBA would be looped through 5,000 rows

=IF(ISBLANK(V2),"",U2="Lost")

I can't put the formula in U2 because there is data in there that needs to remain unless V2 had data in

 

[Fluff]

How about

Sub VbaHell()
   With Range("U2", Range("V" & Rows.Count).End(xlUp).Offset(, -1))
      .Value = Evaluate(Replace("If(@=""""," & .Address & ",""Lost"")", "@", .Offset(, 1).Address))
   End With
End Sub

 

[Rick Rothstein]

This is what I am trying to do

If V2 = Blank then ignore cell U2
If V2 >"" then U2 = "Lost"

What is in the cells of Column V... formulas or constant values?

What type of values are in Column V... text or numbers?

 

[VbaHell]

Hi Rick

the values in column “V” are a drop down list in text format that the user selects, then send the workbook back and I then use the merge vba to bring the data back, at this stage this is when the formula needs to populate correctly to match the updated data

 

[VbaHell]

Hi Fluff

thanks very much for your reply, I will try this once I get home, the wife’s dragging me around the shops Christmas shopping at the moment

 

[Rick Rothstein]

the values in column “V” are a drop down list in text format that the user selects, then send the workbook back and I then use the merge vba to bring the data back...

I am sorry, but the above is kind of meaningless to me as I do not know anything about your company's business model. With that said, all I really need to know is what is in the cells of Column V at the time you want to run the macro you have asked for. It sounds like it will be constants (non-formulas) so that the blank cells you mentioned will be truly blank (that is empty with absolutely nothing in them). If that is correct, then this macro should also work for you...

Sub Lost()
  Columns("V").SpecialCells(xlConstants).Offset(, -1).Value = "Lost"
End Sub

 

[VbaHell]

Ok sorry for not being clear on my reply to your question

 

[Rick Rothstein]

Ok sorry for not being clear on my reply to your question

No problem. More improtant,.. did the macro I posted work for you?

 

[VbaHell]

Yes thank you