Formula/VBA help

[blackie42]

Hi Guys,

Would like to have a macro that ideally does the following (or alternatively a formula).

Starting from row 2 (as row 1 has headers). So

in cells A2 to A5000 will have the string "sw"
in cells B2 to B5000 will have either "sell" or "buy"
in cells C2 to C5000 the formula (ideally some code) will look to change the "sw & sell" taken from cells A & B to switchout in cell C
or "sw buy" taken from cells A & B to switchin in cell C.


Any help will be much appreciated..

thanks
Jon

[blackie42]

So have achieved this by a helper column but macro much better/quicker

in C2 =CONCATENATE(A2,B2) & then in D2 = IF(C2="swsell", "switchout","switchin"), helper column could be anywhere I suppose

Any help with VBA code to replicate (using helper column if needs be) appreciated

thanks
Jon

[Kenneth Hobs]

Of course you can do all that in just one formula.

By VBA, here are 2 methods.

Sub Main()
Dim c As Range
For Each c In Range("A2", Cells(Rows.Count, "A").End(xlUp))
With c
.Offset(, 2).Value2 = swIO(.Value2, .Offset(, 1).Value2)
End With
Next c
End Sub


'=swio(A2,B2)
Function swIO(sw As String, io As String) As String
Application.Volatile False
Select Case sw & io
Case "swsell"
swIO = "switchout"
Case "swbuy"
swIO = "switchin"
Case Else
swIO = ""
End Select
End Function

[blackie42]

Excellent - thanks very much

regards
Jon