VBA excel macro for a button that selects named reference from a named range
I have a named range "last_monday", which dynamically calculates one variable, last monday's date ("d-mmm-yy"). I'd like a macro that I can assign to a button which would select the column where last monday's date resides within a broader range of column-wise dates, called "date_range".
E.g. assuming today's date is Monday, 15-Jan-18, the button would select the cell in position BQ5 in the example below.
1 answer
-
answered 2018-01-14 11:45
Variatus
The function below selects the cell in
Date_Range
indicated by the value ofLast_Monday
. If there is no match it does nothing.Sub SelectMonday() Dim C As Long On Error Resume Next C = Application.Match(Range("Last_Monday").Value2, Range("Date_Range"), 0) If C Then Range("Date_Range").Cells(C).Select End Sub
See also questions close to this topic
-
There is any way to put a Track bar in a VBA form?
new guy at stackoverflow here.
I would know if is possible to put a track bar in a VBA form, i am using this for a excel worksheet and with excel 2013 there is not such control. There is anything that i could do?
sorry about my english, it is not my native language, feel free to correct me , i will appreciate!
ty!
-
Needed: formula to return list of rows in excel
Say I have 3 boxes as follow:
Box 1 contains: item 1 + item 2 + item 3
Box 2 contains: item 2 + item 4 + item 5
Box 3 contains: item 3 + item 6 + item 7
I have them all in o e sheet. Is there is a formula in excel that can enter in a different sheet allow me to type just: “Box 1” and it automatically lists the contents on that particular box (each item in a different cell not all in one cell)?
So I want to enter one word in a cell, that that will result a list of other cells all together. Hope that my question is clear.
Thanks for your help.
-
Excel ComboBox displays Data Validation List with repeated entries and large gaps
I have created an Excel sheet, whereby when one double-clicks on a cell linked to a data validation entry, a combo-box appears in its place. The combo-box enables auto-searching of the entries in the data-validation list (which is a dynamic list of entries). However, on searching and hence re-sizing the dynamic list, members of this list are displayed multiple times within the dropdown and large gaps/spaces are also displayed.
A link to the sheet is provided here: https://www.dropbox.com/s/mig2l8vx301yekt/night_template_names.xlsm?dl=0
I would be grateful if anyone could provide some insight into why this is happening.
Thanks
-
VBA Excel Macro to automatically fill a matrix of Prouty
I am a beginner in VBA. I've been stuck on my project for days, I have to fill two Prouty matrices automatically, that's why I really need your help.
For my project, I must be able to display the risk identifier (IdRisk) of all the risks in a matrix of Prouty (4/96, this may seem odd but my teacher requires that the matrix be in this form: y-axis for Gravity and x-axis for Probability x Criticality and in my case, greatest Probability Value I have is equal to 96).
My excel file contains the following:
For each risk: line in "Table3" from my main tab "Analyse" is provided an IdRisk in order to find it easily. For each risk, in addition to the product Probability x Severity, there is a position in the Prouty matrix and a position in the residual Prouty matrix.
I would like to be able, in my matrix of "Prouty" and my matrix of "Prouty Residual", to display in each cell the reference or references of risks which come from my "Table3".
My macro can: - Empty the 2 matrices. - Indicates the number of rows in "Table3" & indicates the number of rows to display according to the "Yes" criterion in column A of "Table3" in a popup. - Fills the two matrices (Prouty (Column K of "Table3") and Residual Prouty (Column U of "Table3") with IdRisk (Column B of "Table3) of each risk for which it has been indicated" Yes "in (Column A of "Table3").
What I miss is that the macro can fill me both matrices correctly, even if it does now, the result is distorted, because the algorithm is only suitable when this matrix is square, which is unfortunately not my case.
For those who can help me out of this problem, here is the code of my VBA Excel Macro :
Sub Prouty() Dim lastligne As Integer, nbrisque As Integer, nbrisqueaffichés As Integer, i As Integer, j As Integer, k As Integer With Sheets("Analyse ") 'Dernière ligne des risques lastligne = .Range("A8").End(xlDown).Row 'Nombre de risques nbrisque = lastligne - 8 'Nombre de risques a afficher nbrisqueaffichés = Application.WorksheetFunction.CountIf(.Range("A9:A" & lastligne), "=" & "Oui") MsgBox ("Nbr risques : " & nbrisque & " Nbr risques affichés : " & nbrisqueaffichés) 'Vider les proutys Sheets("Prouty").Range("B5:CS8").ClearContents Sheets("Prouty Residuel").Range("B5:CS8").ClearContents 'Remplissage de prouty For i = 9 To lastligne For j = 1 To 96 For k = 4 To 1 Step -1 If .Range("A" & i) = "Oui" Then If .Range("N" & i) = Sheets("Prouty").Cells(9, j + 1) And .Range("I" & i) = Sheets("Prouty").Cells(k + 4, 1) Then Sheets("Prouty").Cells(k + 4, j + 1) = Sheets("Prouty").Cells(k + 4, j + 1) & " " & .Range("B" & i) End If End If Next k Next j Next i 'Remplissage de prouty résiduel For i = 9 To lastligne For j = 1 To 96 For k = 4 To 1 Step -1 If .Range("A" & i) = "Oui" Then If .Range("AA" & i) = Sheets("Prouty Residuel").Cells(9, j + 1) And .Range("S" & i) = Sheets("Prouty Residuel").Cells(k + 4, 1) Then Sheets("Prouty Residuel").Cells(k + 4, j + 1) = Sheets("Prouty Residuel").Cells(k + 4, j + 1) & " " & .Range("B" & i) End If End If Next k Next j Next i End With End Sub
Thank you very much and in advance for your help.
-
VBA - User Selection (workbook and sheet name) then Copy and Paste
Is there a way to make it where user selects which sheet to copy from, after they opened file?
I'd like to do this because there may be multiple sheets with same format, but have different names.
Example: Original workbook named VSC (Contains sheets Compare, Plot)
Secondary workbook named SF (Contains sheets Results1, Results2, Results3)
User clicks button on VSC, and file dialog opens, and user selects SF in a certain directory, then asks user to select which sheet to choose from - user chooses Results2 sheet, copies the data (Range"B2:B5"), then pastes it back to Compare sheet.
Would this be possible? I am not sure how to start.
Current code that asks user to select SF workbook:
Sub GetFilePath() Dim objFSO as New FileSystemObject Set myFile = Application.FileDialog(msoFileDialogOpen) With myFile .Title = "Choose File" .AllowMultiSelect = False If .Show <> -1 Then Exit Sub End If FileSelected = .SelectedItems(1) End With
-
Plot a histogram using the int value when count() is giving me strings
In this other question, the result is coming in a good way, but not good enough. The bins are strings, and the frequency plot is not real. It is like every item is a string, but I want them to be int. How can I do that?
What I want is to know how big is, having thousands of rows with a 'date' for each action, the normal volume of entries for a normal day. This is my code:
df["dayOfYear"] = df["date"].dt.dayofyear plt.hist(df.groupby(df["dayOfYear"]).count())
It gives me the answer, but bins are strings, not int, so the X axis is a collection of strings, avoiding me of getting a nice gaussian distribution.
I tried to int() it, but it doesn't work for a pandas.
Any help? Thank you! Miguel.
-
I want to get yesterday's date time in camel route header
<setHeader headerName="order_creation_date"> <simple resultType="java.lang.String"> ${date-with-timezone:now:GMT-5:MM-dd-yyyy HH:mm:ss} </simple> </setHeader>
the above code sets the current date with new york time zone in exchange header. I need 24 hours back date-time from current date in exchange header from camel XML
-
Extract data for past 7 days (Cannot use current_date function)
I am currently using Amazon Red Shift and I am trying to extract data for the past 7 days but do not want to use current_date function.
Is there any other alternative way, any suggestions. Thanks..