Lock cell reference in a formula in VBA

[mlibrett]

I have a few formulas in my VBA code that I would like to lock. For example if the cell was A4 I want it to be $A$4 in the code.

"=+SUMIF('PCM Portfolio Input Paste'!R10C24:R830C24,Data!R9C2:R17C2,'PCM Portfolio Input Paste'!R10C40:R619C40)"

Thats what my formula looks like when I recorded the macro

 

[jfish1288]

The code you posted does have the reference cells in the formula "locked". For example "R10C24:R830C24" would translate to "$X$10:$X$830".

When using .FormulaR1C1 format if you want the reference cells to not be "locked" you would use R[#]C[#]. For example if I wanted cell B10 to have the formula "=sum($B$4:$B$9)" the .FormulaR1C1 would be "=sum(R4C2:R9C2)". If I wanted cell B10 to have the formula "=sum(B4:B9)" the .FormulaR1C1 would be "=sum(R[-6]C[0]:R[-1]C[0])".

 

[jh0]

Not sure if I should post a question on a question but here it goes..

How do you lock cell references when you aren't using the RC format? In this case I used variables to define the top and bottom of a range and have been trying to concatenate it to make sense:

([B]U" & Top & "[/B]:U" & ActiveCell.Row - 1 & ",U" & ActiveCell.Row + 1 & ":[B]U" & Bottom &[/B] ")

There is some logic included, but when I apply the formula down the entire range the variables shift. I want the bolded areas to remain locked on the range specified. Not sure why I am having a problem because "Top" and "Bottom" are predefined to be 3 and 40000.

Thanks, and sorry if this isn't proper forum etiquette but I figure it fits well.

 

[jfish1288]

It might be more helpful if you post more of your code

 

[jh0]

Not sure if I should post a question on a question but here it goes..

How do you lock cell references when you aren't using the RC format? In this case I used variables to define the top and bottom of a range and have been trying to concatenate it to make sense:

([B]U" & Top & "[/B]:U" & ActiveCell.Row - 1 & ",U" & ActiveCell.Row + 1 & ":[B]U" & Bottom &[/B] ")

There is some logic included, but when I apply the formula down the entire range the variables shift. I want the bolded areas to remain locked on the range specified. Not sure why I am having a problem because "Top" and "Bottom" are predefined to be 3 and 40000.

Thanks, and sorry if this isn't proper forum etiquette but I figure it fits well.

Sub Step5()
'
' Step5 Macro
' Macro recorded 5/23/2012 by
'
' Keyboard Shortcut: Ctrl+e
' Spread additional cost to orders.
'


Range("Z10").Select
Selection.End(xlUp).Select
Dim Top As Long
Top = ActiveCell.Row + 1
Selection.End(xlDown).Select
Dim Bottom As Long
Bottom = ActiveCell.Row


Dim vData
Dim Order
'Identify number of orders and data range
'orders = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row - 1
'lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Range("E1") = Bottom - Top 'sets value for use in formula
'Prompt for additional cost
    On Error Resume Next
        Application.DisplayAlerts = False
   vData = Application.InputBox _
             (Prompt:="Please enter additional cost to be added.", _
             Title:="Additional Cost", Type:=1 + 8)
    
    On Error GoTo 0
        Application.DisplayAlerts = True
    If IsNumeric(vData) And vData <> 0 Then
    Range("F1").Select 'Sets value for use in formula
    ActiveCell.FormulaR1C1 = vData
    Else
    Exit Sub
    End If
 
 'Formula to calculate split
 
 
 
[B] Range("U" & Top).Select
 Range("U" & Top & ":U" & Bottom).Formula = "=IF((ROUNDUP($F$1/$E$1,2)+SUM(U$" & Top & ":U" & ActiveCell.Row - 1 & ",U" & ActiveCell.Row + 1 & ":U$" & Bottom & "))>$F$1,$F$1-(SUM(U$" & Top & ":U" & ActiveCell.Row - 1 & ",U" & ActiveCell.Row + 1 & ":U$" & Bottom & ")),ROUNDUP($F$1/$E$1,2))"
 [/B]
 'Range("U4:U" & Bottom).Select
 'Selection.FillDown
 'Selection.Copy
 'Selection.PasteSpecial xlPasteValues
 
Range("E1:F1").ClearContents

End Sub

I've since tried concatenating in the $ symbol, but It is not behaving as I would like. This is the entire macro. Again I've bolded the trouble code. basically I am using the macro to allocate a cost "F1" over the number of orders "E1"

The issue is that the amount is very small and is typically rounded up, and so the cost averaging only a few cents is not applied to the entire range of orders. That logic is what I am attempting to capture in the bold formula. There is logic in there to only add the cost (3cents) to the order if the total order cost is not totaled in the column so far. Having trouble avoiding circular references, and I attempted to circumvent that by having it sum up everything above and under the activecell within the "Top" "Bottom" range. Need help locking it as intended. Not sure if that is the only issue or if i am having a tough time because the formula is not referencing the proper ActiveCell all through the range.

Hope this wasn't too much.
Thanks

 

[jfish1288]

Okay feel free to correct me if I have something wrong here.

Looking at your code, it looks like you are trying to end up with a formula in each cell in column U from row "Top" to row "Bottom", correct?

Within that formula you want to sum the range U"Top" to U"Bottom", excluding the cell of the current formula, correct?

There are three issues I see:
1)I am not sure but I think what you are doing (summing the range U"Top" to U"Bottom", excluding the cell of the current formula) could be dangerous if your range from "Top" to "Bottom" is not small because it would create circular formulas where when a cell is updated every other cell has to be updated, which in turn causes all other cells to be updated again, and again. I could be wrong and excel could handle this just fine. Also is it possible that you meant to sum column Z and have the output in column U?

2) You start at U"Top" with the command

Range("U" & Top).Select

and have the following in your formula

U$" & Top & ":U" & ActiveCell.Row - 1

Assuming "Top" = 2, you end up with trying to sum "U$2:U1" which is backwards and gets switched by excel to "U1:U$2"

3)You have the same backwards situation as #2 when you get to U"Bottom"

Let me know if I can help more.

 

[jh0]

Okay feel free to correct me if I have something wrong here.

Looking at your code, it looks like you are trying to end up with a formula in each cell in column U from row "Top" to row "Bottom", correct?

Within that formula you want to sum the range U"Top" to U"Bottom", excluding the cell of the current formula, correct?

There are three issues I see:
1)I am not sure but I think what you are doing (summing the range U"Top" to U"Bottom", excluding the cell of the current formula) could be dangerous if your range from "Top" to "Bottom" is not small because it would create circular formulas where when a cell is updated every other cell has to be updated, which in turn causes all other cells to be updated again, and again. I could be wrong and excel could handle this just fine. Also is it possible that you meant to sum column Z and have the output in column U?

2) You start at U"Top" with the command

Range("U" & Top).Select

and have the following in your formula

U$" & Top & ":U" & ActiveCell.Row - 1

Assuming "Top" = 2, you end up with trying to sum "U$2:U1" which is backwards and gets switched by excel to "U1:U$2"

3)You have the same backwards situation as #2 when you get to U"Bottom"

Let me know if I can help more.

I edited out the code that was accounting for the data after the selected cell because it wasn't necessary (If formula wasn't run yet, cell is blank regardless.)

I am summing the range from U"TOP" to one row above the active cell to accommodate the logic doing the calculation. I had it running correctly before in excel and it was working perfectly, but now that I'm fine tuning the VBA portion of it I'm having some grammatical issues that I think are caused by my quote location but I'm not sure.

I did run into the issue that you mentioned in point 2 which is why I introduced the variable Top2 which is one cell above Top and allows me to sum the range and avoid a circular reference in the first cell.

Also, the only reason column z was mentioned was to find the dimensions of the range in U.

My code currently looks like this:

Sub Step5()
'
' Step5 Macro
' Macro recorded 5/23/2012 by
'
' Keyboard Shortcut: Ctrl+e
' Spread additional cost to orders.
'


Range("Z10").Select
Selection.End(xlUp).Select
Dim Top As Long
Top = ActiveCell.Row + 1
Selection.End(xlDown).Select
Dim Bottom As Long
Bottom = ActiveCell.Row


Dim vData
Dim Order
'Identify number of orders and data range
'orders = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row - 1
'lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Range("E1") = Bottom - Top 'sets value for use in formula
'Prompt for additional cost
    On Error Resume Next
        Application.DisplayAlerts = False
   vData = Application.InputBox _
             (Prompt:="Please enter additional cost to be added.", _
             Title:="Additional Cost", Type:=1 + 8)
    
    On Error GoTo 0
        Application.DisplayAlerts = True
    If IsNumeric(vData) And vData <> 0 Then
    Range("F1").Select 'Sets value for use in formula
    ActiveCell.FormulaR1C1 = vData
    Else
    Exit Sub
    End If
 
 'Formula to calculate split
 
 Dim Top2 As Long
 Top2 = Top - 1
 'Dim Bottom2 As Long
 'Bottom2 = Bottom + 1
 
 
 
 
 Range("U" & Top).Select
 [B]Range("U" & Top & ":U" & Bottom).Formula = "=IF((ROUNDUP($F$1/$E$1,2)+SUM(U$"" & Top2 & "":U"" & ActiveCell.Row - 1""))>$F$1,$F$1-(SUM(U$"" & Top2 & "":U"" & ActiveCell.Row - 1 & "")),ROUNDUP($F$1/$E$1,2))"
 [/B]
 'Range("U4:U" & Bottom).Select
 'Selection.FillDown
 'Selection.Copy
 'Selection.PasteSpecial xlPasteValues
 
'Range("E1:F1").ClearContents

End Sub

The problem child is bolded. I know that I have been dealing with this particular code for a while and I think that the problem has something to do with the quoted. I get a 1004 error saying user or object defined error. If i take away the double quotes within the string I get the "Expected statement end" error message.

 

[jfish1288]

Your 1004 error stems from double quotations and a lack of an & symbol. Replacing your bolded line with the following should fix that error.

 Range("U" & Top & ":U" & Bottom).Formula = "=IF((ROUNDUP($F$1/$E$1,2)+SUM(U$" & Top2 & ":U" & ActiveCell.Row - 1 & "))>$F$1 ,$F$1-(SUM(U$" & Top2 & ":U" & ActiveCell.Row - 1 & ")),ROUNDUP($F$1/$E$1,2))"

 

[jh0]

Thanks a lot Jfish. Awesome solution.

~Joe