CODEWITHSUNDEEP
excelvba
nick lanta
nick lanta

Reputation: 638

How to change column referencing for this for loop

I have dummy data that I used with this script: it just updates values I put in sheet 2 and applies them with an array multiplier for wherever the rIterators match in sheet1. None of that is really necessary for this question, I just need to know how to update my j variable referencing.

Currently it is:

        For j = 3 To 5
            wsA.Cells(fndRow + i, j).Value = rIterator.Offset(, j - 1).Value * multplr(i)
        Next j

and it references columns C-E in both sheets because that's where the values align.

How would I change that if Sheet2 has the values that need to be update in columns C-E, but those columns exist in columns AD-AF in Sheet1?

Rest of code: 

Sub UpdateTSRS()

Dim wbk As Workbook
Dim wsA As Worksheet, wsB As Worksheet
Dim rngA As Range, rngB As Range
Dim rIterator As Range
Dim fndRow As Long
Dim multplr As Variant
multplr = Array(1, 1.1, 1.15, 1.2, 1.3)

Set wbk = ThisWorkbook
Set wsA = wbk.Sheets("Sheet1")
Set wsB = wbk.Sheets("Sheet2")
Set rngA = wsA.Range(wsA.Range("A2"), wsA.Range("A2").End(xlDown))
Set rngB = wsB.Range(wsB.Range("A2"), wsB.Range("A2").End(xlDown))

For Each rIterator In rngB
    On Error Resume Next
    fndRow = Application.Match(rIterator.Value, rngA, 0) + _
        rngA.Range("A1").Row - 1
    If Err.Number <> 0 Then
    Else
    For i = 0 To 4
        For j = 3 To 5
            wsA.Cells(fndRow + i, j).Value = rIterator.Offset(, j - 1).Value * multplr(i)
        Next j
    Next i
        wsA.Range(wsA.Cells(fndRow, 3), wsA.Cells(fndRow, 5)).Resize(5).Interior.Color = RGB(255, 255, 0)
    End If
    Err.Clear
Next rIterator

End Sub

Upvotes: 0

Views: 62

Answers (1)

Cyril
Cyril

Reputation: 6829

Display of code changes from my comment:

'Set wsA = wbk.Sheets("Sheet1")
'Set wsB = wbk.Sheets("Sheet2")
Set wsB = wbk.Sheets("Sheet1")
Set wsA = wbk.Sheets("Sheet2")

and your loop would be similar to: 

For j = 3 To 5
    wsA.Cells(fndRow + i, j).Value = rIterator.Offset(, j +27 - 1).Value * multplr(i)  'i think i got that?  destination still C->E but source is AD->AF
Next j

Upvotes: 1

Related Questions