Collecting data with a dictionary vba

[drop05]

Hello, I was told to collect before worrying about printing and doing so using a dictionary and a unique key
The unique key is something in the outer dictionary is what i was told too

But i am not sure what that means, or the creation of a dictionary.
i am new to VBA and trying to create something that does this to assist with copying and pasting values from one sheet to another

any tips or explanations to kinda get an idea of starting

 

[Eric W]

What you're asking for is not hard, and doesn't require a dictionary. I rather suspect though that this is only a step in a larger process, and when all's said and done, this step wouldn't really be needed.

In any case, the first part of the process is to gather the requirements, which I think I've done. There are as many ways to accomplish this task as there are programmers. The way I chose is the simple but slower way. Try it out on a COPY of your data.

Sub SaveData()
Dim r As Long, c As Long, oc As Long
    
' r is the row we're looking at, c is the column
' oc is the output-column
' This assumes there is an empty sheet named Sheet2 to put the data

    oc = 1
    For c = 7 To 10
        r = 7
        While Cells(r, c) <> ""
            Sheets("Sheet2").Cells(1, oc) = Chr(64 + c) & " - " & Cells(r - 1, c)
            Cells(r, c).Resize(350).Copy Sheets("Sheet2").Cells(2, oc)
            oc = oc + 1
            r = r + 351
        Wend
    Next c
    
End Sub

 

[drop05]

What you're asking for is not hard, and doesn't require a dictionary. I rather suspect though that this is only a step in a larger process, and when all's said and done, this step wouldn't really be needed.

In any case, the first part of the process is to gather the requirements, which I think I've done. There are as many ways to accomplish this task as there are programmers. The way I chose is the simple but slower way. Try it out on a COPY of your data.

Sub SaveData()
Dim r As Long, c As Long, oc As Long
  
' r is the row we're looking at, c is the column
' oc is the output-column
' This assumes there is an empty sheet named Sheet2 to put the data

    oc = 1
    For c = 7 To 10
        r = 7
        While Cells(r, c) <> ""
            Sheets("Sheet2").Cells(1, oc) = Chr(64 + c) & " - " & Cells(r - 1, c)
            Cells(r, c).Resize(350).Copy Sheets("Sheet2").Cells(2, oc)
            oc = oc + 1
            r = r + 351
        Wend
    Next c
  
End Sub

@Eric W , If i was to change the number 10 to another number, that is the number of columns from my sheet 1 where it is copying from? So if leaving it as 10 it will only go GHIJ? But if i do 11 it will include K?

 

[Eric W]

Correct! Although if you go past Z, we'll need to change the headers.

 

[drop05]

Correct! Although if you go past Z, we'll need to change the

How would it be for doing that?

 

[Eric W]

Replace

Sheets("Sheet2").Cells(1, oc) = Chr(64 + c) & " - " & Cells(r - 1, c)

with

Sheets("Sheet2").Cells(1, oc) = Replace(Cells(1, c).Address(0, 0), 1, "") & " - " & Cells(r - 1, c)

 

[drop05]

Replace

Sheets("Sheet2").Cells(1, oc) = Chr(64 + c) & " - " & Cells(r - 1, c)

with

Sheets("Sheet2").Cells(1, oc) = Replace(Cells(1, c).Address(0, 0), 1, "") & " - " & Cells(r - 1, c)

The cells i have the values in are highlighted color, when doing the code it brings that color cell background, is there a way to just get the value?

 

[Eric W]

Change

Cells(r, c).Resize(350).Copy Sheets("Sheet2").Cells(2, oc)

to

Sheets("Sheet2").Cells(2, oc).Resize(350).Value = Cells(r, c).Resize(350).Value