Collecting data with a dictionary vba

drop05

Active Member
Joined
Mar 23, 2021
Messages
285
Office Version
  1. 365
Platform
  1. Windows
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
 
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.

VBA Code:
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
 
Upvote 0

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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.

VBA Code:
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?
 
Upvote 0
Replace

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

with

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

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

with

VBA Code:
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?
 
Upvote 0
Change

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

to

VBA Code:
Sheets("Sheet2").Cells(2, oc).Resize(350).Value = Cells(r, c).Resize(350).Value
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top