Posts by jan.g

Thanks KjBox for the really prompt solution. Fantastic. Worked perfectly. I did read about the Intersect but thought it related to a range of cells.
I had modified my code based on someone else's solution so not sure why it worked for them and not me. (I did miss an "EndIf on my post"..still didn't work though).
Happy Easter.
Jan

Hi All,
After much searching and trying various code snippets, I am unable to solve my problem of why this code is not working. There are 2 sheets on the worksheet. If the value in cell A1 Sheet 1 = 0 then hide Column C in Sheet 2. Seems simple. I am using Excel for Mac 2016. Any advice appreciated. Cheers and happy Easter to all. Jan
Worksheet is attached and code is in Sheet1

[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
If Target_Address = Range("A1") Then
If Target_Address = "0" Then
Sheet2.Columns("C").EntireColumn.Hidden = True
Else
Sheet2.Columns("C").EntireColumn.Hidden = False
End If
End Sub[/VBA]

Hello everyone.

I would really appreciate if someone would be kind enough to help me out as I am still trying to learn VBA. I have a basic spreadsheet (attached) with 3 years of data for 2 companies. The Years do not necessarily correspond for each company. As a new company is started the User can add additional columns. All data is totalled by Year. I am trying to run a macro that will compare the year headings (for each new company) to the year headings for the Totals. If the Year is not already there then add it to the end. I will then copy the formula to the new columns. Many thanks in advance. [TABLE="border: 1, cellpadding: 1, width: 500"]

Coy A

Coy A

Coy A

Coy B

Coy B

Coy B

Total

Total

Total

Total

Total

2017

2018

2019

2019

2020

2021

2017

2018

2019

20

40

30

10

20

25

20

40

30

Coy A

Coy A

Coy A

Coy B

Coy B

Coy B

Total

Total

Total

Total

Total

Total

2017

2018

2019

2019

2020

2021

2017

2018

2019

2020

2021

20

40

30

10

20

25

20

40

30

20

25

[/TABLE]
[ATTACH]n1201569[/ATTACH]
This is my code so far, but is doing too many loops! and I am not sure why. I have tried several bits of code from the web but not getting the correct result.

[VBA]Sub Test2()

Dim Stg2Cell As Range
Dim TotalCell As Range
Dim Stg2Title As Range
Dim TotalTitle As Range

Set Stg2Title = Range("e2:g2")
Set TotalTitle = Range("H2:j2")

For Each Stg2Cell In Stg2Title
For Each TotalCell In TotalTitle

If Stg2Cell > TotalCell Then
lastcol = Sheet1.Cells(2, Application.Columns.Count).End(xlToLeft).Column
Cells(2, lastcol + 1).Value = Stg2Cell.Value
End If

Next
Next
End Sub
[/VBA]

Re: Using Last Column Variable name instead of Column reference

Good Afternoon KjBox,
Thank you so much for the script. It works perfectly. It may take me a little time to understand the code as I am quite new to VBA but it's all part of the learning journey. This site is a fabulous resource and I would have been struggling for ages without your support.
Once again, thank you for your time and patience.
Regards
Jan
Thanks also for the IF statement explanation.

Re: Using Last Column Variable name instead of Column reference

Hello KjBox,

My apologies. I should have shifted 1 more column to the right. Actual data starts summing from column K. Rows 1 to 10 are headers and logo.
To upload actual file, I would need to desensitise the data which might take a little bit of time, but I am happy to do this if required. Pls let me know if that would be better.

Cheers
Jan

Re: Using Last Column Variable name instead of Column reference

Hello Again,
Attached is the spreadsheet in the same format as my data. Columns A-J are mostly text and static data. I have copied the new code and run but still gives a Mismatch error.
PS I am working on Mac Excel 2016.
Cheers Jan

Re: Using Last Column Variable name instead of Column reference

Hi KjBox,

The original post was an example of what I was trying to achieve. But for my work file I need to make it more dynamic. I have only very slightly modified the code to update some column and cell values as there were a few more columns before the data columns and a few more rows in the header.
Yes I am trying to make the code more dynamic to account for additional stages which are added through a checkbox. I can update the original file to match the actual file if required. Thanks for your help. It is awesome.

Cheers Jan

Re: Using Last Column Variable name instead of Column reference

Hello KjBox,

Thank you for the fabulously swift reply. I have run the code but it stops on the & after lrow with a Type Mismatch Error.

Cells(i, j) = WorksheetFunction.SumIfs("Range(Cells(i, 11), Cells(i, " & lrow & ")), Range(""K10:T10""), Cells(10, j)")

I have the paste working by switching the row and col ref to Cells(i, icol +1)

Many thanks
Jan

Hello All,
This query continues on from a previous post: http://www.ozgrid.com/forum/showthread.php?t=204475 which has been answered perfectly.

The position of the last column and the number of columns that are totalled may change.
Sumifs Range: Cells(i,11), Cells (i,[COLOR="#FF0000"]20[/COLOR]) - i would like to replace 20 with the last column
Paste Range: Range("U" & i & ":Y" & i) - this would be the last column + 1 to totrange (or last column +5 cols)
I have tried posting the variable name into the code (which was suggested elsewhere) but get an error on both lines.

Sub Totals()
     
    Dim lrow As Long
    Dim i As Long
    Dim j As Integer
    Dim lcol As Long
    Dim totrange As Long
    lrow = Cells(Rows.Count, 1).End(xlUp).Row
    lcol = Range("z49").End(xlToLeft).Column
    totrange = lcol + 5


    For i = 11 To lrow
        For j = lcol To totrange
            If Cells(i, 11) <> "Units" Then
                Cells(i, j) = WorksheetFunction.SumIfs(Range(Cells(i, 11), Cells(i, 20)), Range("K10:T10"), Cells(10, j))
            Else
                Cells(i, j) = Cells(i, j - 5)
            End If
        Next j
        Range("k" & i & ":O" & i).Copy
        Range("U" & i & ":Y" & i).PasteSpecial Paste:=xlPasteFormats
        If Range("l" & i) = 0 Or Len(Range("l" & i)) = 0 Then Range("K" & i & ":O" & i) = ""
    Next i

Many thanks for your help.

Re: WorksheetFunction Sumif with Multiple Columns and Rows - looping through Budget

Hello Max,

Thank you again. The above solution worked perfectly.
Just a quick question as I am trying to learn VBA...could you explain why there doesn't need to be any "End If" after the additional line

If Range ("1" & i) = 0 etc...

.

Cheers and all the best
Jan

Re: WorksheetFunction Sumif with Multiple Columns and Rows - looping through Budget

Hello Max,
Thank you so much for the extremely fast response. You either work really late or you live somewhere in the Sthn hemisphere.
I have found this site invaluable in trying to learn VBA as a beginner. It's much harder on a Mac as it is difficult to debug and there are no helpers when typing code.
Your code was perfect.
Just 1 thing, I would like to remove those lines that have no value so they should not total. These blank rows just improve the visual. I added a column to the end and put letters in each cell to mimic a real report description. I tried to change

If Cells(i, 1) <> "Units" Then

to

If (Cells(i,1)<> "Units" or Cells(i,16) <> "") Then

but it still calculates a total for those lines. I also tried "isEmpty". Any ideas.

Cheers
Jan

Hi All

I have spent many days trying to work this out but am now at the point of pulling my hair out.
My spreadsheet (copy attached) is a P&L Budget with multiple columns divided into Stages. Each Stage has a number of subheadings, Eg Units, Total,2017, 2018, 2019.
In my actual spreadsheet, I have used named ranges for the Sum Range, Lookup Range and Lookup Value
For the Overall Totals my formula is =SUMIF(Titles,TotHead,AllStages)
I would like to achieve the following:

• Sum the totals for each row under the correct subheading.
• Copy the formatting
• Skip header and blank rows

I have this code which works for 1 column only totals. How can I adapt it to total all columns using the SUMIF Worksheet Function across all columns, skipping headers and blank rows.

Sub SumTotals()
    Dim total As Double




    For iRow = 5 To 70
        total = 0
       For iCol = 1 To 10
            If (Cells(3, iCol).Value) = "2017" Then
                total = total + Cells(iRow, iCol).Value
            End If
        Next iCol
        Cells(iRow, 11).Value = total
    Next iRow
[COLOR=#FFFFFF][FONT=Menlo]End Sub[/FONT][/COLOR]

Very much appreciate any ideas.
Cheers and have a wonderful day.

Re: Error 1004: Application.Dialogs(xlDialogOpen).Show Mac Excel 2016 Importing Data

Hi Luke,

Many thanks for your time reviewing and sorting the above which does work. I hope Microsoft come up with a simpler solution in the future to achieve this with the Mac so that files can be compatible with both Mac and PC.
The reason I wanted to add to the existing code was because the file was written by someone else and I didn't want to completely rewrite to work on the Mac.
All the best
Cheers Jan

Re: Error 1004: Application.Dialogs(xlDialogOpen).Show Mac Excel 2016 Importing Data

Hello Again,
After spending many hours searching the web, I have found a couple of articles that may be of interest to other MAC users. These are quite complex (at least to me as a newbie).
Apparently, the Mac does NOT support [FONT=&amp]Application.filedialog - [/FONT]https://answers.microsoft.com/…d2-4379-842a-d6b9efe9f66f
This article also refers to:
Problems with Apple’s sandbox requirements and Mac Office 2016 with VBA code - http://www.rondebruin.nl/mac/mac034.htm
Select files on a Mac (GetOpenFilename) - http://www.rondebruin.nl/mac/mac015.htm

I also came across this code which actually does work exactly as required on the MAC.

Dim vFile As Variant
Dim wbCopyTo As Workbook
Dim wsCopyTo As Worksheet
Dim wbCopyFrom As Workbook
Dim wsCopyFrom As Worksheet








Set wbCopyTo = ActiveWorkbook
Set wsCopyTo = wbCopyTo.Sheets("Sheet1")








vFile = Application.GetOpenFilename
If TypeName(vFile) = "Boolean" Then
Exit Sub
Else
Set wbCopyFrom = Workbooks.Open(vFile)
Set wsCopyFrom = wbCopyFrom.Worksheets(1)
End If




'MsgBox "Please Close Opened MHL Workbook To Continue"




wsCopyFrom.Range("A1:ll4000").Copy
wsCopyTo.Range("A1").PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
wbCopyFrom.Close False


End Sub

Can the original code I posted be modified based on the above? I have tried but still get errors as I am still trying to understand/learn the logic.
Many thanks.
Jan

Re: Error 1004: Application.Dialogs(xlDialogOpen).Show Mac Excel 2016 Importing Data

Hi Luke,
Thank you for your extremely prompt response...I posted the code into my test file but errored on line: Call Application.FileDialog(msoFileDialogOpen).Filters.Clear
Error: Run-time error '438':
Object doesn't support this property or method.

Perhaps something to do with Excel for Mac?
I also tried this simple code from https://answers.microsoft.com/…4f-a1a0-41e0959f28b8?db=5 but could not get it to work either. I am new to VBA but have had other vba macros work.

Cheers Jan