Posts by jonny

Re: Copy/paste worksheet btw 2 workbooks

All that you have is to add following macros to your Personal.xls module, personal.xls is independent workbook.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 20.01.2006 by Jonny
'


'
    Selection.CurrentRegion.Select
    Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste
End Sub

Re: Partial VlookUp

Sorry for inconvenience, my english is not so good to explain myself, so:

I have two different tables with manufacturer names, some of manufacturer names are sam in two table and some are not.
For manufacturer names that are differ I would like anyway to bring them.

For examle: if in a first table I have name National and in a second table I have National Semiconductor, I want that VlookUp will bring National from the first table to the second table, cause "National Semiconductor" string contains "National" string.

Or another example: 1-st table manufacturer Analog ,& 2-nd table manufacturer Analogitech. (Analogitech contains Analog, so VlookUp will find it).

I hope now I clarified the issue.

Re: left command causing trouble

May you add excel sample, you must use TEXT(A2, "dd-mmm-yy").

Re: left command causing trouble

TEXT(A2, "dd-mmm-yy")

Re: Partial VlookUp

This file contains more than 1000 names

So I attach partial file.

Re: Partial VlookUp

Dear Will, you use FIND(" ",G1,1), but it not probably contains " ".
It maybe any long string.

I want to get all strings that beginnig with a source string.

I would like to do "Partitional VlookUp".
What I mean, for example in one table I have following items:
ON
NATIONAL
MICRO

in other table I have:
ON SEMICONDUCTOR
NATIONAL SEMICONDUCTOR
MICRO SEMICONDUCTOR

I would like to link between same items (to look part of the word).

Re: Turning ".Send" method OFF

gmccreedy, thank you.
Yep, you're right

I have some code:

With olMail
                    .To = FullDetail
                    .HTMLBody = NameHtml & ", " & .HTMLBody
                    .Attachments.Add SourceFile
                    .Attachments.Add PcnFile
                    .Send
                End With

After e-mail is sending I would like to turn ".Send" method OFF (to avoid automatically e-mails sending)

Is anyway to do it?

Re: date formula for extracting unique dates

Any function returns value for one cell only and you want to get all unique values. In this case I know solution by code only.

Re: date formula for extracting unique dates

elfudge35, use following code:

Sub test()
    Range("a2").Select
    Range(Selection, Selection.End(xlDown)).Select
    
    Index = 3
    For Each rng In Selection
        If rng.Offset(0, 0) <> rng.Offset(1, 0) And InStr(1, rng.Offset(0, 0), "Total") = 0 Then
            Cells(1, Index) = rng.Offset(0, 0)
            Index = Index + 1
        End If
    Next rng
End Sub

Good Luck!

Re: Emails FollowUp Reminder

If I understood you correctly, you mean to sign manually in a table "status of response", and the e-mail wll be send automatically.

Do you know if the replied or forwarded email carry with it some code/information (in a one of properties) of a source email, that I may catch.

Thanks

I have a lot of customers, I send them requests for approvals & waiting for a response. If after 2~3 days I don't get response, I send this e-mail once more.

If any way to build some macro in VBA that send automatically reminder, if was no response from a customer?

To use maybe EntryID , or smth else.

Thank you.

I have three tables:
1. Manufacturers
2. Suppliers
3. Linked table of both

Attached the data model printscreen.

When I try to insert data to form , I receive following error:
"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."

What wrong?

Re: Building of Correct Relationship

I have found the solution, attached printscreen of Data Model.
Thank you.

I have difficulty to build relationship for next issue:

I have:
1. list of workers (Key: WorkerName)
2. list of assemblies (Key: AssyID)
3. list of tests (Key: TestName)
-------------------------------------------------
a. each worker may check zero or more assemblies.
b. each assembly contains one or more tests.

I will appreciate your help.

Re: User-defined type not defined

Prompt & simple..
Norie, thank you.

I have some code:
When I run it I get error "User-defined type not defined excel vba Outlook.Application".

Sub SendEmails()


    Dim olApp As Outlook.Application
    Dim olMail As MailItem


   Set olApp = New Outlook.Application

May you helpme to find the problem.

Thanks in advance.

Re: Concatenation of Date Format

Norie, Thanks a lot.

I have two columns:

COMPANY|DATE CODE|
----------------------
SONY |01-Jan-04
SHARP |01-Jul-05

When I try to concatenate the rows, I get following:
SONY 37987
SHARP 38534

How I can get by concatenation:
SONY 01-Jan-04
SHARP 01-Jul-05

Thanks