Posts by fess

fess · Apr 20th 2018

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.

fess · Apr 20th 2018

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.

Code

Sub FillChart()
Dim LastRow As Long, FirstRow As Long
Dim Period As String, PeriodStart As String
Dim x As Long
Dim CopyFrom As Range, Dest As Range, pStart As Range, pEnd As Range
Dim UsedRng As Range
LastRow = ActiveSheet.Range("C" & Rows.Count).End(xlUp).Row
Set CopyFrom = Range("A3:" & "J" & LastRow)
ActiveSheet.Columns.AutoFit
CopyFrom.Sort _
key1:=Range("C3"), order1:=xlAscending
FirstRow = CopyFrom.Row
'PeriodStart = ActiveSheet.Range("C" & LastRow).Value - 7
PeriodStart = ActiveSheet.Range("T4").Value - 7
For x = FirstRow To LastRow
If Range("C" & x).Value >= PeriodStart Then
ActiveSheet.Rows(x).EntireRow.Copy
Range("A16").PasteSpecial
MsgBox x
End If
Next
End Sub

Display More

fess · Apr 17th 2018

Hi pike,

Thank you very much for the response. That did indeed fix the issue. My apologies for not responding earlier, that is not how I like to do things I got totally consumed by other stuff. Thanks again

fess · Apr 3rd 2018

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.

fess · Oct 28th 2017

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.

fess · Oct 27th 2017

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.

fess · Jul 5th 2017

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.

fess · Jul 5th 2017

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.

fess · May 5th 2017

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

Private Sub Workbook_Open()
Range("D2:R31").Select
ActiveWindow.Zoom = True
Range("D4").Select
End Sub

fess · May 4th 2017

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.

fess · May 4th 2017

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

fess · Apr 30th 2017

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.

fess · Apr 30th 2017

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.

Code

Sub FindFirst()
Dim sht1 As Worksheet
Dim wsResults As Worksheet
Dim curLastRowData As Long
Dim NextRow As Range
Dim NextDate As Date
Dim d As Date
Dim startDate As Date, endDate As Date
Dim i As Integer
startDate = DateValue("1/1/2017")
endDate = DateValue("4/26/2017")
Set sht1 = Sheet1
Set wsResults = Sheet2
NextDate = startDate
Set NextRow = Range("A1")
With sht1
For d = startDate To endDate
'set the next search row based on advancing date
Set NextRow = sht1.Range("A:A").Find(what:=CDate(NextDate))
'find first instance of LU Loading and copy row
NextRow.Select
Set LoadStartRow = .Cells.Find(what:="LU Loading", after:=NextRow)
LoadStartRow.EntireRow.Copy
'Find last empty row in results worksheet and paste results
curLastRowData = wsResults.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).row
wsResults.Range("A" & curLastRowData).PasteSpecial xlPasteValuesAndNumberFormats
'add 1 to the date to advance the loop
NextDate = DateAdd("d", 1, NextDate)
Next
End With
End Sub

Display More

fess · Apr 29th 2017

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.

Code

Sub FindFirst()
Dim sht1 As Worksheet
'Dim FoundYou As Range
Dim wsResults As Worksheet
Dim curLastRowData As Long
Dim NextRow As Range
Dim NextDate As Date
Dim d As Date
Dim startDate As Date, endDate As Date
Dim i As Integer
startDate = DateValue("1/1/2017")
endDate = DateValue("4/26/2017")
Set sht1 = Sheet1
Set wsResults = Sheet2
NextDate = startDate
With sht1
For d = startDate To endDate
Debug.Print "Iteration " & i & ":" & vbTab & d
i = i + 1
Set NextRow = sht1.Range("A:A").Find(what:=CDate(NextDate))
Debug.Print NextRow
'find first instance of LU Loading and copy row
Set LoadStartRow = .Cells.Find(what:="LU Loading")
LoadStartRow.EntireRow.Copy
'Find last empty row in results worksheet and paste results
curLastRowData = wsResults.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).row
wsResults.Range("A" & curLastRowData).PasteSpecial xlPasteValuesAndNumberFormats
'add 1 to the date to advance the loop
NextDate = DateAdd("d", 1, startDate)
Next
End With
End Sub

Display More

fess · Feb 10th 2017

Re: Refresh Pivot VBA - Protected Worksheets

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

Code

For Each ws In ActiveWorkbook.Worksheets
If ws.PivotTables.Count < 0 Then ws.Protect Password:="password"
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

fess · Feb 10th 2017

Re: VBA Code to Change filter in Pivot Table

Have a look at this link. Seems to be lots of information there that may help you out. Not something I have dabbled in much but I found this today when researching a similar issue.

http://peltiertech.com/referen…ivot-table-ranges-in-vba/

fess · Jan 31st 2017

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

Sub HideRow()
If Range("A5").Value = "-" Then
Rows("5:5").Select
Selection.EntireRow.Hidden = True
End If
End Sub

fess · Jan 20th 2017

Re: Save data in worksheet every hour to CSV/PDF file

A quick search for VBA save to CSV every hour brings up plenty of options to explore, including the one you posted on another site which has provided you with the code. http://stackoverflow.com/quest…m-excel-sheet-to-csv-file What specifically was it about that code that does not work for you, maybe we can help with that?

Cheers

fess · Jan 20th 2017

Re: Excel CLick VBA Image To Load Form And Speak At The Same Time

I see the OP got themselves banned after going on a rant. I guess they should learn to be a tad more patient

fess · Jan 20th 2017

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.

Posts by fess

For loop does not include last row

For loop does not include last row

Select Case returning incorrect result

Select Case returning incorrect result

Anyone know a web developer?

Anyone know a web developer?

Compare dates, match ID and return value

Compare dates, match ID and return value

view on open - limit to the A4 form I created

view on open - limit to the A4 form I created

view on open - limit to the A4 form I created

Looping through dates then making a selection

Looping through dates then making a selection

Looping through dates then making a selection

Refresh Pivot VBA - Protected Worksheets

VBA Code to Change filter in Pivot Table

Hiding Rows and Columns with If

Save data in worksheet every hour to CSV/PDF file

Excel CLick VBA Image To Load Form And Speak At The Same Time

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