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.

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.
>

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.
> >

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.
> >

OK, I see what you are getting at. Maybe this will clarify some things.

1. Column A and Column B (the two in use) are the same on every sheet.
2. Column A is the unique column.
3. Column B will have duplicate values. (except on Worksheet A, this one is
unique as well)
4. Worksheet A is the key-off worksheet (all of the replacing will be done
based on the values in A)

The macro should go row by row on worksheet A. On each new row in Worksheet
A, the macro will search the rest of the workbook and replace the values of
Row A with the unique value in row A of Worksheet A when the values of row B
of Worksheet A and the current worksheet are the same.

Hope this helps....


"Patrick Molloy" wrote:

> 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.
> > >

The code below:
1) Collects the values from sheetA using column A as the GUID and saving the
value from column B

2)
The for the rest of the sheets, sheet by sheet
for each guid in column A , copy the the dictionary value

The net will be that the values in column B will be for all sheets will be
the same as per sheetA for matching GUIDs


' not set a reference to Microsoft Scripting Runtime
Option Explicit
Private dGUIDs As Scripting.Dictionary
Sub Main()
Dim wsheets() As Variant
Dim ws As Worksheet
Dim wsIndex As Long
Dim cell As Range

Dim key As String
Dim keyvalue As String

wsheets = Array("sheetB", "sheetC", "sheetd") ' ....etc
Set dGUIDs = New Scripting.Dictionary

' collect data from SheetA

With Worksheets("SheetA")
For Each cell In .Range(.Range("A1"), .Range("A1").End(xlDown))

key = cell.Value
If key <> "" Then
If Not dGUIDs.Exists(key) Then
keyvalue = cell.Offset(0, 1).Value
dGUIDs.Add key, keyvalue
End If
End If

Next
End With

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 dGUIDs.Exists(key) Then
keyvalue = dGUIDs.Item(key)
cell.Offset(0, 1).Value = keyvalue
End If
End If

Next
End With
Next
End Sub





"Emoshag" wrote:

> OK, I see what you are getting at. Maybe this will clarify some things.
>
> 1. Column A and Column B (the two in use) are the same on every sheet.
> 2. Column A is the unique column.
> 3. Column B will have duplicate values. (except on Worksheet A, this one is
> unique as well)
> 4. Worksheet A is the key-off worksheet (all of the replacing will be done
> based on the values in A)
>
> The macro should go row by row on worksheet A. On each new row in Worksheet
> A, the macro will search the rest of the workbook and replace the values of
> Row A with the unique value in row A of Worksheet A when the values of row B
> of Worksheet A and the current worksheet are the same.
>
> Hope this helps....
>
>
> "Patrick Molloy" wrote:
>
> > 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.
> > > >