IF a value in one column is => 10,000, Yes in next column or no in next column if false

[OilEconomist]

Thanks in advance, and I will let you know what final result works.

Objective: Within the same sheet if column J entry >= 10,000, Then Column K = "Yes", Else Column K = "No". The number of rows in the worksheet will vary when I use this sheet so I want to make it find the last row.

Sub Value()

Worksheets("Sheet1").Activate
Dim LastRow1000 As Long
LastRow1000 = ThisWorkbook.Worksheets("Shee1").Cells(Rows.Count, 1).End(xlUp).Row

erow = Worksheets("Sheet1").Columns.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

For i = 2 To LastRow1000
If Worksheets("Sheet1").Columns.Cells(erow, 10) >= 10000 Then
Worksheets("Sheet1").Columns.Cells(erow, 11) = "Yes"
Else
Worksheets("Sheet1").Columns.Cells(erow, 11) = "No"
End If
Next i

End Sub

 

[FDibbins]

Why do you need VBA for this?

Assuming your data starts in A2...
=if(A2="","",if(A2>=10000,"Yes","No")
Copied down

 

[Joe4]

So, is it not working or are you getting errors?
What exactly is the issue?

Note: Here are a few problems I see:
1. Do NOT use reserved words (names of functions, methods, properties, or objects) as the name of your Procedures, Functions, or Variables. Try changing the name to "MyValue" instead of "Value".
2. You have a typo in your LastRow1000 calculation. It should be "Sheet1" not "Shee1"
3. If you are looking at column J for your formula, why are you looking at column A to find the last row instead of column J, i.e.

[COLOR=#333333]LastRow1000 = ThisWorkbook.Worksheets("Sheet1").Cells(Rows.Count, [/COLOR][B][COLOR=#ff0000]"J"[/COLOR][/B][COLOR=#333333]).End(xlUp).Row[/COLOR]

Note: This can easily be done without the use of VBA. See Ford's recommendation. You can even use Autofill to populate all the rows with a single double-click.

 

[OilEconomist]

Thanks to both of you for your assistance.

No errors, but nothing happens except it goes to that sheet. Can you modify my code where you think it needs to changes as it still does not run.

I am using VBA so I can automate the model versus manually changing this every month.

In terms of Sheet1 typo, the sheet is actually named something else where it is correct throughout the code.

Whether it's column A or J, they both end the same place, and actually column J might be blank for that particular entry where column A always has a value in it.

 

[Joe4]

I can only assume that you want to use VBA and not a formula because you want the hard-coded values and not the formula.
Either way, it can be done without any loops like this:

Sub MyValue()

    Dim LastRow1000 As Long
    
    Worksheets("Sheet1").Activate
    LastRow1000 = Cells(Rows.Count, 1).End(xlUp).Row
    
'   Populate formulas in all cells in column K
    Range("K2:K" & LastRow1000).FormulaR1C1 = "=IF(RC[-1]>=10000,""Yes"",""No"")"

'   Change formulas to hard-coded values
    Range("K2:K" & LastRow1000).Value = Range("K2:K" & LastRow1000).Value
    
End Sub

 

[OilEconomist]

Thanks so much as that seemed to do the trick.

 

[Joe4]

You are welcome!
Glad I could help!

 

[FDibbins]

I am using VBA so I can automate the model versus manually changing this every month.

Not sure I understand how that would make any difference?
(I know ou have an answer, just curious)

 

[OilEconomist]

I already answered that question as I see you posted my answer. There are many things that you can do manually, but I prefer to click a button and work on other things. I can do this manually and it will take a minute, but so will a number of things I've automated. You add all that up, you start to save hours. It's Modeling Best Practices.

 

[FDibbins]

Did not really answer my question, but that's OK.

I still fail to see how doing nothing (because the formulas are automatic with no intervention required) would be slower that doing something (clicking on a macro to get it to run), but perhaps your best practices are different to mine