How to fully qualify all Cells() references

[suremac]

Greetings,

When I run this macro on its own it executes correctly:

    Sub StateBIPDData()
    Dim sheet_name As Range
    For Each sheet_name In Sheets("WS").Range("B:B")
    If sheet_name.Value = "" Then
        Exit For
    Else
    With Sheets(sheet_name.Value)
    '***********************
    'Format BI data for states
    '***********************
    'Countrywide Earned Car Years
    .Cells(14, 2) = (Cells(53, 11))
    .Cells(15, 2) = (Cells(49, 11))
    .Cells(16, 2) = (Cells(45, 11))
    .Cells(17, 2) = (Cells(41, 11))
    .Cells(18, 2) = (Cells(37, 11))
    'Paid Claim Frequency (Claim Frequency) = (Number of Paid Claims ÷ Earned House Years) ×     100
    .Cells(14, 3) = (Cells(53, 17))
    .Cells(15, 3) = (Cells(49, 17))
    .Cells(16, 3) = (Cells(45, 17))
    .Cells(17, 3) = (Cells(40, 17))
    .Cells(18, 3) = (Cells(37, 17))
    '.Range("C14:C18").Copy
    '.Range("C14:C18").PasteSpecial xlPasteValues
    'Average Loss (Claim Severity)  = Paid Losses ÷ Number of Paid Claims
    .Cells(14, 4) = (Cells(53, 15))
    .Cells(15, 4) = (Cells(49, 15))
    .Cells(16, 4) = (Cells(45, 15))
    .Cells(17, 4) = (Cells(41, 15))
    .Cells(18, 4) = (Cells(37, 15))
    '.Range("D14:D18").Copy
    '.Range("D14:D18").PasteSpecial xlPasteValues
    'Pure Premium (Loss Cost) = Paid Losses ÷ Earned House Years
    .Cells(14, 5) = (Cells(53, 19))
    .Cells(15, 5) = (Cells(49, 19))
    .Cells(16, 5) = (Cells(45, 19))
    .Cells(17, 5) = (Cells(41, 19))
    .Cells(18, 5) = (Cells(37, 19))
    '.Range("E14:E18").Copy
    '.Range("E14:E18").PasteSpecial xlPasteValues
    '.Range("K37:S56").Clear
    '***********************
    'Format PD data for states
    '***********************
    'Paid Claim Frequency (Claim Frequency) = (Number of Paid Claims ÷ Earned House Years) × 100
    .Cells(14, 6) = (Cells(30, 13))
    .Cells(15, 6) = (Cells(26, 13))
    .Cells(16, 6) = (Cells(22, 13))
    .Cells(17, 6) = (Cells(18, 13))
    .Cells(18, 6) = (Cells(14, 13))
    '.Range("F14:F18").Copy
    '.Range("F14:F18").PasteSpecial xlPasteValues
    'Average Loss (Claim Severity)  = Paid Losses ÷ Number of Paid Claims
    .Cells(14, 7) = (Cells(30, 11))
    .Cells(15, 7) = (Cells(26, 11))
    .Cells(16, 7) = (Cells(22, 11))
    .Cells(17, 7) = (Cells(18, 11))
    .Cells(18, 7) = (Cells(14, 11))
    '.Range("G14:G18").Copy
    '.Range("G14:G18").PasteSpecial xlPasteValues
    'Pure Premium (Loss Cost) = Paid Losses ÷ Earned House Years
    .Cells(14, 8) = (Cells(30, 15))
    .Cells(15, 8) = (Cells(26, 15))
    .Cells(16, 8) = (Cells(22, 15))
    .Cells(17, 8) = (Cells(18, 15))
    .Cells(18, 8) = (Cells(14, 15))
    '.Range("H14:H18").Copy
    '.Range("H14:H18").PasteSpecial xlPasteValues
    '.Range("K14:O33").Clear
    '***********************
    'Calculate 'Number of BI Claims Per 100 PD Claims"
    '***********************
    .Cells(14, 9) = (Cells(14, 3) / Cells(14, 6)) * 100
    .Cells(15, 9) = (Cells(15, 3) / Cells(15, 6)) * 100
    .Cells(16, 9) = (Cells(16, 3) / Cells(16, 6)) * 100
    .Cells(17, 9) = (Cells(17, 3) / Cells(17, 6)) * 100
    .Cells(18, 9) = (Cells(18, 3) / Cells(18, 6)) * 100
    '.Range("I14:I18").Copy
    '.Range("I14:I18").PasteSpecial xlPasteValues
    End With
        End If
    Next sheet_name
    End Sub

Although, when I run the code in a combined macro the code doesn't execute properly. In fact, nothing happens at all and the target cells are left blank. The data for the source cells is present. Any thoughts as to why the values are not showing up in the target cells? Thank you very much in advance. Here is the code of the combined macro:

Sub DataAutomation_AITrends()
    GetSourceSheets
    CreateCwBIDataCalculate
    InsertRowAllStates
    CopyPasteBIPDPIPData
    StateBIPDData
    StatePIPData
    DeleteWorkSheets
    Percent_change_CalculationAllSheets
    On Error Resume Next
    End Sub

 

[suremac]

If it's not clear. The target data and the source data is on the same sheet. I just need to format the data into different cells on the same sheet.

 

[ukmikeb]

Hi

You need to reference the cells on the right hand side of the equation/s as .Cells

Also, you can put the If .. Then and the Exit For statements on one line and remove the Else and End If statements.

hth

 

[suremac]

Thanks!!!! It worked!