Posts by Brewface

Hi All,

I am hoping someone will be able to come up with a handy way to insert the cell property "font" into an array so i can check whether it is a strikethrough or not without having to continuously call the sheet.
Basically I am looking for something similar to this:

FontTypeArray() = Sheet1.Range(Sheet1.Cells(17, CableLengthCol), Sheet1.Cells(LastRow, CableLengthCol)).Font

I've tried everything from setting the array to an object, font, ect. I know you can set a variable to the font type by going

Set z=Sheet1.Cells(17, CableLengthCol).Font

But this does not work for arrays. If i have to loop through the array anyways I will just leave my code as is and continually check the sheet for font type. I was just trying to minimize the number of sheet calls to speed the code up significantly, as I am in the realm of 10000 lines of data.

Cheers,

Re: SumIf cell is equal to any in a list

That is genius!!
Would you mind explaining how it is that works?? I understand the syntax of both but didn't know you could combine them in such a way...

Re: SumIf cell is equal to any in a list

I had thought of using sumproduct but my problem was that you could again only test one condition at a time not an entire list of conditions against one cell, unless I am missing something. It looks like I will just have make a calculation column and then drag and drop for each condition and make a matrix then sum that, simple enough. Was hoping for a one stop formula to test all conditions, but i can make this work with no issues I think. Thanks!

Hello all,

I have had no luck with internet searches nor racking my brain for a solution. The basic problem is this, I have a large range of data 2000+ cells and I need to Add only the hours for a division within a department.
The division is given with a list of names. To be more clear here is an example

Name Hours
Doe 2
Smith 3
Murphy 3
Roberts 8
Mollard 4

Names belonging to Division A_

Doe
Mollard

Division A Total hours:

6

Usually i would just do a series of If statements but there is between 30 and 55 names in one division and this would be painstakingly tedious (I have to do this same thing for 13 divisions...)
Ideally I would just like to Specify my Sum Range, Specify the Criteria Range, Specify the Criteria List. Exactly like a Sumif...but the Criteria can be anyone in a list. I thought of some combination of Vlookup or match might work but have had no luck so far.

Thanks for the help,

Brewface

Re: Automatic Process Flow

Bump, still have no idea! Looking for some help please!

Hi all,

I am hoping someone has some wonderful ideas on how to make an automatic Process Flow (flow chart), similar to what is available in the Smart Art section.
The situation is this as I select a station it would get added to the flow, if i delete the station it is removed from the process flow.
It's much nicer to do this automatically and represent these things visually.
Some key information,
The stations would be selected on a different sheet than where the process flow is.
I think this goes without saying but the text would in each box would be the same as the station.

That's it! I am not even sure where to start with this, so any help is appreciated. I am familiar with VBA, having dabbled in it for the last couple of years, so am not opposed to having a macro solution.

Re: Special Validation List

I'm in the same boat as Roy at the moment. I had assumed active meant it would appear in the list and if not active it would not appear in the list!

Re: Special Validation List

I'm not sure whether this is possible just through a validation list...
Are you able to have an unused row and then just hide it? If so, you might be able to accomplish through VBA...my idea would be to check each cell in the column/row for bold...something like

Sheet("Sheet name here").Columns(List Column #).ClearContents
LastRow=Sheet("Sheet name here").Cells(Rows.Count, Appropriate columns here).End(xlUp).Row


For iRow=1 To LastRow
If Sheet.Cell(iRow, Column).Font.Bold=True Then
Sheet.Cell(iRow, Column).Copy Sheet.Cell(1, Other Column)
End If
Next

And then make your validation list that Column. I'm not sure about how to trigger it though and whether a change event would capture changing it to bold or not...
Let me know if that code makes any sense to you and or if it put you down the right path, it is very early over here in Deutschland!

Re: Need to keep selected data visible when unhiding rows.

I managed to solve the problem with an array, it's pretty brute force but works.

If Range("C25").Address = Target.Address Then
    
    For i = 26 To 60
        If Rows(i).EntireRow.Hidden = False Then
            ReDim Preserve Vis(Z)
            Vis(M) = i
            M = M + 1
            Z = Z + 1
        End If
    Next
    
    Sheets("Data Introd").Rows("26:61").Hidden = False
    FindStr = Target.Value
    Set FindRng = Sheets("Data Introd").Columns(3).Find(What:=FindStr, After:=Range("C25"))
    FindRow = FindRng.Row
    
    Sheets("Data Introd").Rows("26:61").Hidden = True
    Sheets("Data Introd").Rows(FindRow).Hidden = False
    
    For i = 0 To UBound(Vis)
        RowNum = Vis(i)
        Sheets("Data Introd").Rows(RowNum).Hidden = False
    Next
    
End If

Quote

Please don't nurture a sense of entitlement about the help you should get here. People give of their time freely here. And by the way, criticising code provided by other members will not endear you to anyone!

I have never nurtured a sense of entitlement, and don't recall criticising anyone's code other than my own. The most I would of said would be there is probably a more efficient way of performing the required action, but that is more than likely pointed to my own code.

Hello all,

I am hoping someone may be able to provide the answer to this question.

I have a list of countries which are all hidden until one is selected in a combobox. When the Country is selected that row is unhidden, this is easily done. My problem is that I want to keep that row unhidden when a new country is selected...if the positioning of the rows remained the same this would be fine; however, they are subject to change. So I am having to unhide the rows find the value and then rehide them again.
If that explanation is unclear I will try again...

• If a country was selected I want to keep that row unhidden
• The row where the country appears may change depending on if a new country was added. (They are sorted by continent and must remain this way)
• Is there an..."If visible" function in VBA?

Hopefully i have better luck this time than previous posts...

Cheers,

Brewface

Re: Generate Master Sheet

Some things to note in the code....the first part is just placing cell names into their appropriate place...the second part is placing a Sum into the individual says...just like you would do normally.
One other thing if you are going to change the sheet names then you MUST change them here as well...this is not an elegant way to write it but it is effective.

Sub Summary()


Sheets("Sheet1 (2)").Cells(2, 1).Value = "All Sections Total"
Sheets("Sheet1 (2)").Cells(1, 2).Value = "Monday"
Sheets("Sheet1 (2)").Cells(1, 3).Value = "Tuesday"
Sheets("Sheet1 (2)").Cells(1, 4).Value = "Wednesday"
Sheets("Sheet1 (2)").Cells(1, 5).Value = "Thursday"
Sheets("Sheet1 (2)").Cells(1, 6).Value = "Friday"
Sheets("Sheet1 (2)").Cells(1, 7).Value = "Saturday"
 
Sheets("Sheet1 (2)").Cells(2, 2).Formula = "=Sum(" & "'" & Sheets("Sheet1 (3)").Name & "'!" & Cells(2, 2).Address & ":" & "'" & Sheets("Sheet1 (3)").Name & "'!" & Cells(11, 2).Address & ")"
Sheets("Sheet1 (2)").Cells(2, 3).Value = "=Sum(" & "'" & Sheets("Sheet1 (3)").Name & "'!" & Cells(2, 3).Address & ":" & "'" & Sheets("Sheet1 (3)").Name & "'!" & Cells(11, 3).Address & ")"
Sheets("Sheet1 (2)").Cells(2, 4).Value = "=Sum(" & "'" & Sheets("Sheet1 (3)").Name & "'!" & Cells(2, 4).Address & ":" & "'" & Sheets("Sheet1 (3)").Name & "'!" & Cells(11, 4).Address & ")"
Sheets("Sheet1 (2)").Cells(2, 5).Value = "=Sum(" & "'" & Sheets("Sheet1 (3)").Name & "'!" & Cells(2, 5).Address & ":" & "'" & Sheets("Sheet1 (3)").Name & "'!" & Cells(11, 5).Address & ")"
Sheets("Sheet1 (2)").Cells(2, 6).Value = "=Sum(" & "'" & Sheets("Sheet1 (3)").Name & "'!" & Cells(2, 6).Address & ":" & "'" & Sheets("Sheet1 (3)").Name & "'!" & Cells(11, 6).Address & ")"
Sheets("Sheet1 (2)").Cells(2, 7).Value = "=Sum(" & "'" & Sheets("Sheet1 (3)").Name & "'!" & Cells(2, 7).Address & ":" & "'" & Sheets("Sheet1 (3)").Name & "'!" & Cells(11, 7).Address & ")"


End Sub

Re: Generate Master Sheet

Did not test this before posting but should give you an idea what to do.

Sheet(Insert Sheetname...or index number here).Cells(2,1).Value="All Sections Total"
Sheet(Insert Sheetname...or index number here).Cells(1,2).Value="Monday"
Sheet(Insert Sheetname...or index number here).Cells(1,3).Value="Tuesday"
Sheet(Insert Sheetname...or index number here).Cells(1,4).Value="Wednesday"
Sheet(Insert Sheetname...or index number here).Cells(1,5).Value="Thursday"
Sheet(Insert Sheetname...or index number here).Cells(1,6).Value="Friday"
Sheet(Insert Sheetname...or index number here).Cells(1,7).Value="Saturday"


Sheet(Insert Sheetname...or index number here).Cells(1,2).Formula= "Sum(" & "'" & Sheets(Sheetname*of other sheet8).Name & "'!" & Cells(2,2).address & ":"  & "'" & Sheets(Sheetname).Name & "'!" & Cells(11,2).Address & ")"
Sheet(Insert Sheetname...or index number here).Cells(1,3).Value="Sum(" & "'" & Sheets(Sheetname).Name & "'!" &  Cells(2,3).address & ":" "'" &  Sheets(Sheetname).Name & "'!" & Cells(11,3).Address & ")"
Sheet(Insert Sheetname...or index number here).Cells(1,4).Value="Sum(" & "'" & Sheets(Sheetname).Name & "'!" &  Cells(2,4).address & ":" &  "'" &  Sheets(Sheetname).Name & "'!" & Cells(11,4).Address & ")"
Sheet(Insert Sheetname...or index number here).Cells(1,5).Value="Sum(" & "'" & Sheets(Sheetname).Name & "'!" &  Cells(2,5).address & ":" &  "'" &  Sheets(Sheetname).Name & "'!" & Cells(11,5).Address & ")"
Sheet(Insert Sheetname...or index number here).Cells(1,6).Value="Sum(" & "'" & Sheets(Sheetname).Name & "'!" &  Cells(2,6).address & ":" &  "'" &  Sheets(Sheetname).Name & "'!" & Cells(11,6).Address & ")"
Sheet(Insert Sheetname...or index number here).Cells(1,7).Value="Sum(" & "'" & Sheets(Sheetname).Name & "'!" &  Cells(2,7).address & ":" &  "'" &  Sheets(Sheetname).Name & "'!" & Cells(11,7).Address & ")"

Re: Generate Master Sheet

...Do you want this totalled by Day, Section, or some other means?

Re: Date difference between weekdays (Monday, Tuesday, etc.)

Are you just working with Days of the week? As in NOT dates?
If so you might be forced to create your function and convert the days into numbers...
I.E.
Sunday=1
Monday=2
...
Saturday=7

Then its just a simple subtraction problem

Re: Automatic population of table from data

Sub populate()
     
    Dim NoOfIncumbents As Integer
    Dim BU As Variant
    Dim Grade As String
    Dim FoundCell As Range
    Dim FoundRow As Range
    Dim FoundCol As Range
     
    lrow = Worksheets("1").Range("B" & Rows.Count).End(xlUp).Row
     
    For NoOfIncumbents = 4 To lrow
         
        Grade = Sheets("1").Cells(NoOfIncumbents, 4).Value
        BU = Sheets("1").Cells(NoOfIncumbents, 3).Value
         
        If Grade <> "" And BU <> "" Then
            Set FoundCol = Sheets("2").Rows(3).Find(what:=BU).EntireColumn
            Set FoundRow = Sheets("2").Columns(2).Find(what:=Grade).EntireRow
            Set FoundCell = Intersect(FoundRow, FoundCol)
            FoundCell.Value = Sheets("1").Cells(NoOfIncumbents, 2).Value
            Grade = ""
            BU = ""
        End If
         
    Next NoOfIncumbents
     
End Sub

Re: Automatic population of table from data

i had the code doing the opposite of what you wanted. Taking from sheet 2 and adding it to sheet 1

Re: Automatic population of table from data

Tested it and everything seems to work as designed. Let me know if any issues arise

Sub populate()


Dim NoOfIncumbents, BU, Grade As Integer
Dim FoundCell As Range


lrow = Worksheets("1").Range("B" & Rows.Count).End(xlUp).Row


For NoOfIncumbents = 4 To lrow


FindStr = Sheets("1").Cells(NoOfIncumbents, 2).Value


Set FoundCell = Sheets("2").Cells.Find(What:=FindStr)


If Not FoundCell Is Nothing Then
    Sheets("1").Cells(NoOfIncumbents, 3).Value = Sheets("2").Cells(3, FoundCell.Column).Value
    Sheets("1").Cells(NoOfIncumbents, 4).Value = Sheets("2").Cells(FoundCell.Row, 2).Value
    Set FoundCell = Nothing
End If


Next NoOfIncumbents


End Sub

Re: Automatic population of table from data

Can you please attach a workbook? Will make writing the macro much easier.

Re: Hide row if 3 cell in a column is blank in Pivot

No worries, I would really suggest you read through a few of the tutorials found on this site and on Chip Pearsons Site. VBA problems come up more often than you would think in the work place!

Re: Hide row if 3 cell in a column is blank in Pivot

Quote from blueorangeko;674660

the error is Run time error 9 : Subscript out of range

Code

Sub test2()




Dim Counter As Long
 
LastRow = Sheets(Summary).Cells(Rows.Count, 1).End(xlUp).Row
 
For iRow = 1 To LastRow
    Counter = 0
    For iCol = 2 To 5
         
        If Cells(iRow, iCol) = "" Then
            Counter = Counter + 1
            If Counter = 3 Then
                Sheets(Summary).Rows(iRow).Hidden = True
            End If
        End If
         
    Next
     
Next




End Sub

Display More

Please read my other post carefully you MUST put quotation marks around the sheetname if you are going to designate it this way.

i.e.

Sheets("Summary")