I am desperately trying to get full postcode lists from two websites: [URL] .... and [URL] ........
The latter of the two is harder to get (I'm told) so it could also be available at: [URL] .... by selecting only the "Collect+" stores.
I run a delivery business & work comes in bit by bit by postcode. I thus enter the postcodes into my spreadsheet.
Then when it comes time to process the work I'm wanting to be able to do something that will highlight all postcodes that match the same colour.
I posted this query on another forum & a helpful guy gave me the code below which worked a charm, but only for a while. Since then I have slightly tweaked the spreadsheet, taken a couple of fields off & added a couple. The code now hardly works or if it does will only do one column or row & not the complete spreadsheet.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim oDn As Integer, oAc As Integer, Dn As Range, oPC, nDn As Integer, nAc As Integer, Ac, c, p
Dim Rw1 As Range, Rw2 As Range, Rw3 As Range, Rw As Integer
If Target.Address(0, 0) = "A1" Then
Set Rw1 = Range(Range("E2"), Range("e" & Rows.Count).End(xlUp))
Set Rw2 = Range(Range("G2"), Range("G" & Rows.Count).End(xlUp))
Set Rw3 = Range(Range("I2"), Range("I" & Rows.Count).End(xlUp))
Rw = WorksheetFunction.Max(Rw1.Count, Rw2.Count, Rw3.Count)
oPC = Range("E2:I" & Rw + 1)
c = 2
For oAc = 1 To UBound(oPC, 2)
For oDn = 1 To UBound(oPC, 1)
If Not oAc Mod 2 = 0 Then
If c = 25 Then c = 2
c = c + 1
I have a load of postcodes over 8 different tabs, the problem is the format of the postcode is wrong. I basically need to delete the first gap of each cell to make the postcode valid -
DL 7 9
DL 8 1
DL 8 2
DL 8 4
DL 8 5
DL 9 3
DL 9 4
DL 10 4
DL 10 5
DL 10 6
DL 10 7
DL 11 7
You see I need to have it like DL7 9, or DL10 7, but i'm not sure how, I've attached the file so you can have a look.
I have a spreadsheet with many many worksheets & on each of those worksheets many many postcodes.
I am looking for a way where I can have a list of postcode stored once somewhere (in excel, word or whatever) & then when we type postcodes into the Excel spreadsheet & click whatever to start the macro or run the code it will refer to where I have the postcodes saved & then highlight any that match on the worksheet page.
I have a list of postcodes. Half of them belong to girls, and half of them belong to guys. I have the distances between each postcode in a matrix. I need to put these postcodes into groups of 6, where 3 of the postcodes belong to girls and 3 to guys, in a way that minimises the sum distance between all the houses in every group. I want each group to contain houses that are as close together as possible.
View 7 Replies View RelatedI have a list of post codes two letter starts by region. e.g.
inner london:
EC
WC
SW
W
NW
E
SE
In addition I have several very long lists of postcodes which I can obviously pull out the first two letters from using the Left function.
However I am wondering what is the best way to filter the column of postcodes into the postcode defined regions such as inner london nicely.
I have these postcodes as example below but the array formula I was going to use won't work because, for example when I count everything with the Birmingham post code 'B' it counts every thing that contains the letter B which could also be in the post code BA1 3RL?
Excel 2003FGHIJKL2AB11 7TFWEB3ECRAB143AB12 3NFWEB3ECRAL54AB14 0QNWEB396FECRB1295AB15 4ANWEB34ECRBA86AB15 5LRWEB34ECRBB4Sheet1 (2)
I'm trying to write a formula which will return postcodes from a list of descriptions which aren't consistent in their layout.
For example, I need this to happen
UB3 3NQ - APR13
SW3 5RQ - APR13
Jul 12 - apr13 accrual - ME9 4FW
Mar 12 - apr13 accural - SO14 7P2
Returned to another column as,
UB3 3NQ
SW3 5RQ
ME9 4FW
SO14 7P2
The issue I'm having is that the postcodes aren't in the same place in order to use LEFT, RIGHT or MID functions, and they aren't always proceeded or followed by dashes or spaces in the same way.
I need the returned postcodes to come back in a uniform way so that any duplicates are grouped by the relevant pivot table.
I am working on sales information which includes postcodes. What i need to do is seperate the first or first two text characters from the rest of the postcode. I have attached a small snipet of what i am working on. Currently i am using the =Left(A4,2) but this will give me in some case a numerical value aswell. For example E1 or G1 in the case of the sample attached. Is there a formula that exists where it will just return the text values in a cell and not numerical values.
View 6 Replies View RelatedI have a list of records each with a postcode that takes either of the two formats:
SA1 6HU
N4 3HF
I also have a list of postcodes that show only the first part of postcodes
SA1
SA2
N3
N4
What formula would look up "SA1 6HU" and return SA1? while being also able to look up "N4 3HF" and return N4?
I have a spread I use daily where I need to go to a series of links on a site and extract data- all that is programmed. But it's a site that requires I first be logged into my account.
Code:
Sub AcctLogin()
Dim a As String
Set ie = CreateObject("InternetExplorer.Application")
With ie
.Visible = True
[Code] ....
The above code works, but it opens up an IE window. I'd just like it to log in in the background so I don't have to deal with a new window..
I am using VBA to open an IE page and try to get some info however i cant seem to grab it
This is the code i have..
[Code].....
I have inspected the code from the website and it is this
[Code] ........
The problem is i dont know how to get the info in the "Metaname, ICBM".
I am looking to pull information from certain websites and put them in excel. I'm not quite sure where to start. I have tried the search option but it is not returning anything.
View 3 Replies View RelatedI am wanting to be able to lauch www.fafsa.gov from within Excel. In other words I want to be able to put a button on screen and when the user clicks on the button it will record some statistical data and then lauch the website. I know how to do everything except lauch the website. Can you lauch a website using code from within Excel.
View 4 Replies View RelatedI have as list of company registration numbers and would like you use code to input them into the companies house website - Failure Page
Comany Reg No example - 03292899
In order to get the date of the last accounts.
The problem is then when you submit on the site i cant see how it passes the company reg number through to load the next page. If I can get to the page then i have code to get what i need from the page but i cant find a whay to get the to page that i want.
how to use the example reg number to access the companies house page for this company.
I am trying to bring in a web page into excel but when it brings it in it misses the little football .gif. My code is like this if I don't get this gif its another bunch of work to read through the data.
Sub Get_playbyplay()
Dim cur_year As Integer
Dim game_url As String
Dim cur_row As Integer
Dim site_url As String
Dim paste_row As Long
Dim Heading As String
Dim q1_row As Long
Dim find_last As Long
Dim space_count As Integer
Dim prev_year As Integer
'Main loop each loop one season
I am trying to extract data from a website:
[URL] .....
I looked at the source code of the website and realized that if you notice (above) that the variables listed in the link (i.e year, month, day) are exactly what i need to change in order to get the data for a specific date. how can I accomplish this using VBA. so say I have in on an excel sheet year in column A, month in column B, and days in column C (time interval is constant so we don't have to worry about stime and etime). and i run the macro and it loops through each row taking year,month,day for all rows and saving the data as .csv or xls files?
I have list of various web site and i want to keep only valid site . How it is possible ?
View 1 Replies View RelatedI'm trying to automate some webscraping on a website that requires a login, and was wondering how I would do so using Macros with a specific username password somewhere in the spreadsheet, lets say B2, and C2 respectively. The website I'm trying to login is this; http://underground.chacha.com/account/. I think I have most of the scraping figured out; its just the log-in for now.
View 2 Replies View Relatedretrieving data from financial website databases like yahoofinance.com and bloomberg.com. I'm trying to make an automatic stock analysis model to read from the website database and retrieve the data into excel sheets. For example, when opening the excel model the user gets a popup to enter the stock ticker, the user enters the ticker and gets a set of data. Is this do-able in excel?
View 2 Replies View RelatedI wonder if there is some way to copy a list from a website to an excel sheet.
I am referring to this particular website.
[URL]...
There is a cashflow savings calculator on this site.
I want to copy the categories stated in step 1 of this calculator to an excel sheet.
Is there some easy way to do this?
I'm trying to put an excel sheet on to a website. The website allows HTML snippets and I know how to save the excel sheet as a webpage but I don't know how to transfer the webpage I've created on to the website. It asks me to post the snippet but I don't know what it wants? What is the snippet?
View 4 Replies View RelatedI'm trying to login to [URL] ...... using VBA. I cannot share login details. How it might be able to work?
View 3 Replies View RelatedI have list of url in a column. I want to fetch data from all the links and store it in a excel sheet.
I have written code, the code fetches only 1 links data at a time. I want to loop that vba macro and fetch all the data at a time.
I have an excel sheet that has a lot of APN (parcel numbers) on it. I would like to run that through the assessors page [URL] to get the address and owners name. It seems like a very simple thing to do, but... How would I make it run each parcel through the assessors page to get the name and address information. Is there a tool I can install into Excel to make this easier?
View 7 Replies View RelatedI am building an application through Excel to update specific internal website information. My question is, is there an easier way to identify and view the tags on a web page without having to right-click and "view source"?
View 2 Replies View RelatedI'm attempting to familiarize myself with pulling data from an online database into spreadsheets for manipulation. I'm relatively familiar now with pulling tables using webquery, etc. but my next feat is accessing data from sites which require some "input" before retrieving the desired data set.
Currently, I have a site which contains information and prompts for the "year" of information in a dropdown box. I've attempted to do this as indicated below, and was able to "select" a year, however the page doesn't load the data like it would if I were to manually click on it.
Sub GetEmissionsData()
Dim ieApp As InternetExplorerDim ieDoc As ObjectSet ieApp = New InternetExplorer
ieApp.Visible = True
[Code] ........
Separately I've tried setting the year using another method, but this just give an error
Sub GetEmissionsData()
Dim ieApp As InternetExplorerDim ieDoc As ObjectSet ieApp = New InternetExplorer
ieApp.Visible = True
[Code] .......
I'm not sure if the error is due to some issue with my code - or if "Label1" isn't the correct label for the dropdown / combobox on the site. I didn't post the site source on this page - but the URL indicate in this post is the one I'm interested in.
I ran into a problem with the following code
Dim URL As String
URL = Worksheets("References & Resources").Range("URLMSL")
Dim IE As Object
Set IE = CreateObject("internetexplorer.application")
IE.Navigate URL
IE.Visible = True
My problem is that on most of the workstations here, it will open internet explorer, then open the file in Excel. I just want it to Save the file.
I would like to write a script to upload image to this website. These images are in my local directory and their names are defined in excel file (cell J2:J5)This button in this website using flash so it make me hard.
i attached the file here
Using VBA to upload image to website
I am trying to open/save a csv file from a website but the URL only takes me to the page the file is hosted on then the file has to be clicked and it asks you to save it. It doesn't appear that it is a shortened URL and using Firebug I have gone through the HTML trying to find a link to the file but haven't. These files are date specific and ultimately the macro will be used to scrape different dates.
The URL: [URL] From that you will select the year>month>day number>download file