Placing concatenate in formula in cell

RayLBailey
RayLBailey used Ask the Experts™
on
Good day!
I am trying to place a concatenate formula into a cell. It needs to pull the number from the cell above, and add a dot and the incremental number from the For Next  (  "i" in the formula.  But I can'tseem to get the right combinations of parenthesis to make it work.  Any help?


"i" is the increment.
A number is in the cell R[-1]C above. ie "9".
The result should be   9.1  and the next time through 9.2   etc.

     ActiveCell.Offset(1, 2).Select
     ActiveCell.FormulaR1C1 = "=(CONCATENATE(R[-1]C,""."" & i & ""))"

BTW (My browser won't let me select a lower point value, so enjoy the 500 points for this question!)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
ActiveCell.FormulaR1C1 = "=CONCATENATE(R[-1]C,"".""," & i & ")"
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
If you are trying to loop down a column, then you might need to get the integer value of the cell above before concatenating, such as with:
ActiveCell.FormulaR1C1 = "=CONCATENATE(INT(--R[-1]C),"".""," & i & ")"

Author

Commented:
Cool Beans!  The first one works like a charm.  Now that I see what you did it makes sense.
Thank you!

Ray
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
Ray,
When I am trying to put a formula in a cell, I often get things bollixed. To help debug, I use a string variable for the formula. That way, I can look at the formula in the Immediate pane of the VBA Editor.

For example, you might be testing the following code:
Dim frmla As String
frmla = "=(CONCATENATE(R[-1]C,""."" & i & ""))"
ActiveCell.FormulaR1C1 = frmla              'Put a stop in the code at this statement

To put a stop in the code, click in margin to left of the statement and a big maroon dot will then appear. When you run your macro, execution will stop just before executing this statement--and you will be dumped into the VBA Editor.

To use the Immediate pane, first make sure it is displayed using the View...Immediate pane menu item. You can then evaluate a calculation with:
? "=(CONCATENATE(R[-1]C,""."" & i & ""))"      (hit Enter to make the statement evaluate)

You can also hover the cursor over a variable to see its value.

Either way, you'll see that a valid string is being created--but not one that looks like a worksheet formula. You can then adjust the code to get matters under control.

Brad

Author

Commented:
Okay,
For the first time I understand how the Immediate Pane works. I tried it a while back and couldn't figure it out.

Yes, I often use a test string or variable to track how something works, but with the Immediate Pane it makes it so much easier.

Here's a swig of my coffee to you!

Ray
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
Ray,
With the immediate pane, you can have multiple statements on the same line. Separate each one by a colon.
frmla = "=(CONCATENATE(R[-1]C,""."" & i & ""))" : ?frmla

The first statement puts the result of the string expression into variable frmla. The second statement displays the value.

In a pinch, I have even done complete For...Next loops in the Immediate pane:
For i = 1 To 28 : Cells(i, 1).Value = Environ(i) : Next             'Lists the Environ parameter and its value

Brad

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial