Excel VBA

A macro I used to creat the hyperlinks to another sheet which is in sequence

Sub CreateLinks()


Dim i, j, NoNeeded As Integer

i = 19 'copy source start row

j = 2 'copy destination start row

NoNeeded = 500 'number of links

Dim cellSelect As String

Dim cellTarget As String
For j = 2 To NoNeeded

cellSelect = "A" & CStr(j)

Range(cellSelect).Select

cellTarget = "!A" & CStr(i)
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=_ "'UNKNOWN (1)'" & cellTarget, TextToDisplay:="'UNKNOWN (1)'" & cellTarget

i = i + 1

Next j


End Sub

Comments

Post a Comment

Popular posts from this blog

Outlook : "operation failed, object could not be found. " when trying to close a PST.

Image
When you try to close a PST. The error message "operation failed, object could not be found" pop up. After you have tried scanpst and detect and repair. Still won't work. Solution. (a hard way) Start Regedit Go to Key HKCU\Software\Microsoft\Windows NT\CurrentVersion\Windows Messaging Subsystem\Profiles\Microsoft Outlook Internet Settings Where "Microsoft Outlook Internet Settings" is the Outlook profile name that has problem. (note: Outlook support multiple profile even for same Windows login) In the HKCU\Software\Microsoft\Windows NT\CurrentVersion\Windows Messaging Subsystem\Profiles\Microsoft Outlook Internet Settings, you will see a bunch of long string sub keys in it Like in the format of GUID, like e.g. 75d1fa940a0d3a4497130516edbde90e Then, start at the very first sub keys of HKCU\Software\Microsoft\Windows NT\CurrentVersion\Windows Messaging Subsystem\Profiles\Microsoft Outlook Internet Settings. Right click on sub key, Select Find (menu) And then,
Read more

Troubleshooting Outlook Express (IMAP, POP3, HTTP, NEWS) with the log file

Just happen to know this when trying to solve my friend OE problem. You can log in POP3, IMAP, NEWS, HTTP commands of your Outlook Express into a log file. (note, the password in the log file is encrypted by Microsoft, therefore, it is safe to ask user to send the log file over if you are a IT support) To enable logging. ------------------------- Tools(menu) - Options (menu) - Maintenance(menu) - check on the protocol you need. (POP3, IMAP, NEWS or HTTP) To view the log file -------------------------- Goto path C:\Documents and Settings\YourUserName\Local Settings\Application Data\Identities\{8D32DF8B-D3B8-4783-A0C5-FE37E2FC8659}\Microsoft\Outlook Express\pop3.log (for pop3) imap.log for (imap) and other respectively. P.S. The {8D32DF8B-D3B8-4783-A0C5-FE37E2FC8659} may vary and there could be a few Identities in it. Look for the folder with the structure of *.dbx similar to your Outlook Express email account.
Read more

MSSQL GROUP_CONCAT

This is using XML feature of the SQL server. (nothing new here) select Type, RestaurantNames from Restaurant AS A CROSS APPLY (SELECT RestaurantName + ',' FROM Restaurant AS B WHERE A.Type = B.Type FOR XML PATH('')) D (RestaurantNames) GROUP BY Type, RestaurantNames This is to get something like below (similar to MYSQL GROUP_CONCAT) Type |RestaurantNames ----- --------------- Chinese Food | Ah Yat Abalone, Liang Yah Yong Tau Foo, Indian Food | Kanna Curry House, Western Fast Food | Burger King, McDonald instead of multiple rows. like Type |RestaurantName ----- --------------- Chinese Food |Ah Yat Abalone Chinese Food |Liang Yah Yong Tau Foo Indian Food |Kanna Curry House
Read more