Moving data from worksheet to columns

**nobleprince** · 02-02-2012, 04:01 PM

Hi,

I have a new/different requirement.

Could you please assist with updating the code so that the hyperlinks are captured from worksheet "Company names" and placed into column D within "Sheet1".

The macro should check for any words after the "at" or "@" word/sign (within column E), and if there are any exact words found after the "at" or "@" then the macro should take the URL from within worksheet "Company names" and put them into column D. If no words are found after the "at" or "@" sign then the cell should be marked as "-".

I have attached the spreadsheet with BEFORE and AFTER.

I look forward to your assistance.

Thank you,
Ash

**Marcol** · 02-03-2012, 09:23 AM

Given that you are using 2007
In D3

=IFERROR(INDEX('Company names'!B:B,MATCH(IF(ISNUMBER(SEARCH(" at ",E3,1)),TRIM(MID(E3,SEARCH(" at ",E3,1)+LEN(" at "),255)),IF(ISNUMBER(SEARCH("@",E3,1)),TRIM(MID(E3,SEARCH("@",E3,1)+1,255)),"-")),'Company names'!A:A,0),1)),"-")

Drag/Fill Down

2003 will require a helper column, see the attached.

All versions could use this UDF in a standard module

Function GetURL(rng As Range)
    Dim strTemp As String
    
    Select Case True
        Case InStr(1, rng, " at ") > 0
            strTemp = Trim(Mid(rng, InStr(1, rng, " at ") + Len(" at "), 255))
        Case InStr(1, rng, "@") > 0
            strTemp = Trim(Mid(rng, InStr(1, rng, "@") + 1, 255))
        Case Else
            GetURL = "-"
            Exit Function
    End Select
    If strTemp <> "" Then
        With WorksheetFunction
            GetURL = .Index(Sheets("Company Names").Range("A:B"), .Match(strTemp, Sheets("Company Names").Range("A:A"), 0), 2)
        End With
    End If
End Function

Enter in D3

=GetURL(E3)

Drag/Fill Down

**nobleprince** · 02-03-2012, 01:17 PM

Hi Marcol,

I have Excel version 2007. I am fairly novice to the world of macros. I'm not able to run the macro/code for the file you have attached. Could you please create a button which I can press and it should arrange the data. Please note, that I have over 1000 records and/or company names and I should be able to run the macro for 1000+ records and/or company names. Also, I am not sure why two excel spreadsheets open when I open the attachment? Only one file should open.

Thank you.
Ash

**Marcol** · 02-03-2012, 04:21 PM

1/. I don't fhink that two workbooks are opening, is it not just one workbook showing in two windows?

2/. Does the formula I gave you for 2007 work as you need? This should be your preferred option.

3/. The code is for a User Defined Function, no need for buttons, just put =GetURL(E3) in D3 then drag the fill handle down to where your data ends.