Posts by fess

    Yeah the paste to row 16 is just proof of concept. The final output will be to another sheet.


    The CDate does the job. I was having trouble with some results with the dates in Col C being in date/time format but no matter if I changed .NumberFormat or .Format I could not get rid of the time element.


    Your solution certainly helps me get over the immediate hurdle, thanks for your time and effort.

    Been going mad on this one. I am trying to extract rows where the date in Column C is greater than or equal to the last date in the column.


    My LastRow is reporting correctly and the condition for being >= my PeriodStart is true yet the code stops short of the last row. I have probably reached the point where the woods and the trees all look the same and I cannot see it.


    thanks in advance for help.


    Files

    • Book1.xlsm

      (10.16 kB, downloaded 47 times, last: )

    The attached is a crew picker function as part of a larger project. The crews work a fixed 15 day roster and the function is intended to auto populate the active crew depending on date and shift (day or night).


    Works fine except on the first day of the pattern for night shift. My case select is that if n is 0 to 4 and shift is "night" then crew = "B" however it always returns "C".


    I have added a msgbox in there just for fault finding and to confirm the output of n is as expected, the msgbox is not there in the live sheet.


    Thoughts appreciated.

    Pretty sure he is intending it to be a static map for the image and then randomize the data presented when each zone is clicked. This way the student cannot learn the map they have to understand the data being delivered in order to decide which grid to explore next. Mine geology based content.


    I am torn though as to send him to a web dev or SQL / DBA type person. I will spend some time with him to fully understand his needs.

    I need to talk to a web developer. Project is to create a map with different zones that when clicked link to a database file to prompt further work by the user (offline from the web page). I remember years ago at university I did something similar so I am sure this is a reasonably low level project but I would like to talk to someone to confirm this and then look to taking the next steps.


    Anyone know of a web developer I can talk to?


    Thanks in advance.

    Re: Compare dates, match ID and return value


    Thanks Carim. I was always going to use some pivot tables and slicers but was in the mindset that I had to do most of the calculations in the data set and just use the pivots to report. Told you my head was mushy :p I think I spent too long working code out to provide the functionality that I was stuck in 'code mode' when it came to this part of the project.

    Hi Guys,


    My brain has gone mushy and I cannot think of an elegant or robust way of doing this.


    I have created a sheet for pump management and am now working out how to calculate the daily pump volume. This should be easy but I cannot get my head around it.


    All I need to do is compare todays hours for each pump against yesterdays value and return the difference. Pumps may be added / removed from the fleet so I cannot just compare two columns, I do need to match the pumpID as well.


    Sheet attached does work but it is ugly. I would prefer to be able to do it in VBA as the rest of my sheet is procedures and listboxes but a formula is ok if needs be. Any and all help very much appreciated.


    Thanks in advance.

    Files

    • Pumps.xlsx

      (24.61 kB, downloaded 44 times, last: )

    Re: view on open - limit to the A4 form I created


    ok, I got there in the end. I used the view, zoom to selection option and created a macro from that.


    Code
    1. Private Sub Workbook_Open()
    2. Range("D2:R31").Select
    3. ActiveWindow.Zoom = True
    4. Range("D4").Select
    5. End Sub

    Re: view on open - limit to the A4 form I created


    Sorry for the poor explanation. A4 is a paper size. I am designing a project update form in A4 size.


    When I open Sheet1 (the form) I would like the view that the user sees to reflect the A4 form and only that so they are very clear what needs to be completed in order to properly fill out the form. It is just an aesthetic thing rather than one of functionality.

    Hi,


    How do I limit or fix the view of a sheet so that when I open that sheet the only thing I see is the A4 page that I have created. I am creating an A4 project update template and when I open it I just want to see the full page and type into the open areas, I do not need to see the other rows and column of the normal excel view. All I want to see is that A4 sheet.


    Thanks in advance

    Re: Looping through dates then making a selection


    Update: On Error Resume Next within the with statement as sometimes there was a date missing when a machine was not used and this caused an error when the NextRow was looking for the sequential date.

    Re: Looping through dates then making a selection


    ok, I seem to have had a win. I was working on this for some time yesterday, slept on it, still struggled so posted this plea for help. Seems that sleep does indeed refresh the mind as I have managed to get it to work.


    I was not looping the correct date, I had to increase NextDate by itself not the startDate. Then, I added a .select for the NextRow and the after option in the .Find method. Seems to work now. Thanks to those that took time to look and help.


    Hi,


    I have a sheet with in excess of 600,000 rows of data pulled from a database. The data is in date order. I am searching for the first instance of a status 'LU Loading' for each date, ignoring all other instances of 'LU Loading'. My code works for the first instance but does not advance through to the next date, it always goes to the very first instance of 'LU Loading'


    I want it to look for 1/1/2017 'LU Loading', copy and paste that row THEN ignore everything until 2/1/2017, find 'LU Loading', copy paste that row then move to 3/1/2017.


    Dates in Col A Status in Col J


    in my debug.print I can see that the dates are advancing as I want but not the selection. How do I get LoadStartRow to begin its search from the NextRow value?


    Cut down sheet and code attached.


    Thanks in advance.


    Files

    • Loading.xlsm

      (25.81 kB, downloaded 48 times, last: )

    Re: Refresh Pivot VBA - Protected Worksheets


    Well it won't if you have this line of code like this.


    Code
    1. For Each ws In ActiveWorkbook.Worksheets
    2. If ws.PivotTables.Count < 0 Then ws.Protect Password:="password"
    3. Next ws


    You are telling it that if the pivot table count is LESS than 0, lock it. Where you have a pivot table the count will be MORE than 0. It needs to be >0

    Re: Hiding Rows and Columns with If


    I would hide rather than delete as I assume you are going to want to use that if statement again based on some weekly / monthly KPI.


    Here is the base code that will get you going


    Code
    1. Sub HideRow()
    2. If Range("A5").Value = "-" Then
    3. Rows("5:5").Select
    4. Selection.EntireRow.Hidden = True
    5. End If
    6. End Sub

    Re: VBA Excel -- Split by length and insert rest text into following row.


    I can't help but think this is a case where you need to explain the problem very clearly and preferably provide an example of the sheet you are going to manipulate.


    The solution Bryce gave you will work however I feel it may well cause you more problems down the track, depending on your complete needs. I certainly see issues if you have other columns. I have put some names in the sheet that Bryce created to show the problem you may be creating. You will end up with twice as many company names, there could well be lots of apparent duplications and some will look right when in fact they are not real companies, just a part of another company name.


    The first 2 names are fictitious, the others are real companies. See the issue? May or may not be a problem but once you play a macro you cannot undo so what I am saying is beware of unintended consequences.