Thread: Help selecting Chart Area

Hello,

So currently I am writing a macro in vba to take raw tab delimited data and convert it into an interactive chart that calculates the distances between HaCaT cells being studied in time lapse images. The images are sorted by frame and the cells have x and y coordinates for each frame which are being used in a simple distance calculation.

Currently, I am trying to create my distance chart but have run into an issue. The number of cells in each data set is different therefore my chart is not a set size, it must vary according to the data set. Thus, I am in search of a way for my macro to detect the limits of my chart and apply my desired distance formula to every cell in the chart. I've tried counting the columns from the right much like you can with rows and End.(xlup) but have had no luck.

I have included an example workbook and my current code to help clarify any ambiguity in my question.

Sub DataChart()

Dim M   As Long
Dim LR  As Long
Dim LC  As Long
Dim rngCell As Range
Dim rCell As Range

'Preliminary spacing (this is something I added to all the macros but is in no way imperative to the program)

Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove

' Method to have excel identify the last data entry on the sheets

LR = Range("A" & Rows.Count).End(xlUp).Row
LC = Range("A" & Columns.Count).End(xlToRight).Column
M = Application.WorksheetFunction.Max(Range("A:A"))


'Clear intensity values that aren't used in our calculation

With Range("D4:F" & LR)
    .ClearContents
End With

'Label Trajectory Number for each cell through out all frames

With Range("D2:D" & LR)
    .FormulaR1C1 = _
        "=IF(R[-1]C[-3]=""%%"",R[-1]C[-1],IF(AND( ISNUMBER(RC[-3]),ISNUMBER(R[-1]C)),R[-1]C,""""))"
End With

'Formatting Labels

Range("A2:D2").Value = [{"Frame","X","Y","Cell #"}]

With Range("A2:D2")
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
        .Font.Bold = True
End With

'Create Table
Range("D2:D" & LR).Select
Selection.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
        "H3"), Unique:=True
        
Range("H3").ClearContents
Range("H4").Value = [{"Cell #"}]

With Range("H4")
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
        .Font.Bold = True
End With

Range("H5:H" & LR).Select
Selection.Copy
Range("I4").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
        
Application.CutCopyMode = False
Selection.ColumnWidth = 3.19
Range("I5").Select


'This was my attempt to apply the desired formula to my chart region

'With Range("I5:I" & LR & LC)
'    .FormulaR1C1 = _
'        "=SQRT((SUMIFS(C2,C1,R2C9,C4,R4C)-SUMIFS(C2,C1,R2C9,C4,RC8))^2 + (SUMIFS(C3,C1,R2C9,C4,R4C)-SUMIFS(C3,C1,R2C9,C4,RC8))^2)"
'End With
    


End Sub