Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
I need help writing a macro to replace cell contents. (new to VB)
Hello,
I am building an Oracle database and I am going to be using Excel to injest the data. I am trying to give unique values to records and to do so I have been hand copying rows between different worksheets and pasting the same GUID (unique identifier) on matching column values between different worksheets. I am desparately trying to find a time saving function or macro because this task is taking way too long now that I am getting +50,000 records. Is there a way to create a unique ID for a cell and then copy and paste that ID into a cell in a specific row on another worksheet where a cell in the target row is identical to a cell from the row where the ID is being copied from? I am desparate at this point, so if anyone has any ideas or needs more information, I would be more thatn happy to provide it. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
I need help writing a macro to replace cell contents. (new to
I suggest
1) collect all the unique ID's 2) build your new table 1) We're going to use a collection, more specifically a dictionary object, to use the .Exists method to test if keys exist. In the IDE, set a refence to Microsoft Scripting Runtime in a module, paste Option Explicit Private dGUIDs As Scripting.Dictionary Sub GetIDs() Dim wsheets() As Variant Dim ws As Worksheet Dim wsIndex As Long Dim cell As Range Dim key As String wsheets = Array("sheet1", "sheet2") Set dGUIDs = New Scripting.Dictionary For wsIndex = 0 To UBound(wsheets, 1) Set ws = Worksheets(wsheets(wsIndex)) With ws For Each cell In .Range(.Range("A1"), .Range("A1").End(xlDown)) key = cell.Value If key < "" Then If Not dGUIDs.Exists(key) Then dGUIDs.Add key, key End If End If Next Next End Sub 2) ? you haven't specified how records on more than one sheet get merged. I assume in (1) that column A had the GUIDs. Apart from A then, do the columns dffer on other woksheets? This step should output the ID's into A on a new sheet, then for each of your worksheets copy rows to the relevent columns of the output sheet. so sheet1 may be columns B- G, sheet2 columns H-M and so on? does this sound right....I 'll spend the time writing the code when this is clearer. "Patrick Molloy" wrote: if you use MS Access, this becomes trivial. open a new Access database, link a table to each of the sheets, open a query, add all the sheets & seelct everything, using the GUID as the key for all sheets. The resultant results can be resaved as a table and then you can suck it straight into your main database. "Emoshag" wrote: Hello, I am building an Oracle database and I am going to be using Excel to injest the data. I am trying to give unique values to records and to do so I have been hand copying rows between different worksheets and pasting the same GUID (unique identifier) on matching column values between different worksheets. I am desparately trying to find a time saving function or macro because this task is taking way too long now that I am getting +50,000 records. Is there a way to create a unique ID for a cell and then copy and paste that ID into a cell in a specific row on another worksheet where a cell in the target row is identical to a cell from the row where the ID is being copied from? I am desparate at this point, so if anyone has any ideas or needs more information, I would be more thatn happy to provide it. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
I need help writing a macro to replace cell contents. (new to
See, thats the problem. I am using Access for the first time. I have never
done any sort of database work (audio engineer) and as one of my co-workers has developed the schema for this Oracle database, I am just in charge of populating it. When you say link a table to each of the sheets, what do you mean? I have figured out how to export the Oracle schema into both Access and Excel worksheets, but I do not understand "linking them." Here is a very rough idea of what I imagine the macro will resemble: ----------------------------------------------------- Begin Loop In worksheet A Variable A = Current Row, Cell in Column A Variable B = Current Row, Cell in Column B For Worksheet B, C, D, E, F Row in Current Worksheet = 1 Begin Loop If Cell in Column A of Current Row in Current Worksheet = Variable A Replace Cell in Column B of Current Row with Variable B Next Row until End of File End Loop Next Row in Worksheet A until End of File End Loop I hope this clarifies my problem and not convolutes it. "Patrick Molloy" wrote: if you use MS Access, this becomes trivial. open a new Access database, link a table to each of the sheets, open a query, add all the sheets & seelct everything, using the GUID as the key for all sheets. The resultant results can be resaved as a table and then you can suck it straight into your main database. "Emoshag" wrote: Hello, I am building an Oracle database and I am going to be using Excel to injest the data. I am trying to give unique values to records and to do so I have been hand copying rows between different worksheets and pasting the same GUID (unique identifier) on matching column values between different worksheets. I am desparately trying to find a time saving function or macro because this task is taking way too long now that I am getting +50,000 records. Is there a way to create a unique ID for a cell and then copy and paste that ID into a cell in a specific row on another worksheet where a cell in the target row is identical to a cell from the row where the ID is being copied from? I am desparate at this point, so if anyone has any ideas or needs more information, I would be more thatn happy to provide it. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Replace #VALUE! with contents of cell?? | Excel Worksheet Functions | |||
Why does not Excel 2003 Replace comand romove spaces from cell contents? | Excel Worksheet Functions | |||
Macro to remove contents of cell and move all other contents up one row | Excel Discussion (Misc queries) | |||
replace cell contents | Excel Worksheet Functions | |||
How do I replace the contents of a cell with its value instead of. | Excel Worksheet Functions |