Posts by meijhana

Thank you for the help.

I tried the above code but received an error.

Run-time error '1004':
Application-defined or object-defined error

Walking through the code, it seems to get through the If line but then generates the error.

I also realized I did not state that the hyperlink goes to a SharePoint file. I am not sure if that makes a difference.

Again, thank you for any help
Meijhana

I have an excel file with a list of projects (~500). Every active project has a hyperlinked status report. I want to add a column showing the date that the hyperlinked status report was last modified (so I can keep track of who is not updating their status reports).
Not every project in the list is active so not every row has a hyperlink.

Project Names are in Column A along with a bunch of other information in subsequent columns.

The Hyperlink to the Status report is listed in Column AI.

I want to put into Column AJ the date that the hyperlinked file was last modified.

Hyperlinks exist in Column AI between rows 4 and 500 (though not every row in column AI has a hyperlink)

I have found a few things online but have not been able to cobble together a functioning code.
Any help would be appreciated.

Thank you
Meijhana

Re: Sum Data based on multiple date conditions

Thank you. This is working for columns F through M but is not working for Cell E. I keep getting a 0 in E2, unless I change A2 to be 5/1/16
I did do the shift=crtl+enter for the array.

Thank you
Meijhana

Columns A & B show start and end dates for particular activities and Column C shows the cost/month for those activities

I want to have a running total of the monthly cost in columns E through M

[TABLE="width: 930, align: center"]

A

B

C

D

E

F

G

H

I

J

K

L

M

1

Start Date

End Date

Rate

May-16

Jun-16

Jul-16

Aug-16

Sep-16

Oct-16

Nov-16

Dec-16

Jan-17

2

5/2/2016

9/5/2016

500

500

1250

1250

1250

1250

750

750

750

0

3

6/1/2016

12/5/2016

750

[/TABLE]

I am trying E2=SUMIFS(C:C,A:A,">="&E$1,A:A,"<"&F$1,B:B,"<"&E$1,B:B,">="&F$1)
This is gives me a value of 0 in all months.

Any suggestions?

I would also like to have column C be a daily rate and the values in E through M be based on actual days worked if anyone has a good idea on how to do that.

Thank you in advance for your help

Meijhana

I would like to use code to set the zoom level on all worksheets in my workbook on open of the workbook.

I want to do this without the screen flashing through each sheet.

I have set ScreenUpdating to False.

The only way I could find to set the zoom level was activating the sheet, then using activewindow.zoom. Worksheets(1).Zoom did not work.
By having to activate each sheet, set the zoom, then go to the next sheet, it is flashing through the sheets.

Sub Workbook_Open()


Application.ScreenUpdating = False 'prevents screen flashing through sheets during code
         
'Sets the zoom levels on all sheets in workbook
    Sheets(1).Activate
    ActiveWindow.Zoom = 90
    Sheets(2).Activate
    ActiveWindow.Zoom = 80
    Sheets(3).Activate
    ActiveWindow.Zoom = 80
    Sheets(4).Activate
    ActiveWindow.Zoom = 100
    Sheets(5).Activate
    ActiveWindow.Zoom = 90
    Sheets(22).Activate
    ActiveWindow.Zoom = 90
    Sheets(23).Activate
    ActiveWindow.Zoom = 80
    
Application.ScreenUpdating = True 'prevents screen flashing through sheets during code
End Sub

Any suggestions?

thanks!

Meijhana

Re: Sharepoint excel file and hyperlink

I have this exact same issue. Did you ever get resolution?

Re: Autofilter multiple sections of same worksheet

My ranges were all on one sheet.

A10:M46
A50:M60
A62:M70

For all the ranges, column A had the required filter criteria and for all ranges the same criteria applied.

So I ended up just filtering A10:M70 and then using Hidden=False on the rows with heading information in the middle of the ranges.

Probably not the most elegent solution but it worked for me.

I will keep the table in mind for the future.

Thanks

Sharon

I would like to autofilter multiple ranges of the same worksheet.

So far I cannot even get 1 section to autofilter. I read the articles I could find on autofiltering and I thought I was doing everything correctly, but I guess not.

The value in Cell A11 is "NFP". but it is not filtering out all the other cells.

Sub Worksheet_Change(ByVal Target As Range)
Dim L As Integer
If Target.Address = "$B$2" Then
If Worksheets("Attendance").Range("b2").Value = "Non-Functional Prototype" Then
    


                     Worksheets("Site Selection").AutoFilterMode = False
                     Worksheets("Site Selection").Range("A10:M46").AutoFilter Field:=1, Criteria1:="=NFP"
               '     Worksheets("Site Selection")..Range("A49:M62").AutoFilter Field:=1, Criteria1:="=NFP"
              '      Worksheets("Site Selection")..Range("A66:M69").AutoFilter Field:=1, Criteria1:="=NFP"


 
 
  End If
 
End If
End Sub

suggestions are greatly appreciated!

Thanks!

Re: Selection Change works, IF statement in change is not working

Thank you!

I have a selection change on worksheet "Attendance".

Cell B2 is a Data Validation drop down with 3 options (one of which is "Non-Functional Prototype").

When the drop down is changed, I want to perform a series of actions on all the other sheets in my workbook (i.e. mostly hide or unhide rows to show appropriate info)

The code is below.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$B$2" Then
If Worksheets("Attendance").Range("B2").Value = "Non-Functional Prototype" Then
    
'Predecessors
    Worksheets("Predecessors").Rows("6:14").Hidden = False
    Worksheets("Predecessors").Rows("15:25").Hidden = True
    Worksheets("Predecessors").Rows("26:42").Hidden = True
    Worksheets("Predecessors").Rows("43:59").Hidden = True
    Worksheets("Attendance").Range("h20").Value = "if works"
End If
End If
Worksheets("Attendance").Range("h18").Value = "Hello"
End Sub

I put in two little tests to make sure it was going to the correct lines. The "Hello" appears, but not the "if works". The rows in the IF statement are not hidden/unhidden as required.

What am I not doing properly? I am sure it is something silly and I have simply not had enough coffee yet.

Thanks

Re: Use Cell Value as the sheet name in a Formula.

Thanks so much! It is perfect. I did not even know that formula

Thanks

I have multiple sheets in my workbook: alpha, beta, gamma, etc and a Summary sheet.

On the summary sheet I would like to set-up the following data

A B
1 Sheet Name 1 ='Sheet Name 1'F6

2 Alpha ??? ='A2'F6 ????

What I have list in my B2 cell is not working. Any suggestions on how to make the formula pull the sheet name from text in another cell?

My workbook will have a continuously growing number of sheets.
I want the end user to be able to fill down the formulas in columns B-F, etc to prepopulate from sheets Alhpa, Beta, Gamma, ... Omega since Alpha-Omega have the same base set-up.

thanks

Re: Name Sheet Based on Cell Value

Thank you for the code and all the comments. It helps me learn.
I really apprecaite it!

Re: Name Sheet Based on Cell Value

I had it under:

Private Sub Worksheet_Change(ByVal Target As Range)

I am trying to update the name of a sheet in a workbook based on changes to the value in cell N1 of the sheet. I have looked at other threads on here and came up with the following code but it does not seem to be working.

Any suggestions?

Dim ws As Worksheet
    Dim wsNew As Worksheet
    If Target.Cells.Count > 2 Then Exit Sub
    On Error Resume Next
            If wSheet.Range("N1") = "" Then
                wSheet.Name = "Sheet" & wSheet.Index
            Else
               wSheet.Name = wSheet.Range("N1")
           End If

Thank you very much for the help.

Re: Hide Rows based on Cell Value

Thanks so much!

I would like to unhide rows 28-32, 34-38, etc based on the value in Cell A1.

A1 is populated by a Data Validation List.

If A1 is changed to "Concept" I want to unhide 28-32. If A1 is changed to "Validation" I want to unhide 34-38 and rehide 28-32.

Here is what I have so far. I do not seem to be good at the change events as they have not worked for me all day.

[code]
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A1")) Is Nothing Then
Rows(28).EntireRow.UnHidden = (Target.Value = "Concept")
End If
End Sub[Code]

thank you very much.