If Statement VBA Search and Output Help

[KSolid]

Hello All,

I have currently have trouble developing a VBA/macro that would do a search in a data range within a table and once search it will populate another range with the information I need.
Below I have an example of what I am trying to do but can't seem to wrap my head around

A	B
Status	Results
Sold	Profit
On Sale	Loss
Sold Today	Profit
Sold Yesterday	Profit
Inventory	Stable

<tbody>
</tbody>

The if statement would be for if the range in Status had the word "Sold" it will output "Profit" in the Result range, else it would look for "On Sale" which would output "Loss". So on and so on until the if statement were to end.

Thank you so much in advance for the help and assistance. Feel free to post if I am coming across as unclear.

 

[mumps]

Try:

Sub KSolid()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("A1:B" & LastRow).AutoFilter Field:=1, Criteria1:="*Sold*"
    Range("B2:B" & LastRow).SpecialCells(xlCellTypeVisible) = "Profit"
    Range("A1:B" & LastRow).AutoFilter Field:=1, Criteria1:="*On Sale*"
    Range("B2:B" & LastRow).SpecialCells(xlCellTypeVisible) = "Loss"
    If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
    Application.ScreenUpdating = True
End Sub

 

[KSolid]

Try:

Sub KSolid()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("A1:B" & LastRow).AutoFilter Field:=1, Criteria1:="*Sold*"
    Range("B2:B" & LastRow).SpecialCells(xlCellTypeVisible) = "Profit"
    Range("A1:B" & LastRow).AutoFilter Field:=1, Criteria1:="*On Sale*"
    Range("B2:B" & LastRow).SpecialCells(xlCellTypeVisible) = "Loss"
    If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
    Application.ScreenUpdating = True
End Sub

My Good Sir you are one amazing individual, it is currently working well. Is there a way for it to filter out Blank data in the Status range. For example. If status had no information it will output "No data" in the Result range.

 

[KSolid]

My Good Sir you are one amazing individual, it is currently working well. Is there a way for it to filter out Blank data in the Status range. For example. If status had no information it will output "No data" in the Result range.

I encountered a minor bug I believe. For example if I don't happen to have "Sold" in the range, it would not run the rest of the VBA, so it will not skip over it rather it would give me an error message.

 

[mumps]

I'll get back to you soon.

 

[mumps]

Try:

Sub KSolid()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    If WorksheetFunction.CountIf(Range("A2:A" & LastRow), "*Sold*") <> 0 Then
        Range("A1:B" & LastRow).AutoFilter Field:=1, Criteria1:="*Sold*"
        Range("B2:B" & LastRow).SpecialCells(xlCellTypeVisible) = "Profit"
    End If
    If WorksheetFunction.CountIf(Range("A2:A" & LastRow), "*On Sale*") <> 0 Then
        Range("A1:B" & LastRow).AutoFilter Field:=1, Criteria1:="*On Sale*"
        Range("B2:B" & LastRow).SpecialCells(xlCellTypeVisible) = "Loss"
    End If
    Range("A1:B" & LastRow).AutoFilter Field:=1, Criteria1:="="
    Range("B2:B" & LastRow).SpecialCells(xlCellTypeVisible) = "No Data"
    If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
    Application.ScreenUpdating = True
End Sub

 

[KSolid]

Try:

Sub KSolid()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    If WorksheetFunction.CountIf(Range("A2:A" & LastRow), "*Sold*") <> 0 Then
        Range("A1:B" & LastRow).AutoFilter Field:=1, Criteria1:="*Sold*"
        Range("B2:B" & LastRow).SpecialCells(xlCellTypeVisible) = "Profit"
    End If
    If WorksheetFunction.CountIf(Range("A2:A" & LastRow), "*On Sale*") <> 0 Then
        Range("A1:B" & LastRow).AutoFilter Field:=1, Criteria1:="*On Sale*"
        Range("B2:B" & LastRow).SpecialCells(xlCellTypeVisible) = "Loss"
    End If
    Range("A1:B" & LastRow).AutoFilter Field:=1, Criteria1:="="
    Range("B2:B" & LastRow).SpecialCells(xlCellTypeVisible) = "No Data"
    If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
    Application.ScreenUpdating = True
End Sub

mumps You have need a giant help, it is working perfectly!! Thank you so much!

 

[KSolid]

mumps You have need a giant help, it is working perfectly!! Thank you so much!

Sorry misspelled it was meant to say You were a giant help. Had something else on my mind at the same time. Once again Mumps thank you so much for your help!

 

[mumps]

You are very welcome.