How to concantenate R1C1, address variable, and a space together using VBA?

I have been trying 1,000 different ways  to get my answer, but 4 hours is enough to drive anyone crazy. All im doing is concatenate the value from C[-1] , a space, then the address of a value stored as an address variable, and a simple string at the end. It sounds easy, but i just cannot get it.

End Result:

=CONCATENATE(@E:E & " " & $G17 & "xHS")

The @E:E is the C[-1]

The $G17 is the address variable

The xHS is the simple string

So far, the closest i have gotten is:

tempAddress = Cells(17, 7).Address(RowAbsolute:=False)

    ActiveCell.Formula = "=CONCATENATE(RC[-1]& "" ""&""" & tempAddress & """&""xHS"")"

But, that only gets me:

3040 $G17xHS  (3040 is the value of the cell 1 to the left)


What i want to see in the cell at the end is:

3040 1xHS

3040 being C[-1], the 1 being the address $G17, and that simple string

There are no answers yet.
Be the first to answer this question.