Posts by Excel Noob

I have realized a simple work around regarding ask #2 and therefore do not require it any longer.

I only would like to find a solution to points 1 & 3.

Hi Pecoflyer,

Thank you for the reply. I ended up using a couple of different pivot tables and then used a formula to reference the information I needed. I was on deadline so I just had to get down and dirty to get it done. Thank you for your interest in this though. Cheers!

Hi,

I have a large spreadsheet that contains customer information in each row. There are more than 500 columns to the right that contain part numbers and descriptions in the top row. I need to be able to look at a row and return some customer data such as customer name currently contained in A2 and store number in A3 along with every column in that row that contains a numerical value from column AD on and it's associated column title contained in AD1 and on respectfully.

I attempted to use a pivot table but it's just too unwieldy as it shows everything even if it's blank. I just want to show the columns that have a value in them.

Not sure this is even possible. Any suggestions?

Hello All,

I have a Macro that is largely doing what I need, but I would like to make the following changes to it.

1. I would like the PDF to be saved in the same location as the Excel file it was created from. Currently it is asking me to select a location.
2. I want to have it create a PDF of either the active worksheet or both the active and second worksheet based on the value in one specific cell. Currently it is only converting the active worksheet with no reference to any cell.
   • If cell I16 equals either "New Install" or "Install" then the active sheet is converted along with worksheet 2 which is titled "Install Travel Lot" and emailed. The current macro names the PDF as the active worksheet which will always be worksheet 1 as that's where I have placed my Submit Button. I would like this to remain in place regardless of whether it's converting worksheet 2 or not.
   • If cell I16 equals anything else, including remaining blank then only the active worksheet is converted and emailed. I16 currently contains a Data Validation List just in case that makes any difference
3. The PDF is being named after the active worksheet. I would like the PDF to be named after the file name instead

Here is the current macro I have. It is fully functional with the exception of my wish list above. Thank you in advance for any help I may receive on this!

Sub Saveaspdfandsend()
Dim xSht As Worksheet
Dim xFileDlg As FileDialog
Dim xFolder As String
Dim xYesorNo As Integer
Dim xOutlookObj As Object
Dim xEmailObj As Object
Dim xUsedRng As Range
Dim xStr As String

Set xSht = ActiveSheet
Set xFileDlg = Application.FileDialog(msoFileDialogFolderPicker)

If xFileDlg.Show = True Then
xFolder = xFileDlg.SelectedItems(1)
Else
MsgBox "You must specify a folder to save the PDF into." & vbCrLf & vbCrLf & "Press OK to exit this macro.", vbCritical, "Must Specify Destination Folder"
Exit Sub
End If
xFolder = xFolder + "\" + xSht.Name + "-" + xStr + ".pdf"

'Check if file already exist
If Len(Dir(xFolder)) > 0 Then
xYesorNo = MsgBox(xFolder & " already exists." & vbCrLf & vbCrLf & "Do you want to overwrite it?", _
vbYesNo + vbQuestion, "File Exists")
On Error Resume Next
If xYesorNo = vbYes Then
Kill xFolder
Else
MsgBox "if you don't overwrite the existing PDF, I can't continue." _
& vbCrLf & vbCrLf & "Press OK to exit this macro.", vbCritical, "Exiting Macro"
Exit Sub
End If
If Err.Number <> 0 Then
MsgBox "Unable to delete existing file. Please make sure the file is not open or write protected." _
& vbCrLf & vbCrLf & "Press OK to exit this macro.", vbCritical, "Unable to Delete File"
Exit Sub
End If
End If

Set xUsedRng = xSht.UsedRange
If Application.WorksheetFunction.CountA(xUsedRng.Cells) <> 0 Then
'Save as PDF file
xSht.ExportAsFixedFormat Type:=xlTypePDF, Filename:=xFolder, Quality:=xlQualityStandard

'Create Outlook email
Set xOutlookObj = CreateObject("Outlook.Application")
Set xEmailObj = xOutlookObj.CreateItem(0)
With xEmailObj
.Display
.To = ("[email protected]")
.Subject = xSht.Name + "-" + xStr + ".pdf"
.Attachments.Add xFolder
If DisplayEmail = False Then
'.Send
End If
End With
Else

Hi norie & Carim

Thank you for your assistance. That solved the problem. Much appreciated!

The data is being extracted as a xls file and the the format is the same as you see in the workbook I've attached. When I look at the raw file and right click format it shows as General.

Hi norie,

Thank you. I too have narrowed it down to the date format. The issue I've seen with column F is that neither I or my colleague are able to change the format of the column by right clicking. No matter what format I chose it doesn't change the data in column F. I have to double click on a cell before I can change the content of that cell. I thought if you set the date so it has the * in front of it the date should automatically be changed in each region?

This is meant to be a workbook where you can run reports from sources such as LinkedIn and paste them into the data sheets so the Summary page can gather the required data. I need to be able to make this as easy to use as possible so people in the UK and EU won't have to worry about formatting.

Hi,

I'm looking for some help on, what to me is a strange error. I have created this workbook utilizing a sumproduct array on the Summary page. The formula works perfectly on my workbook but if I send it to a coworker all of the formulas on the Summary page come back with a value error. I have created the workbook in 2013 version and my coworker is opening it in 2016. There are no external data references, all of the data is contained within this workbook on different tabs. I'm in North America and my coworker is in Europe, I doubt this makes any difference though.

Here's where is gets weird. If my coworker sends me back the copy that they saved, the error is present. If I double click on any of the errors on the Summary page and then simply press enter the error goes away. If I go to column F on the Update Engagement tab and either double click on any date, or just click on the filter for column F and then Okay, the error goes away. One thing I do notice is that if I double click on the date in column F it eliminates the leading zero. If my coworker tries any of these steps the error does not resolve.

I'm sure it's probably something simple but it has me stumped. Thank you in advance for taking the time to look at this.

Hi Roy,

Thanks I posted that before I saw your code. I tried it and what it seems to be doing is Auto Filtering the Prospects Sheet, hiding anything that isn't closed. If I have to use Filtering then as you said there's no need for a second sheet or VBA.

There's a couple of things I want to clarify.

1. Rows 1 through 9 are hidden. They contain Data Validation lists.

2. Header values are in A10 and across. All data that needs to be assessed reside in rows 11+ starting in column A.

3. It is a basic Excel sheet, not a table.

Hi Roy,

Thank you for the prompt reply. I understand that filtering is the simplest way to do this but I'd really like to be able to keep open prospects on a separate sheet from closed items if possible. I really don't want to have to keep manipulating the sheet to separate those values.

Hi,

I have a working code (for the most part).

I have 2 issues with this code which I can't figure out how to get past.

Purpose of VBA

Workbook contains 2 sheets, the first is named Prospects the second is named Closed

When the command button is clicked, the entire Prospects Sheet should be scanned for the value of "Closed" in column P.

Any row that has "Closed" in column P should be moved into the first available empty row(s) in the Closed Sheet

Any row that is removed from the Prospect Sheet needs to be deleted and shifted up so that there are no empty rows between data and all formatting remains

Issue 1

When the "Closed" row is moved to the Closed Sheet I lose the formatting in that row on the Prospects Sheet. I have Data Validation set in certain columns on the Prospects Sheet and that's gone once the VBA runs.

Issue 2

The code seems to only work for one row at a time. I'd like the code to grab all of the rows that are marked as "Closed" in column P on the Prospects Sheet, following the same criteria as explained above.

Thank you in advance,

Robert

Private Sub CommandButton1_Click()

Dim Check As Range, r As Long, lastrow2 As Long, LastRow As Long

Application.ScreenUpdating = False

LastRow = Worksheets("Prospects").UsedRange.Rows.Count

For r = LastRow To 2 Step -1
    If Worksheets("Prospects").Range("p" & r).Value = "Closed" Then
        Worksheets("Prospects").Rows(r).Cut Destination:=Worksheets("Closed").Range("A" & Rows.Count).End(xlUp)(2)
        
    End If
Next r

Application.ScreenUpdating = True

End Sub

Re: Calculate overtime based on 8 hour day / 44 hour week

Hi Infomage,

That works perfectly. It's actually really close to an iteration of the formula that I attempted as well. Thank you so much for your efforts and patience with this. I know I kind of led us down the wrong path for a bit. Once again my apologies for that. Members like you are what makes this site so valuable. Thank you again and I hope that you have a great holiday season!!

Re: Calculate overtime based on 8 hour day / 44 hour week

Hi Infomage,

Hope you feel better.

Sorry for the late response. Between work and the holiday season things have been crazy.

This seemed to be far more complex than it should be so I called the Employment Standards agency for this area and have confirmed the following.

Overtime is to be paid for hours worked daily in excess of 8 hours in a day or 44 hours in a week, whichever is greater.

This means if the tech worked 10 hours per day for 5 days they would get 10 hours of OT. 2 hours per day x 5 days is 10 hours of OT. 50 hours for the week subtract 44 hours is 6 hours of OT. 10 hours is greater than 6 so the tech will get 10 hours of OT.

Conversely if the tech worked 9 hours a day for 5 days and then 9 additional hours on Saturday they would get 10 hours of OT. Daily OT would only be 6 hours in total (1 hour per day x 6 days) where as the weekly total would equal 10 hours. 9 x 6=54 - 44 =10 hours OT.

I hope this clarifies it. Sorry for the confusion on my part as well as the extra work.

Re: Calculate overtime based on 8 hour day / 44 hour week

Infomage,

Thanks it works almost perfectly.

In testing I found one small issue. If the tech works 8.25 hours on one day he'll get .25 hours of OT which is correct. If his balance for the week ends up being 44.25 hours the sheet is calculating that he will get paid an additional .25 hours while he was already paid the .25 hours on the day that he worked 8.25. The formula needs to look at the daily amounts paid to ensure that it's not double paying. I've attached an example.forum.ozgrid.com/index.php?attachment/70907/

Re: Calculate overtime based on 8 hour day / 44 hour week

Quote from Infomage;782494

Let's make sure we're clear:

* Hours >8 in a single day to count as overtime for that day
* Hours >44 in any one week to count as overtime

So, does that mean that someone who works 10 hours on Monday then 6 hours per day for the remainder of the week would have a total of 34 hours, 2 of which are overtime?

Hello Infomage,

Yes that is correct. Overtime is earned after 8 hours on any single day as well as when you surpass 44 hours total in one week.

Hello,

I have a working time sheet that we have been using for years and it functions very well. It is currently based on a 44 hour work week. I need to alter it so that it also takes into account the amount of hours worked in a day. It needs to calculate overtime after 8 hours in a day and when the cumulative time worked in the week exceeds 44 hours.

For example,

Joe Technician works 9 hours on Monday and then works 8 hours a day for the rest of the week. While he doesn't break the 44 hour threshold for the week he would still get paid 1 hour OT for the 9 hours he worked on the Monday. Conversely if he worked 10 hours a day for 5 days, by the 5th day he would get 4 hours of regular time and the remaining 6 would be paid as OT since he crossed the 44 hour threshold after 4 hours worked on the 5th day.

I've included examples in the worksheet that shows how the current formula works. I would like to keep the current functionality and add in the 8 hour calculation.

The formulas that I need to alter are located in cells E22:K22.

Thank you in advance.

forum.ozgrid.com/index.php?attachment/70898/

Re: Alter all formulas based on cell value

I'm hoping somebody has some ideas about this.

If the Indirect function can work in this situation, I'd appreciate some guidance as I'm not sure how to apply it to perform the task I require.

Re: Alter all formulas based on cell value

MrRedli,

Thank you for the response. Reading up on the Indirect function, I'm not sure it's going to give me what I'm looking for. If I'm not mistaken (which I may be) I would have to go and alter every one of my existing formulas to include the indirect function. If this is the case then I really would like to avoid that as there are hundreds of formulas referencing over 100 employee names, so theres no way to quickly alter on a mass level.

Here's an example of the formula.

=SUMIFS('Jan'!$E:$E,'Jan'!$B:$B,"*Employee's Name*", 'Jan'!$D:$D,"<30000000")

I'm hoping that there's a way to use the Replace function to change the 'Jan' in the formulas based on what's selected in the Data Validation drop down list. If I can't use a drop down list then I would be open to having the user input the name of the month manually. Doing it this way opens the door to errors if the month isn't typed in correctly.

If this can be done via the Indirect function, I'd appreciate some guidance on how to make it work.

Hello,

I'm trying to find a way to alter or replace a text word (Sheet Name) contained in formulas throughout an entire worksheet.

The workbook will contain multiple sheets. They are as follows; "Year to Date", "Period", "Jan", "Feb", "Mar" and so on for the rest of the year.

I want to use a Data Validation drop down list, on the "Period" sheet containing all of the calendar months.

I currently have multiple formulas within the "Period" sheet that reference one of the month sheets so that it can pull the data for that month from that sheet. Instead of having multiple "Period" sheets, I'd like to use a cell, lets say A1 for argument sake to use a Data Validation drop down list to pick which month I would like the "Period" sheet to reference.

If the current formulas on the "Period" sheet are set to reference the "Jan" sheet, I would like the formula or VBA to find all instances of "Jan" in the formulas and change them to the month selected in the A1 drop down list.

If "Feb" is chosen in A1, all instances of "Jan" in the formulas on that sheet would be replaced with "Feb" so all data on that sheet would reflect the period associated with February.

I hope that makes sense.

Thanks in advance.

Re: Macro protection error 2003

holycow,

Thanks.

I saw an example of that in a related thread where it would also add the password back into it as well. I'll go that way if I have to but I probably have 40 versions of this code in this workbook that I would have to go back and alter. I'd like to avoid that if there's any way possible.