[VBA EXPERTS NEEDED] Help with VBA, please

[barclay34]

Hello
Hopefully someone will be able to help

I have a worksheet and i need some <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-top-style: initial; border-right-style: initial; border-bottom-style: dotted; border-left-style: initial; border-top-color: initial; border-right-color: initial; border-bottom-color: rgb(0, 0, 0); border-left-color: initial; border-image: initial; font-variant-numeric: normal; font-variant-east-asian: normal; cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">vba</acronym> code that will do the following:
If cell value in column B = X
And the next cell value in column B = Y or Z,
then replace Y with XY and Z with XZ

And
If cell value in column B = XY or XZ
And the next cell value in column B = X or Z,
then replace Y with XY and Z with XZ

otherwise do nothing

Example:
[TABLE="class: grid, width: 150, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]z[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]y[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]y[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]y[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]y[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]z[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]y[/TD]
[/TR]
</tbody>[/TABLE]
to be

[TABLE="class: grid, width: 150, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]z[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]y[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]xy[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]xy[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]xy[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]xz[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]y[/TD]
[/TR]
</tbody>[/TABLE]
Thank you.

 

[MickG]

Try :-

[COLOR="Navy"]Sub[/COLOR] MG12Jan58
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Temp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range("B2", Range("B" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Dn.Value = "x" [COLOR="Navy"]Then[/COLOR]
        Temp = "x"
    [COLOR="Navy"]ElseIf[/COLOR] Dn.Value = "" [COLOR="Navy"]Then[/COLOR]
        Temp = ""
     [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]If[/COLOR] Dn.Value = "y" [COLOR="Navy"]Then[/COLOR] Dn.Value = Temp & "y"
        [COLOR="Navy"]If[/COLOR] Dn.Value = "z" [COLOR="Navy"]Then[/COLOR] Dn.Value = Temp & "z"
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[wideboydixon]

I'm not sure this needs an "expert" as it's a fairly simple exercise. Not sure about the casing of your text (X or x?) so this should deal with both.

Public Sub XYZ()

Dim lastRow As Long
Dim thisRow As Long
Dim lastValue
Dim thisValue

lastRow = Cells(Rows.Count, "B").End(xlUp).Row
lastValue = LCase(Range("B1").Value)
For thisRow = 2 To lastRow
    thisValue = LCase(Cells(thisRow, "B").Value)
    If (thisValue = "y" Or thisValue = "z") And (lastValue = "x" Or lastValue = "xy" Or lastValue = "xz") Then
        Cells(thisRow, "B").Value = IIf(Cells(thisRow, "B").Value > "x", "x", "X") & Cells(thisRow, "B").Value
    End If
    lastValue = LCase(Cells(thisRow, "B").Value)
Next thisRow

End Sub

WBD

 

[barclay34]

Thanks a lot! It's working!!!

 

[MickG]

You're welcome