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.
http://www.cric8.com/livescore…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..)
:thumbcoo:

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
:thumbcoo:

Following is the code that you want...

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

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

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

Next
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.

Hi,

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

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

etc.

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....

:cheers:

Hi,

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 Bruce,
Can you please post it here, may be in a text file..?

It will be a great help to me and many such users!!!

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
Next

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

range("a3:h3").Select
in the begining of the code

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

Really not clear
(or may be i am not getting it right)

It will be really great if you put a sheet
and clearely post, what cell should be replaced with what value.

Sorry for stretching you, but it is difficult to imagin these things at times :thumbcoo:

You can put the code into worksheet_activate instead of workbook_activate. This will hopefully solve the problem, as it seem from your reply

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

This link on this site may help you
http://www.ozgrid.com/FreeDownloads/AddingCustomMenus.zip

cheers....

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..

:cheers:

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
="+"&a2

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

E.G.
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
=DATE(YEAR(A2),MONTH(A2)+B2,DAY(A2))

See if this helps.

Please update if your problem is different than this
:guitar::flower:

That is really good Roy!!!

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
=IF(A2<=5000,0,IF(A2<=50000,A2*0.02,A2*0.05))

Hope this helps.