Conditional concatenation in VB

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to add a small element to a macro whereby a cell in column A will concatenate. across the same row, the cells in column B and C, provided the cell in C contains the word "Total", else the cursor moves down by one cell. The loop should run for as long as the cell in column C does not equal null

Example - with the contentS in A1 and A4 being the desired resul
A B
EQUITY TOTAL (35.92%) 35.92% EQUITY TOTAL
AAA Company 2.35% EQUIT
BBB Inc. 5.36% EQUIT
BOND TOTAL (60.23%) 60.23% BOND TOTA
CCC Company 5.33% BON
DDD Inc. 12.56% BON

Can somebody please help? Thanks
J
 
Try a formula like

=IF(ISNUMBER(SEARCH("TOTAL",C2)),C2 & " (" & TEXT(B2,"0.00%") & ")")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

JC said:
I am trying to add a small element to a macro whereby a cell in column A
Click to expand...
will concatenate. across the same row, the cells in column B and C, provided
the cell in C contains the word "Total", else the cursor moves down by one
cell. The loop should run for as long as the cell in column C does not equal
null.
 
Why didn't you say.

Here's some VBA

Sub CheckValues()
Dim i As Long

For i = 2 To Cells(Rows.Count, "C").End(xlUp).Row
If InStr(1, Cells(i, "C").Value, "TOTAL") Then
Cells(i, "A").Value = Cells(i, "C").Value & " (" &
Format(Cells(i, "B").Value, "0.00%") & ")"
End If
Next i

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi JC,

The cursor won't move because the code is using indexing rather than moving
about, so it changes cells 1 row down, then 2, etc, without ever moving the
cursor.

You should however see the values appearing in column A when they need to
change. Is this not the case?

By the way, re-reading your post I see I am staring at the wrong row, it
should be 1 not 2, so the code should be

Sub CheckValues()
Dim i As Long

For i = 1 To Cells(Rows.Count, "C").End(xlUp).Row
If InStr(1, Cells(i, "C").Value, "TOTAL") Then
Cells(i, "A").Value = Cells(i, "C").Value & " (" &
Format(Cells(i, "B").Value, "0.00%") & ")"
End If
Next i

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

JC said:
Hi Bob, thanks for helping me out on this. I tried to use the VBA but
Click to expand...
could't get it to run - using Step-in in VB, I can see the cursor loops up
and down, but there is no corresponding movement in EXcel. The cursor in
Excel just stays where it is, whether on top of column A, B, or C.
 
That change was only to capture the first row, it wasn't a fault stopping it
working.

It works okay in my test, so I am not sure why it doesn't work for you. Do
you want to send me the workbook to look at ( bob . phillips @ tiscali . co
.. uk - remove the spaces)?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

JC said:
I see. But, there is still nothing happening in column A after I changed
Click to expand...
i = 2 to i = 1. No concatenation took palce.
 
Back
Top