Posts by yjoshi

    Many times we need to get data which is on web (in tabular format) into Excel.
    If we do copy-paste, all data is placed in a single column and then we need to go and manually (or thru Data>Text to columns..) edit it.

    Instead you can try this.

    Take an example of following scorecard of India Srilanka match.…d_scorecard_full2_553.htm

    Say you want this data in the excel properly.

    -Open a New Workbook
    -Data -> Get External Data -> New Web Query
    -in the field enter the url given above (or the data you want to import)
    -select from: entire page / only tables /only specific tables
    -If you like the colour scheme on the web, select from the formatting options
    -if you want that the link should not be maintained after import, go to Advanced.. and check "disable data recognition"
    -Press OK

    The entire data will be placed in the excel sheet. No need to separate it in columns again.

    This has benifited me and some of my friends immensely, hope this helps you as well.:cheers:

    I know you must have thought of it...

    But just in case...

    Did you check in your Accounting System that the data exported can be exported in any of the following format..
    .cvs, .mdb, .xls or foxpro database etc.

    Most of the Accounting Systyms do give this facility.

    (I hope i am not irritating you by asking this, but at times we forget these things, so please don't mind)

    That means all the quotes that do not have sloved.. are unsolved?

    (I know i am behaving like a tippical Quality Assurance guy, but can't help..)

    Actually I do agree and also in my first quote, I wrote, but i have specifically put this after seeing so many old quotes laying untouched for almost a month though not completely solved...

    As i mentioned, this group is getting very popular, and this enhancement will be good in long term...

    ofcourse, this is just a suggestion

    Following is the code that you want...

    Private Sub Workbook_Open()
    Dim dt, dt_rng As range
    Sheet1.range("a1:IV65536").Locked = False
    For Each dt In Selection

    If Format(dt.Value, "DD-MMM-YYYY") = Format(Now(), "DD-MMM-YYYY") Then
    range(dt, dt.Offset(5, 5)).Font.Bold = True

    range(dt, dt.Offset(5, 5)).Locked = True
    End If

    Sheet1.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

    End Sub

    Let us know if you want anything more...

    Also attached a worksheet that contains the formula and the data.


    I am using this forum for 5-6 days, and finding it very interesting..

    I have one suggestion to make here, which many will agree...

    There is no way we can make out whether the question is solved or still pending. It happens many times there are
    questions on which there are 4-5 replies and we think that they are solved, but they are still unsolved.
    Some people put: Solved in the text after it is answered fully, but that is rare.

    The risk here is that, a person who wants to answer, will first see at the quotes on which there is 0 or only 1-2 replies to see if it is pending.

    As this group increases in use, it will be very difficult to see each quote every day for the moderator as well..

    So I propose that there can be one field which has some relevant values such as

    ..Information Required
    ..Infesiable to work
    ..Solved and Closed
    ..Solved but suggestions welcome


    If someone needs some more thought process on this, i am ready for this and i can give some of my experience.. being a Software Testing person myself...

    Best of luck for the Group once again....



    I saw the data, It is too complicated...
    not the fact that it cannot be done,
    but there should be some underlying logic to convert it.

    I tried hard to get a generalized logic, but culdn't.

    If you can give a logic, as to wchich rows should be split in which way, (say if contains some text, row # etc.) a script can be written.

    Is this kind of data is required quite regularly? then we can work out some common logic.

    I will certainly try to help you, but will be nice if you can give some more logic...

    hope we can come up with something.

    Sorry for not being able to fulfil your request...

    May be some experts here can still take a look and give it a try..

    hi fireandflame

    This is the code that will work exactly as per your specifications...

    Public Sub true_find()

    Dim c As Range
    Dim r As Integer

    r = 0
    For Each c In Selection
    If c.Value = "True" Then
    r = r + 1
    If r = 1 Then first_true = c.Offset(-1, 0).Value

    last_true = c.Offset(-1, 0).Value

    End If

    Range("d11").Value = first_true
    Range("d12").Value = last_true

    End Sub


    I have tested if for all the things i can think of. You need to select the range where you want to find trues and then click on the button that will do the trick for you.

    Bnix's approach is also correct, but his code is actually changing the values of TRUE and FALSE and not putting value in D11 and D12. (I may be wrong!!!)

    IF you want to run this on a specific range, just put

    in the begining of the code

    Also attached the sheet...
    hope this helps...

    I think, Macro will not even be required.
    You can use Match(), Search(), Indirect() or offset() functions to achieve this.

    But your example is confusion to solve.

    let me see if i can get it.

    Say Row A has True and false, randomly
    10 and 15 times respectively.

    Now what you exactly want?

    The number 10 and 15 in front of true and false? (This can be done by countif)

    The Address of 10th True and 15th False?

    or some value infront of 10th True and 15th False?

    Please update

    Can you give some more data?

    By what logic do you want VBA to perform the text to column?

    One more suggestion...
    It looks that most of the data has space as separator.

    You can use space as dilimiter in the text to column.

    It will be nice if you can upload the sheet with dummy data..


    Hi Sucheng,

    Really interesting thought.

    This can be done by writing a module and
    in it creating a function.

    This is very useful when you want to simplify long calculations

    If you need any further help, please update accordingly

    If your data is in Database, method mentioned by Bruce is excellent, but, beware that you are playing with fire!!!

    Do not add any of your details in the sheet in which you are importing. If you do so, whenever you refresh the data, whatever you manually enter will be messed up.

    Instead enter your details on a different sheet.

    I have suffered a lot due to this long back, so thought will be better to give you some update.

    But certainly not to scare you.

    Best of luck:guitar:

    Hi gadib,

    In excel cell, +5 will not stand individually, and will be converted to 5.
    The only way is to have a quote mark (')
    before +5.
    But the problem with is that, it will not longer be a number :(

    I suggest to get the things imported as
    it is coming now, and if + sign is significant to you even if it is not a number, concatinate that

    If you want to use this concatinated number into calculations, first multiply it by 1 and then do the calculations.

    a1 = 1
    b1 = 3
    c1 = (="+"&a2) = +3

    Now if you still want to add it to a number in say a1
    d1 = (c1*1)+1 = 3+1 = 4

    Note that, if you type c1+a1, it will give you 1 and not 4

    Hope this solves your problem

    May be you can post the script that you have into PERSONAL.XLS which opens by
    default and is hidden everytime you open excel.

    So you need not open your file :)

    Does this work?

    I will also be interested in reading the script you have written, if you don't mind putting it on this forum:thumbcoo:

    I have uploaded one sheet with the example you gave (as i could understand)

    I used formula

    See if this helps.

    Please update if your problem is different than this

    That is really good Roy!!! :thumbup:

    Is there any place where we can get more such built in applications with code?

    :eureka: it will be really nice if you can give more such examples or site where we can find them :bouncing:

    As mentioned by MarkNethercott, even i am a bit confured

    But, just analysing from what you are saying... i create a scenario.

    1. Cell a2 contains sales figure
    2. Cell B2 will contain Discount figure, based on sale.
    3. From 0 to 5000 sales disc. = 0% on total sales
    4. From 5001 to 50000 sales disc. = 2% on total sales
    5. From 50001 onwards sales disc. = 5% on total sales

    so b2 will have formula

    Hope this helps.