How Do I Insert a Subroutine in Excel 2007?

[klicks]

Hi,

This might be a dumb question... but I need help on how and where to insert the following subroutine in my excel 2007 worksheet. Exact instructions on how to insert the code and how to run it.

Thank you very much for any help.
Ted

Sub klicks()
Dim S1 As Worksheet, S2 As Worksheet, V1 As Variant, V2 As Variant, V3() As Variant
Dim R1 As Range, R2 As Range, d As Object
Dim i As Long, ct As Long, n As Variant
Set S1 = Sheets("Sheet1")
Set S2 = Sheets("Sheet2")
Set R1 = S1.Range("A2:B" & S1.Cells(Rows.Count, "A").End(xlUp).Row)
V1 = R1.Value
Set R2 = S2.Range("A2:B" & S2.Cells(Rows.Count, "A").End(xlUp).Row)
V2 = R2.Value
ReDim V3(1 To UBound(V1, 1) + UBound(V2, 1), 1 To 3)
Set d = CreateObject("Scripting.dictionary")
For i = 1 To UBound(V1, 1)
If Not d.exists(V1(i, 1)) Then
ct = ct + 1
d.Add V1(i, 1), ct
n = Application.CountIf(R2.Columns(1), V1(i, 1))
If n = 0 Then
V3(ct, 1) = V1(i, 1)
V3(ct, 2) = V1(i, 2)
V3(ct, 3) = ""
Else
V3(ct, 1) = V1(i, 1)
V3(ct, 2) = V1(i, 2)
V3(ct, 3) = V2(Application.Match(V1(i, 1), R2.Columns(1), 0), 2)
End If
End If
Next i
For i = 1 To UBound(V2, 1)
If Not d.exists(V2(i, 1)) Then
ct = ct + 1
d.Add V2(i, 1), ct
n = Application.CountIf(R1.Columns(1), V2(i, 1))
If n = 0 Then
V3(ct, 1) = V2(i, 1)
V3(ct, 2) = ""
V3(ct, 3) = V2(i, 2)
Else
V3(ct, 1) = V2(i, 1)
V3(ct, 2) = V2(i, 2)
V3(ct, 3) = V1(Application.Match(V2(i, 1), R1.Columns(1), 0), 2)
End If
End If
Next i
Application.ScreenUpdating = False
S1.Range("F1:H1").Value = Array("Item No.", "No.", "No.")
S1.Range("F2:H" & ct + 1).Value = V3
S1.Columns("F").NumberFormat = "0"
S1.Columns("F:H").AutoFit
Application.ScreenUpdating = True
End Sub

 

[brucef2112]

OK. first make a copy of your working spreadsheet.

no, really, stop and make a copy so you don't cry when all goes wrong..

Good!

Open your spreadsheet you want to use this macro in..
Once opened, press Alt+F11 to open the Visual Basic Editor.
On the menu bar at the top , click on Insert > Module
you will see a Module1 added to the workbook (pane on left side)
on the right side you now have a white screen with the cursor blinking on it.
Paste your code here.

.Go back to the spreadsheet.
press Alt+F8 to open the Macro window.
Select your macro (named) "klicks" then click the RUN button.

If all goes well, you won't need the backup copy.
If it doesn't go well, you can restore it by making a copy of the backup you created before you started.
You did make a backup, didn't you?
Then call the person who gave you the macro. If you got the code into the module and it did something wrong while running then call the person who wrote the code.
Only they will know exactly what is happening with this chunk of code.

good luck

 

[klicks]

WOW... Thank you so much.
Really appreciate it.

Ted