Change to code offsets

[SUSAN BAXTER]

Hi


I have code that I would like to make a small change to but need some help with. I only need the first cell in all the ranges in all the “arr1” in my code below to be increased by one row , so the 1^st change is the current range is Range("D22:I36") change to Range("E22:I36"). I can do this on my own but I’m having trouble with the changes needed in the offsets (believe that’s the correct terminology) in the code. There is a lot going on in that code and I’m no expert like you are.
No changes are needed in arr2.
Thanks so much for any help.

Sub PlaceNumbers()

Dim c As Range, rng1 As Range, rng2 As Range, rng3 As Range, rng4 As Range
Dim last1 As Long, last2 As Long, rtar As Long, xtar As Long
 
Application.ScreenUpdating = False

With ActiveSheet
    'create arrays
    arr1 = Array(.Range("D22:I36"), .Range("J22:O36"), .Range("P22:U36"), .Range("V22:AA36"), .Range("AB22:AG36") _
        , .Range("AH22:AM36"), .Range("AN22:AS36"), .Range("AT22:AY36"), .Range("AZ22:BE36"), .Range("BF22:BK36"))
    arr2 = Array(.Range("BN22:BQ36"), .Range("BR22:BU36"), .Range("BV22:BY36"), .Range("BZ22:CC36"), .Range("CD22:CG36") _
        , .Range("CH22:CK36"), .Range("CL22:CO36"), .Range("CP22:CS36"), .Range("CT22:CW36"), .Range("CY22:DA36"))                                                                '
    'loop through arrays
    For i = LBound(arr1) To UBound(arr1)
        Set rng1 = arr1(i)
        Set rng3 = arr2(i)                                                          '
        last1 = .Cells(.Rows.Count, ColLetter(rng1.Columns(1).Column)).End(xlUp).Row
        last2 = .Cells(.Rows.Count, ColLetter(rng3.Columns(1).Column)).End(xlUp).Row
 
        For Each c In rng1.Offset(1, 2).Resize(, 1)
            If c <> "" Then
                rtar = Evaluate("=MATCH(" & ColLetter(rng1.Columns(2).Column) & rng1.Row & "&" & ColLetter(rng1.Columns(3).Column) & rng1.Row & "," & ColLetter(rng3.Columns(1).Column) & "1:" & ColLetter(rng3.Columns(1).Column) & last2 & "&" & ColLetter(rng3.Columns(3).Column) & "1:" & ColLetter(rng3.Columns(3).Column) & last2 & ",0)")
                xtar = Application.Match(c.Offset(0, -2), Range(ColLetter(rng3.Columns(1).Column) & rtar & ":" & ColLetter(rng3.Columns(1).Column) & last2), 0)
                With Application.WorksheetFunction
                    c.Offset(0, 1) = .Index(Range(ColLetter(rng3.Columns(2).Column) & rtar & ":" & ColLetter(rng3.Columns(2).Column) & last2), xtar)
                    c.Offset(0, 2) = .Index(Range(ColLetter(rng3.Columns(3).Column) & rtar & ":" & ColLetter(rng3.Columns(3).Column) & last2), xtar)
                    c.Offset(0, 3) = .Index(Range(ColLetter(rng3.Columns(4).Column) & rtar & ":" & ColLetter(rng3.Columns(4).Column) & last2), xtar)
                End With
            End If
        Next c
    Next
End With

Application.ScreenUpdating = True
 
End Sub
 
Function ColLetter(Collet As Integer) As String

ColLetter = Split(Cells(1, Collet).Address, "$")(1)

End Function 

 
  [SIZE=3]<style type="text/css">pre { direction: ltr; color: rgb(0, 0, 0); text-align: left; }pre.western { font-family: "Liberation Mono", serif; }pre.cjk { font-family: "WenQuanYi Micro Hei Mono"; }pre.ctl { font-family: "Liberation Mono"; }p { margin-bottom: 0.25cm; direction: ltr; color: rgb(0, 0, 0); line-height: 115%; text-align: left; }p.western { font-family: "Liberation Serif", serif; font-size: 12pt; }p.cjk { font-family: "WenQuanYi Micro Hei"; font-size: 12pt; }p.ctl { font-family: "Lohit Devanagari"; font-size: 12pt; }a:link { }</style>

[/SIZE]

  <style type="text/css">p { margin-bottom: 0.25cm; direction: ltr; color: rgb(0, 0, 0); line-height: 115%; text-align: left; }p.western { font-family: "Liberation Serif", serif; font-size: 12pt; }p.cjk { font-family: "WenQuanYi Micro Hei"; font-size: 12pt; }p.ctl { font-family: "Lohit Devanagari"; font-size: 12pt; }a:link { }</style>

 

[percom74]

Hello Susan:

So let me see if I understand what you want to do...Your first Array (arr1), you want all the first rows to increase by one row so range D22:I36 goes to E22:I36 and J22:O36 goes to K22:O36 and so on? So you will be losing a row in each array range in arr1. Is that correct? If that is correct, I think this should work...

Sub PlaceNumbers()

Dim c As Range, rng1 As Range, rng2 As Range, rng3 As Range, rng4 As Range
Dim last1 As Long, last2 As Long, rtar As Long, xtar As Long
 
Application.ScreenUpdating = False


With ActiveSheet
    'create arrays
    arr1 = Array(.Range("E22:I36"), .Range("K22:O36"), .Range("Q22:U36"), .Range("W22:AA36"), .Range("AC22:AG36") _
        , .Range("AI22:AM36"), .Range("AO22:AS36"), .Range("AU22:AY36"), .Range("BA22:BE36"), .Range("BG22:BK36"))
    arr2 = Array(.Range("BN22:BQ36"), .Range("BR22:BU36"), .Range("BV22:BY36"), .Range("BZ22:CC36"), .Range("CD22:CG36") _
        , .Range("CH22:CK36"), .Range("CL22:CO36"), .Range("CP22:CS36"), .Range("CT22:CW36"), .Range("CY22:DA36"))                                                                '
    'loop through arrays
    For i = LBound(arr1) To UBound(arr1)
        Set rng1 = arr1(i)
        Set rng3 = arr2(i)                                                          '
        last1 = .Cells(.Rows.Count, ColLetter(rng1.Columns(1).Column)).End(xlUp).Row
        last2 = .Cells(.Rows.Count, ColLetter(rng3.Columns(1).Column)).End(xlUp).Row
 
        For Each c In rng1.Offset(1, 2).Resize(, 1)
            If c <> "" Then
                rtar = Evaluate("=MATCH(" & ColLetter(rng1.Columns(2).Column) & rng1.Row & "&" & ColLetter(rng1.Columns(3).Column) & rng1.Row & "," & ColLetter(rng3.Columns(1).Column) & "1:" & ColLetter(rng3.Columns(1).Column) & last2 & "&" & ColLetter(rng3.Columns(3).Column) & "1:" & ColLetter(rng3.Columns(3).Column) & last2 & ",0)")
                xtar = Application.Match(c.Offset(0, -2), Range(ColLetter(rng3.Columns(1).Column) & rtar & ":" & ColLetter(rng3.Columns(1).Column) & last2), 0)
                With Application.WorksheetFunction
                    c.Offset(0, 1) = .Index(Range(ColLetter(rng3.Columns(2).Column) & rtar & ":" & ColLetter(rng3.Columns(2).Column) & last2), xtar)
                    c.Offset(0, 2) = .Index(Range(ColLetter(rng3.Columns(3).Column) & rtar & ":" & ColLetter(rng3.Columns(3).Column) & last2), xtar)
                    c.Offset(0, 3) = .Index(Range(ColLetter(rng3.Columns(4).Column) & rtar & ":" & ColLetter(rng3.Columns(4).Column) & last2), xtar)
                End With
            End If
        Next c
    Next
End With


Application.ScreenUpdating = True
 
End Sub
 
Function ColLetter(Collet As Integer) As String


ColLetter = Split(Cells(1, Collet).Address, "$")(1)


End Function

 

[JLGWhiz]

disregard. not relevant.

 

[percom74]

Actually I should have said columns instead of row...I believe that is what you meant...

 

[SUSAN BAXTER]

Hi

I meant column as you figured out already. Thanks so much I will try it and get back to you.

 

[SUSAN BAXTER]

Hi

My apologies, I should have just listed the changes up front.

arr2 – does not need to be changed.

The range changes to arr1 are as follows:
arr1 = Array(.Range("E22:I36"), .Range("J22:N36"), .Range("O22:S36"), .Range("T22:X36"), .Range("Y22:AC36") _
, .Range("AD22:AH36"), .Range("AI22:AM36"), .Range("AN22:AR36"), .Range("AS22:AW36"), .Range("AX22:BB36"))


<style type="text/css">pre { direction: ltr; color: rgb(0, 0, 0); text-align: left; }pre.western { font-family: "Liberation Mono", serif; }pre.cjk { font-family: "WenQuanYi Micro Hei Mono"; }pre.ctl { font-family: "Liberation Mono"; }p { margin-bottom: 0.25cm; direction: ltr; color: rgb(0, 0, 0); line-height: 115%; text-align: left; }p.western { font-family: "Liberation Serif", serif; font-size: 12pt; }p.cjk { font-family: "WenQuanYi Micro Hei"; font-size: 12pt; }p.ctl { font-family: "Lohit Devanagari"; font-size: 12pt; }a:link { }</style>

 

[JLGWhiz]

The only Offset that I think would be affected is this one.

For Each c In rng1.Offset(1, 2).Resize(, 1)

Since you modified all of your array ranges to start one column to the right, then if you change the 2 to a 1 in the For statement it should then reference the same range that it did before.

For Each c In rng1.Offset(1, 1).Resize(, 1)

If not, post back with the results and what they should have been, then we can make any necessary adjustments. It would be much better if we could see the workbook in a link to a share server.

 

[SUSAN BAXTER]

JLGWhiz and percom74

Thank-you very much for both efforts. It works and that is is just great!!!

 

[JLGWhiz]

JLGWhiz and percom74

Thank-you very much for both efforts. It works and that is is just great!!!

Happy to assist,
Regards, JLG