CODEWITHSUNDEEP
vbaexcel
Xcrape
Xcrape

Reputation: 1

VBA Dual Loops Contiguous and Non Contiguous

Rewriting this for clarity, I think the original was too confusing and too long.

I'm trying to grab contiguous time series data off a sheet named "Files", process it through a series of calculations on a sheet named "Data", copy those results and paste them as static data in a non-contiguous range on "Data" and then repeating that process until all data has been handled.

The issue I've been struggling with for the last five days is the placement of the second function. If I nest it inside of "i" it writes each single result 25 times to the non-contiguous range. If I place it outside of "i" it finishes "i" then writes only the last result to each of the 25 locations.

I'm pretty sure at this point I'm using the wrong structure, I'm guessing a "For" loop isn't the way to go for the second function, but I'm so new to this I can't really get my head wrapped around how to implement it otherwise. I've also tried to structure "n" as an array but was never able to get that debugged and wasn't sure it was the right approach either.

Sub getData()

' Process individual time series

Dim Data As Worksheet, Files As Worksheet
Dim fLastRow As Long, dLastRow As Long
Dim i As Long, n As Long

Application.ScreenUpdating = False

Set Data = ActiveWorkbook.Sheets("Data")
Set Files = ActiveWorkbook.Sheets("Files")

fLastRow = Files.Range("A" & Files.Rows.Count).End(xlUp).Row
dLastRow = Data.Range("F" & Data.Rows.Count).End(xlUp).Row

' Process three column data

    Files.Range("A1:C" & fLastRow).Copy
    Data.Range("A3").PasteSpecial xlPasteValuesAndNumberFormats
    Data.Range("F202:P" & dLastRow).Copy
    Data.Range("T202").PasteSpecial xlPasteValuesAndNumberFormats

' Process single column data

    For i = 4 To 26
            Files.Activate
            Range(Cells(1, i), Cells(3509, i)).Copy
            Data.Range("C3").PasteSpecial xlPasteValuesAndNumberFormats
            Data.Range("F202:P" & dLastRow).Copy
        For n = 32 To 296 Step 12  ' <~~ this is the problem. inside or outside "i" doesn't work.
            Data.Activate
            Range(Cells(202, n), Cells(3511, n)).PasteSpecial xlPasteValuesAndNumberFormats
        Next n ' <~~ i know this is the problem just not sure what the answer is.
    Next i

' Post processing

    Data.Cells.Columns.AutoFit
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    Data.Activate
    Data.Range("A1").Select

End Sub

Upvotes: 0

Views: 305

Answers (2)

Steven Reid
Steven Reid

Reputation: 11

You could use one variable; n. Continue stepping by 12, but where you want to place the i variable, use n and divide by 12 and add 4

Upvotes: 1

Xcrape
Xcrape

Reputation: 1

Until I get better at the code I'll have to settle for this... I stepped the data on the "files" sheet to coincide with the step on the data sheet. To keep the data readable on the "files" tab I just hid the added columns. This allowed me to use "i" for both copy paste operations. I'm sure there is a better way but this meets my needs and is much faster than the original code I started with.

Sub getData()

' Process individual time series

Dim Data As Worksheet, Files As Worksheet
Dim fLastRow As Long, dLastRow As Long, i As Long

Set Data = ActiveWorkbook.Sheets("Data")
Set Files = ActiveWorkbook.Sheets("Files")
fLastRow = Files.Range("A" & Files.Rows.Count).End(xlUp).Row
dLastRow = Data.Range("F" & Data.Rows.Count).End(xlUp).Row

Application.ScreenUpdating = False

' Process data

    Files.Range("A1:C" & fLastRow).Copy
    Data.Range("A3").PasteSpecial xlPasteValuesAndNumberFormats
    Data.Range("F202:P" & dLastRow).Copy
    Data.Range("T202").PasteSpecial xlPasteValuesAndNumberFormats
    Files.Range("D1:JH" & fLastRow).Copy
    Data.Range("AF3520").PasteSpecial xlPasteValuesAndNumberFormats

    For i = 32 To 296 Step 12
        Data.Range(Cells(3520, i), Cells(7103, i)).Copy
        Data.Range("C3").PasteSpecial xlPasteValuesAndNumberFormats
        Data.Range("F202:P" & dLastRow).Copy
        Data.Range(Cells(202, i), Cells(3511, i)).PasteSpecial xlPasteValuesAndNumberFormats
    Next i

' Post processing

    Data.Range(Cells(3520, 32), Cells(7103, 296)).ClearContents
    Data.Cells.Columns.AutoFit
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    Range("A1").Select

End Sub

Upvotes: 0

Related Questions