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.

Are the ranges you're calculating always 14 cells?

SJR
– SJR

2019-03-20 18:35:08 +00:00
Commented Mar 20, 2019 at 18:35 — SJR
– SJR, Commented Mar 20, 2019 at 18:35
@SJR yes..I will always be calculating variance of 14 cells

Aprnaa
– Aprnaa

2019-03-21 03:40:02 +00:00
Commented Mar 21, 2019 at 3:40 — Aprnaa
– Aprnaa, Commented Mar 21, 2019 at 3:40

Cyril · Accepted Answer · 2019-03-21 17:16:39Z

0

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

edited Mar 21, 2019 at 17:16

answered Mar 20, 2019 at 18:53

Cyril

6,8291 gold badge21 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Aprnaa Over a year ago

In the code, lc, lr and ns pertains to sheet1 and sheet2 has different number of rows than sheet1. So the for loop is ideal for computing variance but not for pasting. So I would have to replace 'i' in sheets(2).cells(i,j).value with another variable. How do I do that?

Cyril Over a year ago

@Aprnaa for sheet 2, do you only have as many rows as you have sets of variances? that's how i was treating sheet 2, which is why you have ns (number of sets) being the maximum, starting in row 3 (just realized i need "ns+3" as the maximum for i. the use of lr (last row on sheet 1) would be used to find ns, which would dictate the use of rows on sheet 1 up and until the last row.

Aprnaa Over a year ago

Sorry for the late reply. Haven't tried the edited code but it makes sense. Will let you know the result soon.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related