Increment rows and columns to find variance and paste values in Excel VBA

Question

I have two worksheets. Sheet1 contains data in B3:W296 and Sheet2 contains data in alternate columns B3:B23, D3:D23,..,T3:T23. Now I am supposed to fill up the empty alternate columns in Sheet2(C3:C23, E3:E23,..,U3:U23)

Sheet2 values are supposed to be filled like,

Sheet2.C3.value = VARP("Sheet1".Range(C3:C16)) ...

Sheet2.U3.value = VARP("Sheet1".Range(U3:U16))

For the row4 in Sheet2, the formula should be changed like,

Sheet2.C4.value = VARP("Sheet1".Range(C17:C30)) ...

Sheet2.U4.value = VARP("Sheet1".Range(U17:U30))

I have to do this in different files, so the number of rows and columns in both the sheets might vary. So I can't give the row and column numbers explicitly.

I have started writing the following code but I am stuck,

Dim lRow, lRow2 As Long
Dim lCol, lCol2 As Long
Dim i, j As Integer

lRow = ThisWorkbook.Worksheets("Sheet2").Cells.Find(What:="*", After:=Range("A1"), LookAt:=xlPart, _
LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
lCol = ThisWorkbook.Worksheets("Sheet2").Cells.Find(What:="*", After:=Range("A1"), LookAt:=xlPart, _
LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False).Column
lRow2 = ThisWorkbook.Worksheets("Sheet1").Cells.Find(What:="*", After:=Range("A1"), LookAt:=xlPart, _
LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
lCol2 = ThisWorkbook.Worksheets("Sheet1").Cells.Find(What:="*", After:=Range("A1"), LookAt:=xlPart, _
LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False).Column

For i = 3 To lRow
    For j = 3 To lCol - 2 Step 2
            ThisWorkbook.Worksheets("Sheet2").Range(j & i).Value = VarP(ThisWorkbook.Worksheets("Sheet1").Range())
    Next j
Next i

I am not sure how to proceed with the for loop for this case.

Any help would be appreciated greatly. Thanks in advance.

Cyril Cyril · Accepted Answer · 2019-03-20T18:53:31

I believe you want to figure out a separate number than your current i for rows... you will need to find the number of varp sets to iterate through, similar to (untested):

dim lr as long, lc as long, i as long, j as long, k as long, ns as long
with sheets(1)
    lc = .cells(1,.columns.count).end(xltoleft).column
    lr = .cells(.rows.count,1).end(xlup).row
    ns = application.rounddown(lr/14.01) 'uses 14.01 to divide so you start the 15th row on a separate set
    for j = 3 to lc -2 step -2
        for i = 3 to ns+3 'just added the plus 3 as edit1
            k = i*14+3  'starts on row 3
            sheets(2).cells(i,j).value = varp(.range(.cells(k,j),.cells(k+13,j))
        next i
    next j
end with

Increment rows and columns to find variance and paste values in Excel VBA

1 Answers