Posts by travellerva

Never mind... I'm getting old.

I could have sworn I used to use Shft,Cmd,V for Paste Special, but now I see it is Ctrl,Cmd,V. That works fine

Sorry...

I'm using the latest version of Excel for Mac (16.52) that was auto updated yesterday. Since then the keyboard shortcut for Paste Special (Shft,Cmd,V) does not bring up the option window and pastes formats only. MS has not been helpful. has any one else noticed this?

I have a row of 50 cells in one sheet and want to replicate those formulas in a separate sheet but as a column rather than a row. I'm trying to find a way of setting up = formulas in the second sheet without having to manually enter =E6; =F6; =G6 etc. I've tried copying and moving the row and transposing the copy formulas but no luck. There must be an easy way!!!

Please see attached sample spreadsheet. I'm trying to find a formula that provides a count of the number of times in two columns that the values in corresponding rows are equal. I've tried various versions of COUNTIF without success. I realise this could be done by creating a "shadow" column that flags each row that's equal; then counting that column but I don't want to add a third column - even hidden. I also don't want to do it as a macro. Many thanks for all ideas.
[ATTACH]n1214614[/ATTACH]

Re: Truncate variable in chart title

Quote from Max1616;772296

Try this:

Code

With ActiveChart 
    .HasTitle = True 
    .ChartTitle.Select 
    .ChartTitle.Text = "Last " & Format(Age/ 30, "#,###.0") & " months" 
End With

Many thanks. That works fine. I hadn't realized I could embed a Format method inside a string.

I am trying to truncate a variable in a chart title. See:

With ActiveChart
    .HasTitle = True
    .ChartTitle.Select
    .ChartTitle.Text = "Last " & Age / 30 & " months"
    End With

The variable "Age/30" needs to be truncated to 1 decimal place but I can't figure out how to do that.

Thanks.

Re: Selecting a sheet array in VBA for Excel for Mac

Thought I'd bump this up as I still have the same problem!

Re: Using SUMIF with variable columns

Very comprehensive - thanks again. That will go into my library of tips.

Re: Using SUMIF with variable columns

Perfect! Many thanks. Although I've used the INDEX function in the past, I hadn't realised it would fit this situation.

The attached sheet is an extract from a much larger workbook. The table provides monthly entries for revenue from multiple projects. Project names are in A8:A55 and project type is in B8:B55. Columns D:D through O:O contain revenue entries for each calendar month for each project.

I need to sum the total for a particular month for all T&M projects and all Fixed Fee projects. The month specified would be in B1 and the results are to be place in B2 and B3. In the real workbook, these results will be on a different sheet but i can deal with that variable.forum.ozgrid.com/index.php?attachment/58243/

I realize it would be simple to add a couple of rows to the table and sum the totals for each month; then to pick up the relevant total from that. However, it is not possible to provide the space for such rows (there is much more dynamic stuff going on in the workbook) and I'm looking for a formula for B2 and B3 that would give the totals according to the value in B1. If it helps, the value in B1 could be the alpha equivalent of the month sequence (Jan, Feb etc.) to match the headings in cols D:D through O:O.

Im thinking some form of a SUMIF with INDIRECT to get to the appropriate column but can't figure that out. Any help much appreciated.

Re: Formula for total backlog

Thanks again. I had no cells with either text or #N/A in them but by experimenting with batches of rows, it finally worked ok. Maybe I had finger trouble somewhere.

Either way, your solution works well - thanks

Re: Formula for total backlog

Thanks. I took a look at this but get a #N/A result. I should have mentioned there may be cells with no values in at all. Could that cause an #N/A?

I have a sheet with columns for Project Name (col A:A); Estimated Total Value (col B:B) and YTD Revenue (col C:C).

I'm looking for an array or other formula to give me a total Backlog for all projects. This is the sum total for any project where the Estimated Total Value (B:B) is greater than the YTD Revenue (C:C). In some cases, the value in col. B will be less than the value in col. C - these should be given a zero value, not a negative value.

I know I could just create another column of =IF(Bn<Cn,0,Bn-Cn) and then sum the results but I do not want to have another column, even hidden. What would be a SUMIF or SUMPRODUCT way of doing this?

Thanks

Re: Find earliest date for client

Many thanks, Maqbool. However, that array formula returns the latest date, not the earliest.

But your advice led me to the opposite array formula, SMALL, which gave me what I wanted - thanks!

I have a 2-column table where the first column is a series of dates (A:A) and the second column is a list of clients (B:B). There are multiple entries for each client; each entry with different dates. The table is sorted in alpha order of client but random order of date entries. A sample table is similar to the attached "Date-client" sheet.

I have a separate list of clients (E1:E4), one entry for each client, and need to find the formula for the earliest entry from the A:B table for each client and insert it into the corresponding E:E table entry in the next column (F:F). I do not want to use a pivot table and cannot do repetitive sorts.

It would seem to require a VLOOKUP to find all the instances of each client, then to find the earliest date. I'm struggling with this one. Any help much appreciated.

Re: FIND text in cell only when it is in the leftmost position

I hadn't noticed that part of your suggestion. Perfect! It works well. Thanks for your help

Re: FIND text in cell only when it is in the leftmost position

Thanks. I should have said I tried the wildcard idea. It doesn't work. The FIND still hits any cell that has TOTAL anywhere.

I have a macro that needs to FIND an instance of "TOTAL" in a cell range. "TOTAL" occurs with variable text following it in the cell but can also occur as a word inside other text. I only want the cell where TOTAL is in the leftmost position. I don't want to change the content of any of the scanned cells.

Obviously the following example catches all variants of TOTAL. How do I restrict it to only cells where TOTAL is the first word?

Range("A1").Select
    Cells.Find(What:="TOTAL", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        False).Activate
    Selection.End(xlUp).Select
    Selection.Copy
    Range("F8:F22").Select
    ActiveSheet.Paste
    Rows("23:23").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    Range("A23").Select

Many thanks for any ideas

Re: Selecting a sheet array in VBA for Excel for Mac

After more research, I believe the problem can be narrowed down to the ExportAsFixedFormat command in Mac Excel. I can show that the code to select the array of sheets works fine in both Windows and Mac. However, the:

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF 'etc...

statement only picks up the last sheet of the sheet array when running on Mac.

Further testing on Mac shows that simulating this vba action by doing a manual selection of multiple sheets, followed by a File>Print>Save as PDF on the Mac has a similar issue - only one sheet is saved.

Can anyone help as to how to work around this difference between Win Excel and Mac Excel?

I have a macro that creates a single PDF file of several worksheets of a workbook and saves it in a folder selected by the user. The array of worksheets varies according to an initial selection by the user. The arrays of sheet names to be included in the PDF are maintained in tables inside the workbook. Once the sheet names are selected as an array they are saved as a single document using the ExportAsFixedFormat VBA command.

The macro is run by different users - some using Excel 2010 for Windows and some using Excel 2011 for Mac. The macro works fine on Excel 2010. On Excel for Mac, although the array of sheet names is created correctly only the last sheet in the array is saved in the selected folder. Here is the relevant portion of the code:

Sub PrintDiv(ReportMonth, ReportDiv)
       
        ' Set up correct title page
        Sheets("Title").Select
        Range("A23").Value = ReportDiv & " "
        Range("A25").Value = ReportMonth & ", 2013"
        
        ' Select sheets to print
'
Dim strPath As String, strFileName As String, ReportSheetList As String
Dim y As Integer
Dim WkstNames() As Variant 'declares a dynamic array of sheet names
Dim iWkstCount As Integer
 
If ReportDiv = "Company" Then ReportSheetList = "ReportCoPDFsheets"
If ReportDiv = "Commercial" Then ReportSheetList = "ReportCommPDFsheets"
If ReportDiv = "Federal" Then ReportSheetList = "ReportFedPDFsheets"
 
iWkstCount = Range(ReportSheetList).Rows.Count - 1 ' finds the array size (i.e. number of sheets)
 
    'Place worksheet names into a dynamic array for reference using ExportAsFixedFormat
ReDim WkstNames(1 To iWkstCount) 'declares the array variable with the appropriate size
Sheets("TOC").Select
Range(ReportSheetList).Select
For y = 1 To iWkstCount
    WkstNames(y) = ActiveCell.Offset(y, 0).Value
    Next y
Sheets("Title").Select   'tell the export to use this sheet and ignore TOC sheet
 
Sheets(WkstNames).Select (False)  'pick up all sheets to be saved as PDFs
 
    'Select file save location from user
    If OS = "Win" Then
With Application.FileDialog(msoFileDialogFolderPicker)
    .AllowMultiSelect = False
    .Show
 
    If .SelectedItems.Count > 0 Then
        strPath = .SelectedItems(1)
    End If
End With
 
strPath = strPath & "\"
Else
Call ChooseMacFolder(strPath)  'if running on Mac, choose folder differently
End If
 
strFileName = strPath & "Company " & "- " & ReportDiv & " " & ReportMonth & " " & "2013" & ".pdf"
                    '...and export all report sheets to folder
ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=strFileName, _
    IgnorePrintAreas:=False, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True
 
                                                                                              ' IgnorePrintAreas:=False
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Title").Range("A23").Value = ""                                                                     'clear Div name from report title page
Sheets("Title").Range("A25").Value = Sheets("Dashboard").Range("G6")                    ' ...and set month back to last month
       
        ' Ungroup sheet selection
Sheets("TOC").Select
End Sub

In case it has any effect, the subroutine used on Mac for selecting the saved folder target is:

Sub ChooseMacFolder(strPath)
 
Dim folderPath As String
Dim RootFolder As String
On Error Resume Next
RootFolder = MacScript("return (path to desktop folder) as String")
'Or use RootFolder = "Macintosh HD:Users:mvp:Desktop:TestMap:"
folderPath = MacScript("(choose folder with prompt ""Select the folder"" default location alias """ & RootFolder & """) as string")
On Error GoTo 0
If folderPath <> "" Then
strPath = folderPath
End If


End Sub

Anyone have any idea why the sheet array doesn't get selected (or saved) correctly on Excel for Mac?