Vba concatenate 2 columns that have a currency

[VBA learner ITG]

Hi all,

Any advice would be appreciated on this!


Column P of my workbook is a numerical column and Column Q is a Currency sign indicator, I need to add the Currency sign indicator to the front of the cell in column P or at the end depending on the currency sign indicator.

£ = UK Pounds
P = UK Pence



[TABLE="width: 500"]
<tbody>[TR]
[TD]Column P[/TD]
[TD]Column Q[/TD]
[/TR]
[TR]
[TD]1.50[/TD]
[TD]£[/TD]
[/TR]
[TR]
[TD]50[/TD]
[TD]P[/TD]
[/TR]
</tbody>[/TABLE]


so the output would look like the below
[TABLE="width: 500"]
<tbody>[TR]
[TD]Column P[/TD]
[TD]Column Q[/TD]
[/TR]
[TR]
[TD]£1.50[/TD]
[TD]£[/TD]
[/TR]
[TR]
[TD]50P[/TD]
[TD]P[/TD]
[/TR]
</tbody>[/TABLE]


Is this achieving in column P without adding another column to the table using VBA?

I have tried the below code but it will only concatenate the value at the front of the cell value.

 For i = 2 To Cells(Rows.Count, "P").End(xlUp).Row
    Cells(i, "P").Value = Cells(i, "Q").Value & "" & Cells(i, "P").Value
Next i

 

[JackDanIce]

A suggestion:

For i = 2 To Cells(Rows.Count, "P").End(xlUp).Row
    Select Case Cells(i, "Q").Value
        Case Is = "£": Cells(i, "P").Value = Cells(i, "Q").Value & Cells(i, "P").Value
        Case Is = "p": Cells(i, "P").Value = Cells(i, "P").Value & Cells(i, "Q").Value
    End Select
Next i

 

[VBA learner ITG]

Hi JackDanIce,

Thank you for your help, i have learnt something new today

A suggestion:

For i = 2 To Cells(Rows.Count, "P").End(xlUp).Row
    Select Case Cells(i, "Q").Value
        Case Is = "£": Cells(i, "P").Value = Cells(i, "Q").Value & Cells(i, "P").Value
        Case Is = "p": Cells(i, "P").Value = Cells(i, "P").Value & Cells(i, "Q").Value
    End Select
Next i

 

[footoo]

Another way :

Dim p$: p = Range("P2:P" & Cells(Rows.Count, "P").End(3).Row).Address
Dim q$: q = Range(p).Offset(0, 1).Address
Range(p) = Evaluate("IF(" & q & "=""P""," & p & "&" & q & "," & q & "&" & p & ")")

 

[VBA learner ITG]

hi Footoo

Thank you for your assistance, both codes have been very helpful and useful as i have learnt something new.

Another way :

Dim p$: p = Range("P2:P" & Cells(Rows.Count, "P").End(3).Row).Address
Dim q$: q = Range(p).Offset(0, 1).Address
Range(p) = Evaluate("IF(" & q & "=""P""," & p & "&" & q & "," & q & "&" & p & ")")