to input date between start and end date
I have a method to input a start and end date using user form. How can I input the dates in between this start and end date and provide it as an input in excel sheet using
For example, if my start date
1st jan 2012 and my end date is
1st april 2012, I would like to get input in an
excel sheet in this form :
1st jan 2012 1st feb 2012 1st march 2012 1st april 2012
Please, suggest me a VBA code to do the same.
It s difficult to understand what do you mean but you can try this:
Sub test3() Dim Startdate As Date, EndDate As Date, AdditionalDate As String Dim DatesDiff As Integer Dim i As Integer Dim LastRowA As Long Startdate = Format(CDate("01/01/12"), "DD/MM/YYYY") EndDate = Format(CDate("01/04/12"), "DD/MM/YYYY") If Sheet1.Range("A1").Value = "" Then Sheet1.Range("A1").Value = Startdate Else LastRowA = Sheet1.Cells(Sheet1.Rows.Count, "A").End(xlUp).Row Sheet1.Range("A" & LastRowA + 1).Value = Startdate End If DatesDiff = Month(EndDate) - Month(Startdate) - 1 For i = 1 To DatesDiff AdditionalDate = Left(CStr(Startdate), 2) & "/" & (Month(CStr(Startdate)) + i) & "/" & Right(CStr(Startdate), 4) LastRowA = Sheet1.Cells(Sheet1.Rows.Count, "A").End(xlUp).Row Sheet1.Range("A" & LastRowA + 1).Value = CDate(AdditionalDate) 'Format(CDate(AdditionalDate), "DD/MM/YYYY") Next i LastRowA = Sheet1.Cells(Sheet1.Rows.Count, "A").End(xlUp).Row Sheet1.Range("A" & LastRowA + 1).Value = EndDate End Sub
See also questions close to this topic
How to link a ComboBox value with a different value and put that value into a cell in a worksheet
I have an excel workbook. Within that work book I have one sheet named Classes and I have another sheet called Homework. In the Classes sheet I have the different classes in column A I also have the class hours in column B. In the Homework worksheet I have the homework assignment names in column A. I have a UserForm that has a TextBox, ComboBox and a command button. In the TextBox I am supposed to type the assignment name. The combobox is populated with the classes that are in column A of the Classes worksheet. I want to be able to type in the homework assignment and select a class within the combobox and then click the command button and this will put the homework assignment in column A of the Homework worksheet and then put the class hour (Ex. 1,2,3,4,5 not the class name) in column B of the Homework worksheet. I dont know how to link the classes that are populated in the Combobox with the class hours so that when I choose a class it will then put the class hour into a cell within the Homework worksheet.
How to prevent UiPath from getting stuck in Call Macro Stage?
I am calling a macro using call macro in UiPath Workflow. The concern is some time one of the excels that the macro handles will freeze forever, Causing the macro execution to hang and the process will get stuck in Call macro forever.
Is there some way I can modify the UiPath workflow so that, if the Call macro activity is not completed within 15 minutes, the bot throws an exception?
Scraping web with url in milliseconds Unix
The web in which I browse has several pages. I want to click on those elements or play with urls and thus be able to copy the data. The initial URL ends at = 1 & playerType = ALL & ts = 1558502019375, in my code there is a loop that is supposed to go page after page to get the data but I can not get it done.
Sub UPDATE_DATA_MLB() Application.ScreenUpdating = False 'dimension (set aside memory for) our variables Dim objIE As InternetExplorer Dim ele As Object Dim y As Integer Dim EstaPagina As Byte Dim EstaURL As String 'Página inicial EstaPagina = 1 'we will output data to excel, starting on row 1 y = 1 EstaURL = "http://mlb.mlb.com/stats/sortable.jsp#elem=%5Bobject+Object%5D&tab_level=child&click_text=Sortable+Player+hitting&game_type='R'&season=2018&season_type=ANY&league_code='MLB'§ionType=sp&statType=hitting&page=1&playerType=ALL&ts=1558502019375" '&ts=1526432697176" 'start a new browser instance Set objIE = New InternetExplorer 'make browser visible objIE.Visible = False Do Until EstaPagina = 255 'navigate to page with needed data objIE.navigate EstaURL & EstaPagina 'wait for page to load Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop 'If UCase(Left(EstaURL, 211) & "1") = UCase(Left(objIE.LocationURL, (211 + Len(EstaPagina)))) And y > 1 Then Exit Do 'look at all the 'tr' elements in the 'table' with id 'myTable', 'and evaluate each, one at a time, using 'ele' variable For Each ele In objIE.document.getElementById("datagrid").getElementsByTagName("tr") 'show the text content of 'tr' element being looked at 'Debug.Print ele.textContent 'each 'tr' (table row) element contains 4 children ('td') elements 'put text of 1st 'td' in col A Sheets("Sheet1").Range("A" & y).Value = ele.Children(0).textContent 'put text of 2nd 'td' in col B Sheets("Sheet1").Range("B" & y).Value = ele.Children(1).textContent 'put text of 3rd 'td' in col C Sheets("Sheet1").Range("C" & y).Value = ele.Children(2).textContent 'put text of 4th 'td' in col D Sheets("Sheet1").Range("D" & y).Value = ele.Children(5).textContent 'put text of 4th 'td' in col f Sheets("Sheet1").Range("E" & y).Value = ele.Children(22).textContent 'increment row counter by 1 y = y + 1 Next EstaPagina = EstaPagina + 1 Loop lobjIE.Quit Set objIE = Nothing Set ele = Nothing Range("A1").Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select Selection.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5), _ Header:=xlNo Application.ScreenUpdating = True MsgBox "Volcado terminado", vbInformation Range("A1").Select 'save the Excel workbook ActiveWorkbook.Save End Sub ```
VBA: Loop through an array, find how many values are matching and display the result as percentage in a cell
- What I have so far
The code below, which I got from my original request here: Finding if a cell values (delimited by comma) are all existing in a defined table
Option Explicit Sub LE_Checker() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Application.DisplayStatusBar = False Application.EnableEvents = False Dim RunValidation As Integer RunValidation = MsgBox("Are you sure?", vbYesNo, "Checking LE Matches") If RunValidation = vbYes Then Dim ws1 As Worksheet, ws2 As Worksheet Dim LastRowA As Long, i As Long, y As Long Dim arr As Variant Dim CountryCode As String Dim rng As Range, SearchRange As Range, FindPosition As Range Dim Appears As Boolean Dim Suppliers As Long 'Set worksheets on variables With ThisWorkbook Set ws1 = .Worksheets("Sheet1") Set ws2 = .Worksheets("Sheet1") ' For testing purpose I'm gonna use the same sheet End With 'Set the range to search in for country codes Set SearchRange = ws2.Range("I2:K2") With ws1 'Find the last row of Column A Sheet1 LastRowA = .Cells(.Rows.Count, "A").End(xlUp).Row ' Start loop from row 2 to last row Sheet1 For i = 2 To LastRowA 'Criteria needed: Content Groups: not empty / Region: EUROPE / Old Supplier: No (case sensitive) If .Range("A" & i).Value <> "" And .Range("C" & i).Value = "EUROPE" And .Range("D" & i).Value = "No" Then CountryCode = .Range("B" & i).Value 'In which column is the country code found Set FindPosition = SearchRange.Find(What:=CountryCode, LookIn:=xlValues, LookAt:=xlWhole) 'If country code exists If Not FindPosition Is Nothing Then 'Set the range to search for the groups in the column where the code is header Set rng = ws2.Range(ws2.Cells(2, FindPosition.Column), ws2.Cells(ws2.Cells(ws2.Rows.Count, FindPosition.Column).End(xlUp).Row, FindPosition.Column)) 'Split the string with comma and assing it on arr arr = Split(.Range("A" & i).Value) Appears = False 'Loop the arr For y = LBound(arr) To UBound(arr) 'Check if the arr(y) start from C as all code start from C If Left(arr(y), 1) = "C" Then 'Count how many times the arr(y) with out the comma appears in the rng If Application.WorksheetFunction.CountIf(rng, Replace(arr(y), ",", "")) > 0 Then 'If appears the variable Appears is true Appears = True Else 'If does not appear the variable Appears is False & Exit the loop Appears = False Exit For End If End If Next y 'Check Appears variable status and import value in Column C If Appears = True Then .Range("E" & i).Value = "Yes" Suppliers = Suppliers + 1 Else .Range("E" & i).Value = "No" End If End If End If Next i End With MsgBox "Done!" & Chr(13) & "Found " & Suppliers & " suppliers" End If Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic Application.DisplayStatusBar = True Application.EnableEvents = True End Sub
The code will loop through a cell's values and find if all of them match the values defined in a table for the country code specified. If all codes match what's in the table = YES, otherwise = NO.
Like in the picture below:
- What I need:
An extension of this code, basically to check in that array loop (column A), in addition to what it does now, how many values are matching. Then return it in next column (F) as percentage (I need to identify the partial ones that have at least 50% codes in)
For example: If a supplier has 2/3 codes matching the country he's from, then show in column F 66.6%, if a supplier has 1/4 codes matching show 25% and so on.
- Expected result:
Google Map Scrapping
I have a list of companies and I want scrap all the companies address from google map using vba. I have tried to do it with some of the codes of vba but still its not working. Can anyone guide me please. I will really appreciate you for this. You can contact me @ email@example.com
Find With VBA DOCX File in Folder and Add It To eMail
I created code that is searching certain files inside a folder with dynamic path, then add it to an email and sendes it. I want that in every email I send inside the loop to will be specific DOCX file too. but I dont want to search for specific name, i want that it just will find the only DOCX in this folder and add it as an attachment.
i n my code its not working well, the part with the DOCS file. Here is the relevant part of the code:
'*****************************************EMail from here Dim outlookApp As Outlook.Application Dim myMail As Outlook.MailItem Dim source_file, to_emails As String Dim WordFile As String Dim i, j As Integer '---------here i find the file, I tried that it will search for the only docx in the folder WordFile = Dir(path & "\*.docx") Do While Len(WordFile) > 0 WordFile = Dir Wend For i = 2 To LastManaRow 'find manager in mailing list With ListSheet ManaNumber = .Cells(i, 1).Value End With With EmailWS j = .Range(.Cells(2, 1), .Cells(LastMailRow, 1)).Find(ManaNumber, , xlValues, xlWhole).Row to_emails = .Cells(j, 3).Value End With Set outlookApp = New Outlook.Application Set myMail = outlookApp.CreateItem(olMailItem) FileToSendName = ManaNumber & " " & PartFileName source_file = path & "\" & FileToSendName & ".xlsx" '-----here i give the excel file variable name MainWB.Worksheets("Activation").Cells(1, 5) = WordFile MsgWS.Cells(1, 5) = FileToSendName MsgWS.Range("H1") = source_file With myMail .Attachments.Add source_file .Attachments.Add WordFile ' ------ERROR because the variable has no value .To = to_emails .Subject = MainWB.Worksheets("MSG").Range("B1").Value .Body = BodyMsg .Send End With Next i