# Posts by smuzoen

• ## adding a percentage in a progress bar without losing visuality

Re: adding a percentage in a progress bar without losing visuality

Not sure what you mean - if it is greater than 100%? If you put in 50000 and 25000 if will go to 75%, 50000 and 50000 it will go to 100% - problem if the total is > 100% - is that what you refer to?

• ## RankIF - what formula do i need

Re: RankIF - what formula do i need

Just add in an "AND" condition

=IF(AND(\$G6="Long",\$F6>0),(SUMPRODUCT((\$G\$6:\$G\$25="Long")*(\$F6<\$F\$6:\$F\$25))+1),"") - copy down
=IF(AND(\$G6="Short",\$F6>0),(SUMPRODUCT((\$G\$6:\$G\$25="Short")*(\$F6<\$F\$6:\$F\$25))+1),"") - copy down

• ## adding a percentage in a progress bar without losing visuality

Re: adding a percentage in a progress bar without losing visuality

Try the attached workbook - the bar will change with a change in percentage. Depending on your Excel version with the gradient fill at 100% it will never fill the cell - in earlier versions (2007 and below I think) you need to add in a second conditional format rule to make the entire cell fill when at 100%. I don't use percentages but rather numbers - just format the number as a percentage

• ## Get Elements by ID Keeps varying.

Re: Get Elements by ID Keeps varying.

If the page has been coded correctly then strict HTML/XHTML would dictate there is only one element with that id - so John_w suggestion should get you the id you require because no other element on the page "should" have the same "id". You can only use an id once in HTML or XHTML - you can use the same class as many times as you want but id is a different story. That being said though it will only cause a failure in validation and problems with associated javascript not necessarily prevent the page from resolving correctly.

• ## RankIF - what formula do i need

Re: RankIF - what formula do i need

I don't think it is because there is nothing to make numbers that are equal have a different ranking - there would need to be some other factor to decide the ranking. Perhaps someone else has an idea/solution but at this point in time I cannot see a way to differentiate between two numbers that are equal.

• ## RankIF - what formula do i need

Re: RankIF - what formula do i need

Problem is that how do you decide which should be ranked higher/lower when the number is the same? First occurrence gets the higher ranking? Basically 10 is the same as 10 obviously, which of the 10's should be ranked higher?

• ## RankIF - what formula do i need

Re: RankIF - what formula do i need

You could use Sumproduct

=IF(\$G6="Long",(SUMPRODUCT((\$G\$6:\$G\$25="Long")*(\$F6<\$F\$6:\$F\$25))+1),"") and copy down for Long Ranking
=IF(\$G6="Short",(SUMPRODUCT((\$G\$6:\$G\$25="Short")*(\$F6<\$F\$6:\$F\$25))+1),"") and copy down for Short Ranking

See attached workbook

• ## adding a percentage in a progress bar without losing visuality

Re: adding a percentage in a progress bar without losing visuality

If you mean to show the progress bar (conditional formatting) with the percentage inside the progress bar then just divide the value in B19 by 100000 - make the formula in J4

=SUM(B19)/K4

Is that what you mean or have I misunderstood your question

• ## Macro to run IF the current time is before 17:00 only

Re: Macro to run IF the current time is before 17:00 only

You could use Time

Code
``````Dim endTime As Date
endTime = "17:00"
If Time < endTime Then
......send email code
Else
MsgBox "Too late to send emails", vbCritical
End If``````
• ## [SOLVED] VBA: Rename Worksheet based on cell value in another Workbook

Re: VBA: Rename Worksheet based on cell value in another Workbook

Try

Reading your code I assume that you want the sheet called DATA copied from the Target file to Master dB.xlsm then rename the sheet you copied called DATA to the value in C6 in the Target File from the sheet called inputs general.
Try as good practice to reference your sheets - there would be a conflict here that you have an open workbook (Targetfile) as the active file so when you try to rename the sheet DATA it would try to rename the workbook you opened. It makes it easier to read and maintain the code.
I also changed the file to open to allow you to select both xls and xlsx files as well (and xlsm) - you can change that back if you want to only select xls files

• ## How to Disable / Enable specific columns based on the input from a cell

Re: How to Disable / Enable specific columns based on the input from a cell

With respect to adding the hours you could use Sumproduct

=SUMPRODUCT(--(\$B\$2:\$B\$16=\$B19)*(\$C\$2:\$C\$16))+(SUMPRODUCT(--(\$B\$2:\$B\$16=\$B19)*(\$D\$2:\$D\$16))/60)

Have a look at the attached workbook

Try and place a link for a sample workbook - you could use Dropbox or Mediafire - I am not sure about the first post part of your question - with a sample workbook it would be easier to see how your data is set out

PS: The sum of hours and minutes is in decimal e.g. 28.75 means 28 and 3/4 hours = 28hours 45 mins

• ## Copy page margins from one workbook to another

Re: Copy page margins from one workbook to another

Please use code tags when posting code to the forum - please read our forum rules http://www.ozgrid.com/forum/announcement.php?f=8 - Forum Rule 1

I have added them for you this time however please make sure that you include code tags in all your subsequent posts.

• ## Delete First Row & Column in multiple CSV

Re: Delete First Row &amp; Column in multiple CSV

You could try this method - this code will allow you to browse to the folder with CSV files - it will then iterate through all the csv files and remove row 1 and column 1. BEFORE running this code BACKUP all the files - this code will alter the csv files so make sure you have a BACKUP. I cannot emphasise this enough.

See attached workbook.
Option 2 - You could open the CSV for binary access and then manipulate the resultant array - this is more complicated - sample code:

However option 1 I think would be easier

• ## vba code to populate combobox from another workbooks (excel 2007)

Re: vba code to populate combobox from another workbooks (excel 2007)

Sorry - worded my response badly - obviously need to be opened to get data - doesn't detract from fact that still cool method

• ## Trying to modify this 'text to rows' code to only use semicolons as delimiters

Re: Trying to modify this 'text to rows' code to only use semicolons as delimiters

Can you supply a sample workbook with no sensitive data - what you are asking sounds very straight forward but I am not sure how your data is set out. As a really simple example

See attached workbook

• ## Filter based on Input

Re: Filter based on Input

Without a sample workbook with dummy data, all sensitive data removed it is very difficult to help you - if you select "Go Advanced" when you post you can attach a sample workbook.

• ## Move formulas from one column to another column

Re: Move formulas from one column to another column

Can you upload a sample workbook to Dropbox/Mediafire and supply a link - without workbook very difficult to help and clarify what you require

• ## vba code to populate combobox from another workbooks (excel 2007)

Re: vba code to populate combobox from another workbooks (excel 2007)

snb - very cool - the workbooks don't even need to be open for your method to work - very succinct.

• ## Copy page margins from one workbook to another

Re: Copy page margins from one workbook to another

You could set up a template sheet in Master.xlsm with no data set up how you want - then copy this to Numbers.xlsm, delete sheet1 in Numbers.xlsm and rename the copied sheet to sheet1 - this could be done via VBA - the Page Object has an enormous amount of properties (try recording a macro and you will see what I mean) - I think this would be the easiest way. There are some macros/add ins around that may be looking for what you want. What specifically do you want to replicate wrt page margins?

• ## vba code to populate combobox from another workbooks (excel 2007)

Re: vba code to populate combobox from another workbooks (excel 2007)

What I assume you have is a combobox on a sheet in Main.xlsm - you want to populate it with the data from name.xlsx Range B2:B6 - and then also another combobox with data from city.xlsx. I am just going to show you how to do the first part - populate a combobox on Main.xlsm with data from name.xlsx. I assume you want this done when Main.xlsm is opened as you have your sample code in the workbook open event. This code will open name.xlsx and get the data from the range, populate the combobox and then close name.xlsx. You will need to follow the example to do the same with the other combobox.

Attached is a sample workbook - code is in "This Workbook".

It is faster to use the List property rather than iterating through the array and adding each item one at a time with additem property however the data range is pretty small anyway