CODEWITHSUNDEEP
excelvbafor-loop
adamjamesb
adamjamesb

Reputation: 35

Cell reference changes for excel macro

I worked with some collaboraters on the site a couple of months ago to perfect the following code, however trying to re-trace my steps in respect to cell references is proving difficult. To clarify - all that's changed is the following cell references below.

Essentially:

  1. Column C has now changed to column J
  2. Column R has now changed to column O
  3. Column S has now changed to column P
  4. Column T has now changed to column Q

But for the life of me I can't work out where these cell references take effect in the code.

Sub Display()

Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = ThisWorkbook.Sheets("DISPLAY")
Set ws2 = ThisWorkbook.Sheets("REPORT_DOWNLOAD")

Dim arr_1 As Variant, arr_2 As Variant, arr_result As Variant
arr_1 = ws1.Range("C2:C" & ws2.Range("L" & ws2.Rows.Count).End(xlUp).Row).Value2
arr_2 = ws2.Range("E2:L" & ws2.Range("E" & ws2.Rows.Count).End(xlUp).Row).Value2

ReDim arr_result(LBound(arr_2) To UBound(arr_2), 1 To 3)

Dim i As Long, j As Long

For i = LBound(arr_1, 1) To UBound(arr_1, 1)
    For j = LBound(arr_2, 1) To UBound(arr_2, 1)

        If arr_1(i, 1) = arr_2(j, 1) Then
            'use this if you're handling numbers
            arr_result(i, 1) = arr_result(i, 1) + arr_2(j, 5)
            arr_result(i, 2) = arr_result(i, 2) + arr_2(j, 4)
            arr_result(i, 3) = arr_result(i, 3) + arr_2(j, 8)
        End If

    Next j
Next i

ws1.Cells(2, 18).Resize(UBound(arr_result, 1), 3).Value2 = arr_result

End Sub

Upvotes: 0

Views: 99

Answers (1)

SnowGroomer
SnowGroomer

Reputation: 695

So basically you want to adapt the macro to changed column-wise layout of your worksheet?

There is some missing information in your question:

  • which worksheet changed? (only change corresponding references)

  • did the order of columns change? (your arrays definitely rely on a fixed order of columns)

You have two places where you interact with Excel columns (shown below). This is where you need to make your changes.

  1. When assigning values to your arrays:

Columns C through L:

(presumably this requires your C->J change, so "C2:C" => "J2:J")

    arr_1 = ws1.Range("C2:C" & ws2.Range("L" & ws2.Rows.Count).End(xlUp).Row).Value2

Columns E through L:

    arr_2 = ws2.Range("E2:L" & ws2.Range("E" & ws2.Rows.Count).End(xlUp).Row).Value2
  1. When outputting values to ws1 (Columns R through T - that's columns 18 and 2 additional columns after the resize, if I count correctly):

(Presumably this requires your R->O, S->P, T->Q changes, so 18 => 15)

    ws1.Cells(2, 18).Resize(UBound(arr_result, 1), 3).Value2 = arr_result

Upvotes: 1

Related Questions