VBA:Populating Cells/List From External Data
Hi,
can anyone help, I have a background in Perl, JScript etc but am new to Excel/VBA. I need some hints or prefferably code that will allow me to perform the following on an Excel worksheet. I am trying to populate several "cells" from data which is external to the Excel application, the data is in the format: Player#Club#Cost i.e. Ronaldo#Manchester United#8.0 I want the "Player" part of the data to be read into list form when a cell is selected, if one of the players from the list is selected then that player will populate that cell (D1:D11) - ALSO, the "Club" will populate another cell AND the "Cost" another cell, i.e. Cells D1:D11 will be the PALYERS Cells E1:E11 will be the CLUBS Cells F1:F11 will be the COSTS If I could push my luck a little further then I would also like a bit of data checking to be performed at the same time - I can only allow 2 players from the same club to be selected, anymore is not allowed - so if cells E1:E11 contain MORE THAN 2 of the same string value then I need a warning, maybe if the cells containing the cinflicting data could FLASH in a red colour. thanks for any help, Mark... |
VBA:Populating Cells/List From External Data
mystring = "Ronaldo#Manchester United#8.0"
first_Item = Left(mystring, InStr(mystring, "#") - 1) second_item = Mid(mystring, InStr(mystring, "#") + 1) third_Item = Mid(second_item, InStr(second_item, "#") + 1) second_item = Left(second_item, InStr(second_item, "#") - 1) " wrote: Hi, can anyone help, I have a background in Perl, JScript etc but am new to Excel/VBA. I need some hints or prefferably code that will allow me to perform the following on an Excel worksheet. I am trying to populate several "cells" from data which is external to the Excel application, the data is in the format: Player#Club#Cost i.e. Ronaldo#Manchester United#8.0 I want the "Player" part of the data to be read into list form when a cell is selected, if one of the players from the list is selected then that player will populate that cell (D1:D11) - ALSO, the "Club" will populate another cell AND the "Cost" another cell, i.e. Cells D1:D11 will be the PALYERS Cells E1:E11 will be the CLUBS Cells F1:F11 will be the COSTS If I could push my luck a little further then I would also like a bit of data checking to be performed at the same time - I can only allow 2 players from the same club to be selected, anymore is not allowed - so if cells E1:E11 contain MORE THAN 2 of the same string value then I need a warning, maybe if the cells containing the cinflicting data could FLASH in a red colour. thanks for any help, Mark... |
VBA:Populating Cells/List From External Data
Ok, so that's how to split the string - but I still need help on:-
a). when a cell is selected, reading the data into a list from the external source. b). populating the 3 different cells, player, club, cost. c). checking for existance of more than 2 clubs. cheers. |
VBA:Populating Cells/List From External Data
Is the external source a file?
" wrote: Ok, so that's how to split the string - but I still need help on:- a). when a cell is selected, reading the data into a list from the external source. b). populating the 3 different cells, player, club, cost. c). checking for existance of more than 2 clubs. cheers. |
VBA:Populating Cells/List From External Data
I created a file on the Desktop called:
data.mark1 I imported the data manually with the Recorder turned on and got: Sub Macro1() ' ' Macro1 Macro ' Macro recorded 4/12/2008 by James Ravenswood ' ' With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;C:\Documents and Settings\Owner\Desktop\data.mark1", Destination:=Range _ ("D1")) .Name = "data.mark1" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 437 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = False .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = False .TextFileOtherDelimiter = "#" .TextFileColumnDataTypes = Array(1, 1, 1) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With End Sub -- Gary''s Student - gsnu2007g " wrote: Ok, so that's how to split the string - but I still need help on:- a). when a cell is selected, reading the data into a list from the external source. b). populating the 3 different cells, player, club, cost. c). checking for existance of more than 2 clubs. cheers. |
All times are GMT +1. The time now is 09:06 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com