Change Text in Cell by Value

[nhinx]

Dear Excel Experts,

I'm not good in excel VBA, I need help to activate the following formulation in VBA or Excel Function if it can do it. Please see below interpretation
The black font in the left is the scenario and the red font is the output.

if Cell(A1) = "DP" and Cell(A2) = "" or <>"" and Cell(A3) = "" or <>"" then Cell(B1) = Cell(A2) and "Downpayment" then [Cell(A3) = should be automatically empty or deleted or blank or no value]

A1 = "DP"	A1 = "DP"	A1 = "DP"	A1 = "DP"
A2 = "20%"	A2 = "20%"	A2 = ""	A2 = ""
A3 = "Maria"	A3 = ""	A3 = ""	A3 = ""
	B1 = 20% Downpayment		B1 = Downpayment

if Cell(A1) = "RB" and Cell(A2) = "" or <>"" and Cell(A3) = "" or <>"" then Cell(B1) = Cell(A2) and "Retention Billing" then [Cell(A3) = should be automatically empty or deleted or blank or no value]

A1 = "RB"	A1 = "RB"	A1 = "RB"	A1 = "RB"
A2 = "5%"	A2 = "5%"	A2 = ""	A2 = ""
A3 = "Jennifer"	A3 = ""	A3 = ""	A3 = ""
	B1 = 5% Retention Billing		B1 = Retention Billing

if Cell(A1) = "PB" and Cell(A2) = "" or <>"" and Cell(A3) = "" or <>"" then Cell(B1) = "Progress Billing No." and Cell(A2) then [Cell(A3) = should be automatically empty or deleted or blank or no value]

A1 = "PB"	A1 = "PB"	A1 = "PB"	A1 = "PB"
A2 = "6"	A2 = "6"	A2 = ""	A2 = ""
A3 = "Cristina"	A3 = ""	A3 = ""	A3 = ""
	B1 = Progress Billing No. 6		B1 = Progress Billing No.

if Cell(A1) = "FB" and Cell(A2) = "" or <>"" and Cell(A3) = "" or <>"" then Cell(B1) = "Final Billing" then [Cell(A2) and Cell(A3) = should be automatically empty or deleted or blank or no value]

A1 = "FB"	A1 = "FB"	A1 = "FB"	A1 = "FB"
A2 = "6"	A2 = ""	A2 = ""	A2 = ""
A3 = "Brotsador"	A3 = ""	A3 = ""	A3 = ""
	B1 = Final Billing		B1 = Final Billing

if Cell(A1) = "OT" and Cell(A2) = "" or <>"" then Cell(B1) = Cell(A3) then [Cell(A2) = should be automatically empty or deleted or blank or no value]

A1 = "OT"	A1 = "OT"	A1 = "OT"	A1 = "OT"
A2 = "25%"	A2 = ""	A2 = ""	A2 = ""
A3 = "Parakado"	A3 = "Parakado"	A3 = ""	A3 = ""
	B1 = "Parakado"		B1 = ""

Thank you in advance.

Kind regards,

Nhinx

 

[Rosen]

For B1 (without VBA) :=IF(A1="", "", IF(A1="DP",IF(A2="","",TEXT(A2,"0%") & " ") & "Downpayment",IF(A1="RB",IF(A2="","",TEXT(A2,"0%") & " ") & "Retention Billing",IF(A1="PB","Progress Billing No. " & A2,IF(A1="FB","Final Billing",IF(A1="OT",IF(A2="","",A3),"ERROR: Unknown value in cell A1"))))))

In terms of the output for column A, that cannot be done with formulas. Though, as a general guideline, it is considered bad form to manipulate your inputs. It might be good to have an input page and an output page where you can compare the inputs to the results to ensure things are working as intended.

 

[nhinx]

For B1 (without VBA) :=IF(A1="", "", IF(A1="DP",IF(A2="","",TEXT(A2,"0%") & " ") & "Downpayment",IF(A1="RB",IF(A2="","",TEXT(A2,"0%") & " ") & "Retention Billing",IF(A1="PB","Progress Billing No. " & A2,IF(A1="FB","Final Billing",IF(A1="OT",IF(A2="","",A3),"ERROR: Unknown value in cell A1"))))))

In terms of the output for column A, that cannot be done with formulas. Though, as a general guideline, it is considered bad form to manipulate your inputs. It might be good to have an input page and an output page where you can compare the inputs to the results to ensure things are working as intended.

Hi Rosen,

Thank you for the reply. So thus this mean it can't be done in VBA?

Regards,

Nhinx

 

[Rosen]

Sorry, I didn't mean to give the impression that it isn't doable with VBA, just not built in function inside an excel worksheet.

Here is a VBA function that should do the trick

Sub RunMyCode()
    
    Dim A1 As String
    Dim A2 As String
    Dim A3 As String
    Dim B1 As String
    
    Dim Sheet As Worksheet
    Set Sheet = Sheet1 ' Change this to the name of the sheet you are using
    
    A1 = Sheet.Range("A1").Value
    A2 = Sheet.Range("A2").Value
    A3 = Sheet.Range("A3").Value
    
    Select Case A1
        Case "DP"
            If Not A2 = "" Then
                B1 = A2 & " "
                A3 = ""
            End If
            B1 = B1 & "Downpayment"
        Case "RB"
            If Not A2 = "" Then
                B1 = A2 & " "
                A3 = ""
            End If
            B1 = B1 & "Retention Billing"
        Case "PB"
            B1 = "Progress Billing No."
            If Not A2 = "" Then
                B1 = B1 & " " & A2
                A3 = ""
            End If
        Case "FB"
            B1 = "Final Billing"
            A2 = ""
            A3 = ""
        Case "OT"
            B1 = A3
            A2 = ""
        Case Else
            B1 = "ERROR: Unknown value in cell A1"
    End Select
    
    Sheet.Range("A2").Value = A2
    Sheet.Range("A3").Value = A3
    Sheet.Range("B1").Value = B1
End Sub

 

[nhinx]

Sorry, I didn't mean to give the impression that it isn't doable with VBA, just not built in function inside an excel worksheet.

Here is a VBA function that should do the trick

Sub RunMyCode()
   
    Dim A1 As String
    Dim A2 As String
    Dim A3 As String
    Dim B1 As String
   
    Dim Sheet As Worksheet
    Set Sheet = Sheet1 ' Change this to the name of the sheet you are using
   
    A1 = Sheet.Range("A1").Value
    A2 = Sheet.Range("A2").Value
    A3 = Sheet.Range("A3").Value
   
    Select Case A1
        Case "DP"
            If Not A2 = "" Then
                B1 = A2 & " "
                A3 = ""
            End If
            B1 = B1 & "Downpayment"
        Case "RB"
            If Not A2 = "" Then
                B1 = A2 & " "
                A3 = ""
            End If
            B1 = B1 & "Retention Billing"
        Case "PB"
            B1 = "Progress Billing No."
            If Not A2 = "" Then
                B1 = B1 & " " & A2
                A3 = ""
            End If
        Case "FB"
            B1 = "Final Billing"
            A2 = ""
            A3 = ""
        Case "OT"
            B1 = A3
            A2 = ""
        Case Else
            B1 = "ERROR: Unknown value in cell A1"
    End Select
   
    Sheet.Range("A2").Value = A2
    Sheet.Range("A3").Value = A3
    Sheet.Range("B1").Value = B1
End Sub

Hi Rosen,

Thank you for the code. I've changed the Sub RunMyCode() to Private Sub Worksheet_Change(ByVal Target As Range) then it change as expected but afterwards excel is no longer responding.

 

[Rosen]

The code changes the values in cells a2, a3 and b1… invoking the worksheet_change event, effectively you’ve created an infinite loop. Considering you are looking for 3 inputs before you should be running the code I suggest finding a better trigger.

 

[Peter_SSs]

Any chance you could give us a series of sample values and the expected results with XL2BB and explain again in relation to those new samples? To me the format you have used is quite confusing & difficult to copy into a worksheet for testing.

Also confusing are expressions like this

and Cell(A2) = "" or <>""

This says cell A2 is empty or not empty. Obviously that is always true.