Returning Cell Reference/link

[Fishnerd]

This seems like it should be fairly simple, but for the life of me, I can
figure out the correct command. I'm writing a macro that loops through a
database comparing all rows and when the a statement is true, and if it is,
it adds a reference/link to the contents of Cell(y, r) to the current formula
of Cell(x, qr). This way, if Cell(y,r) changes later, Cell(x,qr)
automatically changes as well. The end result is each cell in column qr will
have formulas that are the equivelent of =SUM(B3,B12,B56).

Here is what I've got so far... "Ref" in the macro is simply what I wish the
command was, but unfortunately it doesn't exist...

Do While Cells(x, name).Value <> ""
Do While Cells(y, name).Value <> ""
If (Cells(x, name).Value = Cells(y, name).Value) Then
Cells(x, qr).Formula = Cells(x, qr).Formula + Cells(y, r).Ref
y = y + 1
Else
y = y + 1
End If
Loop
x = x + 1
y = 2
Loop

Thanks for any help you can offer. I hope I at least made a little sense in
my explanation... couldn't think how to better word it...

 

[OssieMac]

Hi,

The following is the logic of what I believe you need to do. It takes a sum
formula in cell A1, =SUM(B1,D1,E1) and adds cell F1 to the formula,
=SUM(B1,D1,E1,F1)

There may be better ways so you might get a better answer from someone else.
As some added advice, you should not use name as a variable because it is a
reserved word.

'Add a cell address to SUM formula

Dim strFormula As String
Dim lngFormula As Long

'Assign formula to a string variable
strFormula = Cells(1, 1).Formula

'Assign number of characters to a variable
lngFormula = Len(strFormula)

'Subtract 1 from the number of characters
lngFormula = lngFormula - 1

'Assign characters without last bracket to string variable
strFormula = Left(strFormula, lngFormula)

'Concatenate the string variable with comma, the
'added address and the the last bracket
strFormula = strFormula & "," & Cells(1, 6).Address(0, 0) & ")"

'Assign formula to a cell
Cells(1, 1) = strFormula

 

[Fishnerd]

Thanks,
That worked like a charm... but it brings to light a fundamental flaw in my
original goal...
Everytime I sort, the formulas no longer match up with the original cells
and all my numbers are messed up until I run the macro again.

Is there a different way to do this that will cause the cells to stay linked
even while sorting?

Here is my current Macro:

Sub testing()

ActiveSheet.Evaluate("Name").Select
nm = ActiveCell.Column
ActiveSheet.Evaluate("Quantity").Select
r = ActiveCell.Column
ActiveSheet.Evaluate("Quantity_All_Types").Select
qr = ActiveCell.Column

Dim strFormula As String
Dim lngFormula As Long

Rows("2:2").Select
x = ActiveCell.Row

Do While Cells(x, nm).Value <> ""
Do While Cells(y, nm).Value <> ""
If (Cells(x, nm).Value = Cells(y, nm).Value) Then
If (Cells(x, qr).Formula = "") Then
Cells(x, qr).Formula = "=SUM(" & Cells(y, r).Address(0, 0) & ")"
Else
strFormula = Cells(x, qr).Formula
lngFormula = Len(strFormula)
lngFormula = lngFormula - 1
strFormula = Left(strFormula, lngFormula)
strFormula = strFormula & "," & Cells(y, r).Address(0, 0) & ")"
Cells(x, qr) = strFormula
End If
y = y + 1
Else
y = y + 1
End If
Loop
x = x + 1
y = 2
Loop
End Sub

Thanks again for your assistance with this!

 

[OssieMac]

I don't know if it will help because I don't know if you initially inserted
these formulas on the worksheet or they have all been done with macros.
However, absolute cell addressing keeps the formulas addressing the original
cells.

You use the $ signs with the cell address like $C$6.

Other forms of absolute addressing are $C6 which only keeps the column
constant but the row changes. C$6 keeps the row constant but the column
changes.

When entering the addresses directly in Worksheet formulas, while the
address is highlighted, press F4 to move through the options of
relative/abolute options.

When using Address in VBA the various syntax is as follows:-

Range("C6").Address(0,0) returns C6
Range("C6").Address(1,0) returns C$6
Range("C6").Address(0,1) returns $C6
Range("C6").Address(1,1) returns $C$6

 

[Fishnerd]

The absolute cell addressing allows the cells with formulas to update to the
correct reference cell when I insert/delete rows/columns or when I copy and
paste, but they still don't update when I sort. ex:
=SUM($A$21,$A$34,$A$52,$A$65,$A$73) stays exactly the same after sorting,
resulting in adding together a bunch of wrong numbers. Oh, and to answer
your question, the cells in column r are simply real number constants... no
formulas at all.

Any idea what might allow these to update when sorting or another way of
going about this to achieve the same goal?

Thanks again OssieMac!

 

[OssieMac]

Fresh out of any more ideas.

 

[Fishnerd]

I appreciate your help with this, you've gotten me almost the whole distance.
By chance, do you know of a way to set things up so that this macro runs
automatically whenever a "sort" occurs? That should solve my dilemma.

Thanks again!

 

[OssieMac]

You could set up a Command Button to run the sort which could be the first
part of the macro with the your macro running after the sort is finished or
vice versa.

 

[Fishnerd]

That will work! I was wanting to set up buttons to run the sorts anyway as I
was wanting to sort by more then 3 variables!

Thanks again for your help!