Trying to return data using array

So, I have this huge data set which has caused me to have to use arrays. I'm still a new and learning user, but I really do think this is the best solution for my problem. I have sheet one as my rollup sheet and sheets 2-5 have data on them representing quarterly numbers. My problem is figuring out how to offset the data one cell to the right of where the original data goes and I thought I had it figured out, but I think I need to define a dimension for one or all of the variables that represent where I want the data to go. I've tried just about everything, but I think I'm missing something simple, yet fundamental. When I put in a message box to return values for the cell variables I wish to drop data in, it returns the data that's already in there, and the offset works, but for some reason, that's all I can get it to do. It will not for the life (or death, if I cannot figure this out soon) of it put the sum'd values in those cells, just keeps looking at them and the data that's already in them. PLEASE HELP before I go insane! I'm going to put the last iteration of my code in here, but I have a simpler build which might be easier to look through to identify the problem, which I'll drop in below that. BUT I'M killing my sanity right now just trying to think about what I'm doing wrong. Below, I've included the code(s).

Dim xArr As Variant

Dim ws As Worksheet

Dim rtrnOff As Variant

Dim lRow As Long

 

Application.ScreenUpdating = False

 

Set xArr = Sheets(Array(Sheet2.Name, Sheet3.Name, Sheet4.Name, Sheet5.Name))

 

'Initialize For Each array loop

 

For Each ws In xArr

 

    'setup offset parameter for array loop

    

    rtrnOff = (ws.Index) - 2

    

    'define variable and parameters for the receiving cells

    

    lRow = ws.Range("Q" & Rows.Count).End(xlUp).Row

    balCel = Sheet1.Range("Q3").Offset(0, rtrnOff)

    expCel = Sheet1.Range("Q5").Offset(0, rtrnOff)

    dlqCel = Sheet1.Range("Q14").Offset(0, rtrnOff)

    critCel = Sheet1.Range("Q23").Offset(0, rtrnOff)

    clasCel = Sheet1.Range("Q23").Offset(0, rtrnOff)

    

    'test message box

    

    'MsgBox (balCel)

        

        'Setup condition to look specifically for bancorp wide numbers

        

        If Sheet1.Range("B46").Value = "Bancorp" Then

        

        'Calculate balance, exposure and sum of 30-89, and 90 delq

        

        rtrnBal = (Application.Sum(ws.Range("Y2:Y" & lRow))) / 1000000

        rtrnExp = (Application.Sum(ws.Range("X2:X" & lRow))) / 1000000

        nff3089 = (Application.Sum(ws.Range("AR2:AR" & lRow))) / 1000000

        nff90 = (Application.Sum(ws.Range("AS2:AS" & lRow))) / 1000000

        

        'Calculate balance for criticized and classified

        

        nffCrit = (Application.SumIf(ws.Range("T2:T" & lRow), "SM", Sheet2.Range("Y2:Y" & lRow))) / 1000000

        nfsCrit = (Application.SumIf(ws.Range("T2:T" & lRow), "SS", Sheet2.Range("Y2:Y" & lRow))) / 1000000

        nftCrit = (Application.SumIf(ws.Range("T2:T" & lRow), "DF", Sheet2.Range("Y2:Y" & lRow))) / 1000000

                       

        balCel = rtrnBal

        expCel = rtrnExp

        dlqCel = nff3089 + nff90

        critCel = nffCrit + nfsCrit + nftCrit

        clasCel = nfsCrit + nftCrit

        

        'End conditional to target for selected MEDC

        

        Else

        

        'Calculate balance, exposure and sum of 30-89, and 90 delq for filtered by MEDC

        

        rtrnBal = (Application.SumIf(ws.Range("B2:B" & lRow), Sheet1.Range("B46"), Sheet2.Range("Y2:Y" & lRow))) / 1000000

        rtrnExp = (Application.SumIf(ws.Range("B2:B" & lRow), Sheet1.Range("B46"), Sheet2.Range("X2:X" & lRow))) / 1000000

       nff3089 = Application.SumIf(ws.Range("B2:B" & lRow), Sheet1.Range("B46"), Sheet2.Range("AR2:AR" & lRow))

        nff90 = Application.SumIf(ws.Range("B2:B" & lRow), Sheet1.Range("B46"), Sheet2.Range("AS2:AS" & lRow))

        

        fCrit1 = Application.SumIfs(Sheet2.Range("Y2:Y" & lRow), Sheet2.Range("B2:B" & lRow), Sheet1.Range("B46"), _

        Sheet2.Range("T2:T" & lRow), "SM")

        fCrit2 = Application.SumIfs(Sheet2.Range("Y2:Y" & lRow), Sheet2.Range("B2:B" & lRow), Sheet1.Range("B46"), _

        Sheet2.Range("T2:T" & lRow), "SS")

        fCrit3 = Application.SumIfs(Sheet2.Range("Y2:Y" & lRow), Sheet2.Range("B2:B" & lRow), Sheet1.Range("B46"), _

        Sheet2.Range("T2:T" & lRow), "DF")

 

        balCel = rtrnBal

        expCel = rtrnExp

        dlqCel = nff3089 + nff90

        critCel = fCrit1 + fCrit2  + fCrit3

        clasCel = fCrit2  + fCrit3

        End If
        

        'Return results to desired cells on Sheet1


    Next ws


Application.ScreenUpdating = True


End Sub

The simpler code set is as follows:

Sub Test()

 

Dim xArr As Variant

Dim ws As Worksheet

Dim rtrnOff As Variant

 
Set xArr = Sheets(Array(Sheet2.Name, Sheet3.Name, Sheet4.Name, Sheet5.Name))

 

'Initialize For Each array loop

 

For Each ws In xArr

 

    'setup offset parameter for array loop

    

    rtrnOff = (ws.Index) - 2

    

    'define variable and parameters for the receiving cells

    

    balCel = Sheet1.Range("Q3").Offset(0, rtrnOff)

    expCel = Sheet1.Range("Q5").Offset(0, rtrnOff)

    dlqCel = Sheet1.Range("Q14").Offset(0, rtrnOff)

    critCel = Sheet1.Range("Q23").Offset(0, rtrnOff)

    clasCel = Sheet1.Range("Q23").Offset(0, rtrnOff)

    
    'test message box

        MsgBox (balCel)

       
        'Setup condition to look specifically for bancorp wide numbers

    Next ws

   

End Sub

At the top of your code, you have:

balCel = Sheet1.Range("Q3").Offset(0, rtrnOff), but you did not declare the variable balCel, so balCel is holding the value of the data in the cell at the offset from Q3.

It seems that you are trying to use balCel (and the rest) as names (pointers) for cells. To do that you need to modify your code such as:

Dim balCel as Range
Set balCel = Sheet1.Range("Q3").Offset(0, rtrnOff)

Then to write data to the cell:

balCel.Value = rtrnBal

Hello Rokn,

Help us to Help you...

You should post a copy of your workbook for review. Please change or redact any information that is personal or confidential before you post.

Please tell us in detail what results you expect and include before and after examples of your data.

If you have any error messages, please include them in your post and mark the line in your code where the error occurred.