How to: Identify Range And Then Last Cell In Range And Insert Absolute Cell Address Into R1C1 Formula

Question

Long-time lurker, first time poster since this is my first real VBA macro, and I have encountered a roadblock I have not been able to overcome.

I have an excel workbook with worksheets formatted as follows:

I have been trying to write some code that will populate the "% Weight" column with a formula that will divide the value of the adjacent cell in "Weight" column by the value of the cell that contains the sum function below each range of cells.

I have dozens of tables on a sheet, divided by a few blank rows, all formatted like this vertically. I need the cells to identify the correct sum cell and divide the offset cell by the value.

I have tried the below code.

Basically I tried to run a For Each loop through the "% Weight" column and identify when the adjacent cell in "Weight" was not empty. Then it would identify the offset cell by setting a range variable, and then set another variable to identify the final cell in the range therefore identifying the cell containing the sum formula.

I do know that my If logic is working tho, as I had to populated "% Weight" column with a value of "1" if there was an adjacent cell and that worked.

I keep getting error 424 or type mismatch.

Code block providing issues:

Dim cell As Range, rng2 As Range, sideweight As Range, TargetWeight As Range
Dim TargetWeightr As Long, Dim TargetWeightc As Long

rng2 = Range("D1:D" & LR)


For Each cell In rng2
    If cell.Offset(0, -1).Value <> "" Then
    Set sidewight = cell.Offset(0, -1)
    Set TargetWeight = sideweight.End(xlDown)
    Set TargetWeightr = TargetWeight.Address.Row
    Set TargetWeightc = TargetWeight.Address.Column
    'cell.FormulaR1C1 = "=RC[-1]/R[" & TargetWeightr & "]C[" & TargetWeightc & "]"
    End If
Next cell

Entire Macro For Context:

Sub WeightCalculations2()

Application.ScreenUpdating = False


Dim rng As Range, cell As Range, rng2 As Range, rA As Range, totalweight As Range, totalweightrng As Range
Dim sideweight As Range, TargetWeight As Range
Dim LR As Long, TargetWeightr As Long, TargetWeightC As Long

Dim ws As Worksheet

Set ws = ActiveSheet

With ActiveSheet
    LR = Cells(Rows.Count, "A").End(xlUp).Row
End With

Set rng = ws.Range("I2:I" & LR)
Set rng2 = ws.Range("J2:J" & LR)

For Each cell In rng
    If cell.Offset(0, -1).Value = "EA" Then cell.FormulaR1C1 = "=RC[-2]*RC[3]"
    If cell.Offset(0, -1).Value = "LB" Then cell.FormulaR1C1 = "=RC[-2]*1"
Next cell

For Each cell In rng
    If WorksheetFunction.IsError(cell) Then cell.Formula = "=1*0"
Next cell



For Each rA In Columns("I").SpecialCells(xlFormulas).Areas
    rA.Cells(rA.Cells.Count + 1).Formula = "=SUM(" & rA.Address & ")"
Next rA


For Each cell In rng2
    If cell.Offset(0, -1).Value <> "" Then
    Set sidewight = cell.Offset(0, -1)
    Set TargetWeight = sideweight.End(xlDown)
    Set TargetWeightr = TargetWeight.Address.Row
    Set TargetWeightC = TargetWeight.Address.Column
    'cell.FormulaR1C1 = "=RC[-1]/R[" & totalweightrn & "]C[" & totalweightcn & "]"
    End If
Next cell


End Sub

Expected Output: The program populates the cells in the column "% Weight" with the formula dividing the value of the corresponding offset cell in the "Weights" column by the value of the cell containing the sum for the corresponding range of cells.

Actual Output: Error 424 and/or Error Mismatch.

Please let me know what other information would be useful in answering this question, doing my best to make this comprehensive.

Unknown Unknown · Accepted Answer · 2019-03-24T22:22:28

TargetWeight.Address.Row should be TargetWeight.Row
TargetWeight.Address.Column should be TargetWeight.Column
When you create an xlR1C1 style address, the n inside [n] is a relative row or column adjustment. RC[-1] means same row, one column left. You want an absolute address and you have absolute row and column as long integers so R" & totalweightr & "C" & totalweightc

You don't Set integer values, you assign them with an =. You only Set objects like ranges, cells, worksheets, etc.

For Each cell In rng2
    If cell.Offset(0, -1).Value <> "" Then
        Set sidewight = cell.Offset(0, -1)
        Set TargetWeight = sideweight.End(xlDown)
        TargetWeightr = TargetWeight.Row
        TargetWeightc = TargetWeight.Column
        cell.FormulaR1C1 = "=RC[-1]/R" & TargetWeightr & "C" & TargetWeightc
    End If
Next cell

You might also want to forget all of the manipulation and just use TargetWeight.Address in xlR1C1 style.

For Each cell In rng2
    If cell.Offset(0, -1).Value <> "" Then
        Set sideweight = cell.Offset(0, -1)
        Set TargetWeight = sideweight.End(xlDown)
        cell.FormulaR1C1 = "=RC[-1]/" & TargetWeight.Address(referencestyle:=xlR1C1)
    End If
Next cell

How to: Identify Range And Then Last Cell In Range And Insert Absolute Cell Address Into R1C1 Formula

1 Answers