• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Search results

  1. C

    Loop through fixed number of rows

    Posted this by mistake in the excel formula forum. I do need a vba solution as in J9:J38 there is a data validation list that contains "Cash Withdrawal" as part of the list options I'm looking for code that will loop though O9:O38 and if on any of those rows the first 4 characters = "Cash"...
  2. C

    Loop through fixed number of rows

    I'm looking for code that will loop though O9:O38 and if on any of those rows the first 4 characters = "Cash" then "Cash Withdrawal" will be put in the adjacent J9:J38 cell. So if O15 = "Cash Deposit abcd " then "Cash Withdrawal" will be entered into J15 ▬▬▬▬▬▬▬▬▬ Mod edit : thread closed as...
  3. C

    Formula gives circular error

    I have sales revenues which have a commission % deducted to leave an net amount that repays an investment. The total investment to be repaid is $5000. Until 50% of this is repaid ie $2500 the commission to be charged should be 5%. Once $2500 has been repaid any further sales amounts should have...
  4. C

    Code not saving to sharepoint

    Hi All I have the following code which creates a backup of the open workbook - this works when on a local machine. I now have the file on a sharepoint drive and it's not working. I've already changed the \ to / in the path's but still not registering. Is it do to with being a URL? Any help...
  5. C

    Filepath when Saving to Sharepoint folder

    I had code to correctly save to a local/network folder but its now not working when the file is on sharepoint. 'the following function will get the path only (i.e. the folder) from the file's full path: Function FilePath(strPath As String) As String FilePath = Left$(strPath...
  6. C

    Can this macro be speeded up

    I am using the code below to hide rows that are not relevant to print and/or copy just relevant data to another sheet. The code below looks at all the rows in Col A and if they =0 it hides the rows. This seems to be taking a lot longer than it initially did. It looks through approx. 2000 rows...
  7. C

    Select TimeValue from cell

    I have a cell that shows the minutes I require (although formatted hh:mm:ss - still seems to include date) Sheet6.Range("TimeInterval") How can I use this in the code below. dTime = Now + TimeValue("00:05:00") Thanks DK
  8. C

    Cell to show defined name not value.

    I have cell A1. This is a defined name "GLShoot" and returns a value on 10 in A1. In B1 I want to show the defined name - "GLShoot". Is this possible? Thanks DK
  9. C

    Add new cell value to list in another sheet

    I have a list of Grades in Sheet"Setup". This list will be added to by user. They will select which SCH it belongs too. I need a macro that will take the new entry in Sheet"Setup") and add to the correct column in Sheet"List2". Ideally the lists should be sorted. Thanks DK
  10. C

    Macro to create defined names

    I want to have a macro that will create defined names from a list As the list is added to it needs to run again and not create old names again or create an error message and just add the names that don't yet exist. COL A COL B Job1 defined name in Col B - name needs to be "GL" & Job1 &...
  11. C

    Complex Data Validation?

    I want to set entry into a cell based on any of the following conditions: 1. If A1 = "F","O","E" then no entry allowed 2. If A1 = "I" or "S1" then entry allowed 3. If A1 = "B" then option Defined Name "List1" I am looking to enter this as a custom formula is DV setting in cell. Thanks DK
  12. C

    How can I add more criteria to this code

    This code works well. I need to get it to work with more criteria. 1. - I would like to be able to have Criteria1: = "I" or "S" As a new piece of code I need to 2. - I would like to be able to have Criteria = "I" or "S" for Range "I" but also Criteria = "U" for Sh.Range "V". Sub APExtract()...
  13. C

    Msg - Function in formula causes result to change when excel calculates

    I thought this formula was ok but the result keeps changing. When excel calculates its ok and then I go and do something and suddenly its not. When I went to evaluate formula if gives the text above. Any ideas? Thanks DK...
  14. C

    IF AND OR Statement

    I just can't get this to be excepted. Brain blank If I19 = "E" and V19 = "U" or "P" then U19 otherwise 0 =IF(AND($I19="E",IF(OR($V19="U",$V19="P"),$U19,0) Thanks DK
  15. C

    Formula seems to work and then get #Value error

    This formula seems to work ok and then suddenly kicks up #value error. As this is a key formula in my many sheets I would appreciate any help. Thanks DK...
  16. C

    Need to add a criteria to a loop formula[SOLVED]

    This code currently runs fine. It goes through many sheets. Many of these sheets might not have any information to extract. In order to exclude them and speed process up I want the sheet to be skipped if DH7 in each sheet is not greater than 0. Thanks DK. Sub FloatExtract() Dim Sh As...
  17. C

    Too Many If Statements in formula?

    I have the code below - which is not working. I think I have too many If statements. This formula is in V40. I also need to add in one further condition...
  18. C

    Faster code than looping code

    I have this code that does the job but can be slow as it's looping. Goes through 4000 lines. Is there a faster solution - sure there is? Sub HideBudgetRows() Dim cel As Range Dim rng As Range Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Set rng =...
  19. C

    Use list of worksheet names to set UserInterfaceOnly on Opening

    The code below sets the UserInterfaceOnly for Sheet13 when the workbook open. I have many many sheets that need to be set - some with different passwords. Can I use a listing of worksheet names somewhere that can then be used in an array or something rather than having to type them...
  20. C

    Why when line gets inserted does it then get hidden

    I have the code below to insert lines on a sheet. It works correctly and inserts the lines BUT then the lines get hidden. I can't understand what is causing this. There is no event code in the worksheet module. DK. Sub InsertSch1NotesLine() Dim myRow As Long Application.ScreenUpdating =...
  21. C

    Go to selected sheet on open still flashes last sheet from close?

    When the workbook opens I want it to go to the selected sheet which I am using as a splash screen based on a condition. I've put this code in "This Workbook" to action when WB opens. However it doesn't go cleanly to this screen. It still flashes up what I think was the last sheet when workbook...
  22. C

    Delete User selected rows only if Col B ="1"

    I need a macro that will delete rows based on the below criteria: The user can select a cell on a row or rows that they want to delete (therefore maybe multiple and non continuous rows) The row can only be deleted if the value on the row in col B = "1". Need some error handling if any of the...
  23. C

    Sum cells only to a Maximum value

    I should know this but just can't get my head around. Any help would be appreciated. Attached file shows simple layout of whats needed.
  24. C

    Set Password using value of named range

    How can I use the value of a cell to be the password name in my code. If the value in A1 is "Password" and this is named as "PW1" how can this be placed in the code Sheet1.Protect Password = "PW1" Thank DK
  25. C

    Give array a Name and use in code

    I have the following line of code that selects the 2 sheets. [For Each sh In Sheets(Array("Sch6", "Sch7"))] Can I create a list of sheets and name this range and then just have the one name in the above formula. Thanks DK
Back
Top