User:Separ13655

Introduction
I have been playing Guildwars since May 2005 (any bets on what the year 4 minis will be?) and have made a few contributions to Guildwiki. I am working on various titles now and have been thinking of how to make Guildwiki even more useful.

Map Notes for Missions, Quests and Dungeons
This shows how I would like to modify the mission/quest/dungeon maps for those maps with many alphanumeric codes added to them. It would allow a user to view the map and comments and not need to scroll around the page during a mission for hints on what the map codes mean. Here is an example:

Objectives
Kill Markis and his Jade Armor guards.
 * Find the Seer.
 * ADDED Kill the Eidolon.
 * ADDED Deliver the Spectral Essence to the Seer to infuse your armor.
 * BONUS Defeat The Inquisitor before he leaves with the information about the Shining Blade.

Primary
The main objective of the mission is to find and kill Markis. Markis is the treacherous member of the council of the Shining Blade who has foiled all your previous attempts at building a resistance to the White Mantle. Markis is no imposing boss and the main objective itself is quite straightforward. Reaching the Seer is the hardest part of the mission.

=Excel Code for Working with Gift of the Traveler drop rate raw data=

Option Explicit

'Version 1.0 15 August 2009

'These three procedures work with data copied from the 'http://guildwars.wikia.com/index.php?title=Drop_rate/Gift_of_the_Traveler&action=edit&section=3 'and pasted into cell A1 of an Excel 2003 worksheet

Sub SplitTableData

'Code assumes Total Column in first column with a title "Total" 'Followed by at least one column of data 'Followed by a signature column with a title "Signature" 'This procedures splits the pasted Edit Page Drop Rate data into separate cells 'on the code-created 'Split' Data worksheet Dim iDataColumnCount As Integer Dim iLastInputRow As Integer Dim iTotalColumn As Integer Dim iSignatureColumn As Integer Dim sImportWorksheetName As String Dim iCharLastFoundPosition As Integer Dim iX As Integer Dim iY As Integer Dim iFirst As Integer Dim iLast As Integer 'Ensure TextToColumns does not have | set as other delimiter If Range("A1").Value = "" Then Range("A1").Value = "XYZZY" Range("A1").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _ Semicolon:=False, Comma:=False, Space:=False, Other:=False, OtherChar _ :="", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True If Range("A1").Value = "XYZZY" Then Range("A1").Value = "" For iX = Worksheets.Count To 1 Step -1 If Worksheets(iX).Range("A1").Value = "== Raw Data ==" And _ Worksheets(iX).Cells(Application.Rows.Count, 2).End(xlUp).Row = 1 And _ Worksheets(iX).Name <> "Edit Text" Then sImportWorksheetName = Worksheets(iX).Name End If       If Worksheets(iX).Name = "Split Input" Then Application.DisplayAlerts = False Worksheets(iX).Delete Application.DisplayAlerts = True End If       If Worksheets(iX).Name = "Edit Text" Then Application.DisplayAlerts = False Worksheets(iX).Delete Application.DisplayAlerts = True End If   Next If Len(sImportWorksheetName) = 0 Then MsgBox "No worksheet has '== Raw Data ==' in cell A1" & vbCrLf & vbCrLf & _ "Paste data from the edit page into a blank worksheet and try again." GoTo End_Sub End If   Worksheets.Add(before:=Sheets(1)).Name = "Split Input"

Worksheets(sImportWorksheetName).Select iLastInputRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row For iX = 1 To iLastInputRow 'Range("A" & iX).Value = CStr(Trim(Range("A" & iX).Value)) If Left(Cells(iX, 1).Text, 6) = "!Total" Then iTotalColumn = iX       If Left(Cells(iX, 1).Text, 10) = "!Signature" _ And iSignatureColumn = 0 Then iSignatureColumn = iX   Next iDataColumnCount = iSignatureColumn - iTotalColumn - 1 For iX = ActiveSheet.Shapes.Count To 1 Step -1 ActiveSheet.Shapes(iX).Delete Next With Worksheets("Split Input") For iX = 1 To iLastInputRow Application.StatusBar = "Processing row: " & iX           iY = 1 iLast = 1 Select Case Left(Cells(iX, 1).Text, 1) Case "|" If Left(Cells(iX, 1).Text, 2) = "|-" Or _ Left(Cells(iX, 1).Text, 2) = "|}" Then .Cells(iX, 1) = ActiveSheet.Cells(iX, 1) Else For iY = 1 To iDataColumnCount + 2 iFirst = InStr(iLast, Cells(iX, 1), "|") iLast = InStr(iFirst + 1, Cells(iX, 1), "|") If iLast - iFirst = 1 Then iFirst = iLast iLast = InStr(iFirst + 1, Cells(iX, 1), "|") End If                       If iY = iDataColumnCount + 2 Then iLast = 10000 End If                       .Cells(iX, iY) = Trim(Mid(Cells(iX, 1).Text, iFirst + 1, iLast - iFirst - 1)) Next End If           Case Else .Cells(iX, 1) = ActiveSheet.Cells(iX, 1) End Select Next End With End_Sub:

End Sub

Sub AddFormulasToSplitDataWorksheet

'This procedures adds formulas to the code-created 'Split' Data worksheet 'and recalculates values Dim iRateRow As Integer Dim iTotalRow As Integer Dim iFirstDropRow As Integer Dim iLastDropRow As Integer Dim iSignatureRow As Integer Dim iLastInputRow As Integer Dim sTemp As String Dim iSignatureColumn As Integer

Dim iX As Integer iLastInputRow = Worksheets("Split Input").Cells(Application.Rows.Count, 1).End(xlUp).Row For iX = 1 To iLastInputRow 'Range("A" & iX).Value = CStr(Trim(Range("A" & iX).Value)) If Left(Cells(iX, 1).Text, 6) = "!Total" Then iTotalRow = iX       If Left(Cells(iX, 1).Text, 10) = "!Signature" _ And iSignatureRow = 0 Then iSignatureRow = iX   Next iSignatureColumn = iSignatureRow - iTotalRow + 1 iFirstDropRow = Worksheets("Split Input").Cells(1, 2).End(xlDown).Row iRateRow = Worksheets("Split Input").Columns(1).Find("Rate", lookat:=xlWhole).Row iTotalRow = iRateRow - 2 iLastDropRow = Worksheets("Split Input").Cells(iTotalRow, 2).End(xlUp).Row 'Add Formulas Range(Cells(iTotalRow, 1), Cells(iTotalRow, iSignatureColumn - 1)).FormulaR1C1 = _ "=SUM(R" & iFirstDropRow & "C:R" & iLastDropRow & "C)" Range(Cells(iRateRow, 2), Cells(iRateRow, iSignatureColumn - 1)).FormulaR1C1 = _ "=R[-2]C/R" & iTotalRow & "C1" 'Change Formulas to Text Range(Cells(iTotalRow, 1), Cells(iRateRow, iSignatureColumn)).Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Cells(iTotalRow, 1).Select

End Sub

Sub CreateEditPage 'This procedure copies data from the 'Split Data' worksheet and pastes it inti 'the code-created "Edit Text" worksheet Dim iDataColumnCount As Integer Dim iLastInputRow As Integer Dim iTotalColumn As Integer Dim iSignatureColumn As Integer Dim sImportWorksheetName As String Dim iCharLastFoundPosition As Integer Dim iX As Integer Dim iY As Integer Dim iFirst As Integer Dim iLast As Integer For iX = Worksheets.Count To 1 Step -1 If Worksheets(iX).Name = "Edit Text" Then Application.DisplayAlerts = False Worksheets(iX).Delete Application.DisplayAlerts = True End If   Next Worksheets.Add(before:=Sheets(1)).Name = "Edit Text"

Worksheets("Split Input").Select iLastInputRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row For iX = 1 To iLastInputRow If Left(Cells(iX, 1).Text, 6) = "!Total" Then iTotalColumn = iX       If Left(Cells(iX, 1).Text, 10) = "!Signature" _ And iSignatureColumn = 0 Then iSignatureColumn = iX   Next iDataColumnCount = iSignatureColumn - iTotalColumn - 1 For iX = ActiveSheet.Shapes.Count To 1 Step -1 ActiveSheet.Shapes(iX).Delete Next With Worksheets("Edit Text") For iX = 1 To iLastInputRow Application.StatusBar = "Processing row: " & iX           iY = 2 If Cells(iX, 2) = "" Then .Cells(iX, 1) = Cells(iX, 1) Else .Cells(iX, 1) = "|" & Cells(iX, 1) Do While Cells(iX, iY) <> "" If Cells(iX, 1) = "Rate" And IsNumeric(Cells(iX, iY).Value) Then .Cells(iX, 1) = .Cells(iX, 1) & "||" & Format(Cells(iX, iY), "0.00%") Else .Cells(iX, 1) = .Cells(iX, 1) & "||" & Cells(iX, iY) End If                   iY = iY + 1 Loop End If       Next End With End_Sub:

End Sub