Populate value inside VBA only without using helper excel tables

[earthworm]

I have a list of codes in a column that has duplicates and I need to perform some calculation based on it .

In order to perform that calculation I am recording the macro to extract that column into another sheet and remove duplicates in another column / sheet.

What I am looking for is that , Instead of copy pasting the column in another sheet without disturbing the main the data , is it possible to perform the activity in VBA only in backend only
instead of performing stuff in excel sheet

Example

A	B	C	D
Value 1	AB1	AB2	AB3
Value 1	AB1	AB2	AB3
Value 1	AB1	AB2	AB3
Value 1	AB1	AB2	AB3
Value 2	AB1	AB2	AB3
Value 3	AB1	AB2	AB3
Value 4	AB1	AB2	AB3
Value 5	AB1	AB2	AB3
Value 6	AB1	AB2	AB3
Value 7	AB1	AB2	AB3
Value 8	AB1	AB2	AB3
Value 9	AB1	AB2	AB3
Value 10	AB1	AB2	AB3

Instead of using above data present in sheet , How can I perform calculations in vba without jumping into excel sheets and using it as source.

Example extract unique record only based on column "A" without showing the working in excel sheet. Its like I dont want to perform working in excel sheets to perform another vba functions.

 

[Legacy 143009]

I think this post can be a good start for you:

Add worksheets to array by VBA

I practice on VBA for one month and I have some problem.I want to create array from data in worksheet by this loop but it doesn't work. I have 6 worksheets but it can get only one worksheet into ar...

stackoverflow.com

Then, you can make every calculations addressing those array indexes without writing anythig to any sheet.
For example the code below will sum sheet1 D3 and Sheet2 C2 values regarding the example above:

dataArray(1)(3, 4)+dataArray(2)(2, 3)

 

[earthworm]

I think this post can be a good start for you:

Add worksheets to array by VBA

I practice on VBA for one month and I have some problem.I want to create array from data in worksheet by this loop but it doesn't work. I have 6 worksheets but it can get only one worksheet into ar...

stackoverflow.com

Then, you can make every calculations addressing those array indexes without writing anythig to any sheet.
For example the code below will sum sheet1 D3 and Sheet2 C2 values regarding the example above:

dataArray(1)(3, 4)+dataArray(2)(2, 3)

Thank you very much. Can you please explain the above logic as it seems like index array . Secondly have you removed duplicates in above ?

 

[Legacy 143009]

Yes, you are keeping sheets in a one dimensional array and accessing indexes with R1C1 logic.
I don't understand what do you mean by duplicate values or what do you achieve by those values but study my example carefully below. The example sums the values in each row with only unique records:

Sub myFunction()
    
  Dim dataArray(1) As Variant 'Array length is 1 because there will be only one sheet
  Dim results As String
  dataArray(1) = Worksheets("Sheet1").UsedRange.Value 'Get all data on sheet to the array

  'Eleminate the duplicate Column A values by comparing each value with one another
  For j = 1 To UBound(dataArray(1)) - 1
    If dataArray(1)(j, 1) <> "" Then 'If Column A is not empty
      For i = j + 1 To UBound(dataArray(1))
        If dataArray(1)(j, 1) = dataArray(1)(i, 1) Then '1 stands for column number. j is compared value.
          dataArray(1)(i, 1) = "" 'Empty value if there is duplicate.
        End If
      Next
    End If
  Next
   results = "Sums for each row" & vbCrLf & vbCrLf
  'Make a simple calculation, sum 3 columns for each row which are not empty
  For j = 1 To UBound(dataArray(1))
    If dataArray(1)(j, 1) <> "" Then 'Skip that row if Column A is empty.
      results = results & dataArray(1)(j, 1) & ": "
      results = results & (dataArray(1)(j, 2) + dataArray(1)(j, 3) + dataArray(1)(j, 4)) & vbCrLf
    End If
  Next
  'Display results
  MsgBox results
  r = 1
  'Put the results in a new file without touching the old data
  Sheets.Add After:=Sheets(Sheets.Count)
  With ActiveSheet
    For j = 1 To UBound(dataArray(1)) 'For each record
      If dataArray(1)(j, 1) <> "" Then 'If Column A is not empty.
        For i = 1 To UBound(dataArray(1), 2) 'For each column
          .Cells(r, i).Value = dataArray(1)(j, i) 'write data to cells
        Next
        'Write the sum to Column E
        .Cells(r, 5).Value = dataArray(1)(j, 2) + dataArray(1)(j, 3) + dataArray(1)(j, 4)
        r = r + 1
      End If
    Next
  End With
End Sub