VBA Macro to add a relative reference column

0 votes

I'm trying to make a macro right now that adds a relative reference number based on two values.

Both a client (column B) and batch are present in the dataset (column C). Each client is allowed to have several batches, each of which starts at zero and successively rises the more batches the client has. This indicates that a customer may have batches 0, 1, 2, 3, etc., as well as batches 2, 3, 4, etc.

To generate a relative reference for the client-batches, I'm trying to develop a macro (see column E in the screenshot).

Desired Output

This would have to be done per client.

So far I have only managed to create a macro which selects a specific client ID, I have not figured out how to cycle through them or add the values into column E:

Sub select_relative_column()

Dim ref As Range
Dim ref2 As Range

For i = 1 To 100
        If Cells(i, 2) = 10000201 Then
            Set ref = Range(Cells(i, 1), Cells(i, 5))
            If ref2 Is Nothing Then
                Set ref2 = ref
            Else
                Set ref2 = Union(ref2, ref)
            End If
        End If
    Next i
    ref2.Select
End Sub
Mar 25, 2023 in Others by Kithuzzz
• 38,000 points
477 views

1 answer to this question.

0 votes

You may accomplish this using an Excel formula instead of a macro.

Below is the calculation for row 5 if client is in column C and batch is in column D with data from rows 5 to 24. (can be copied down)

  =$D5-MIN(UNIQUE(FILTER($D$5:$D$24,$C$5:$C$24=$C5,)))+1
answered Mar 25, 2023 by narikkadan
• 63,600 points

Related Questions In Others

0 votes
1 answer
0 votes
1 answer

How to find a value in an excel column by vba code Cells.Find

Just use: Dim Cell As Range Columns("B:B").Select Set cell = ...READ MORE

answered Nov 17, 2022 in Others by narikkadan
• 63,600 points
3,803 views
0 votes
1 answer

How do I use the Indirect Function in Excel VBA to incorporate the equations in a VBA Macro Function

Try this: Sub Test() Dim str As String: str ...READ MORE

answered Jan 19, 2023 in Others by narikkadan
• 63,600 points
1,060 views
0 votes
1 answer
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,690 points
997 views
0 votes
1 answer

Remove formulas from all worksheets in Excel using VBA

Try this : Option Explicit Sub test1() ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,600 points
2,080 views
0 votes
1 answer

Calculate monthly average from daily data without PivotTable

Assuming you have the months in column D enter ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,600 points
1,788 views
0 votes
1 answer

Automate compound annual growth rate (CAGR) calculation

The following PowerPivot DAX formulas worked for ...READ MORE

answered Oct 7, 2022 in Others by narikkadan
• 63,600 points
1,300 views
0 votes
1 answer

VBA to unhide a column when opening a workbook

Use the workbook_open even within ThisWorkbook. READ MORE

answered Oct 20, 2022 in Others by narikkadan
• 63,600 points
509 views
+1 vote
1 answer

Excel macro to make selected range absolute or relative reference?

Try  this: Sub ConverReferenceType() On ...READ MORE

answered Oct 23, 2022 in Others by narikkadan
• 63,600 points
1,573 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP