Introduction [ad_1] Solution [ad_2] I have a list of excel filenames in the column A of my worksheet and I would like to remove the .xls and .xlsm tags from the filenames using VBA. How?
[ad_1] Assuming that column A of sheet1 are date/times, and not text, and you are attempting to find how many date/times are more than 16 hours old then you simply want to place the following formula in sheet2!F7: =COUNTIF(sheet1!A:A,”<“&NOW()-0.75) (16 hours is 0.75 of a day.) If the data in column A is text, then … Read more
[ad_1] Hi. Im trying to use the VBA code in powerpoint to run a complete powerpoint that an ai wrote for me but i keep getting an error code [closed] [ad_2] solved Hi. Im trying to use the VBA code in powerpoint to run a complete powerpoint that an ai wrote for me but i … Read more
[ad_1] This problem is indeed not vba specific. For each box, add a label when it was added to the storage. You may take the point in time as a timestamp, iteration number or whatever unit suits you. Then you can now evaluate the time the box spent in storage by calculating now-time_when_box_was_added, for every … Read more
[ad_1] Public Sub MakeColumnsFromRows() Dim totalCutsToMake As Integer Dim currentColumn As Integer Dim currentCut As Integer Dim rowsToCut As Integer Sheets(4).Activate rowsToSkip = 10 totalCutsToMake = (ActiveSheet.UsedRange.Rows.Count / rowsToSkip) currentColumn = 1 Dim RowCount As Integer For currentCut = 1 To totalCutsToMake RowCount = Cells(Rows.Count, currentColumn).End(xlUp).Row Range(Cells(rowsToSkip + 1, currentColumn), Cells(RowCount, currentColumn + 2)).Select Selection.Cut … Read more
[ad_1] Merging cells in a spreadsheet means taking two or more cells and constructing a single cell out of them. When you merge two or more adjacent horizontal or vertical cells, the cells become one larger cell that is displayed across multiple columns or rows. When you merge multiple cells, the contents of only one … Read more
[ad_1] Want to concatenate multiple row data in in cell along with its values if present. Emp Id should not come if no data available infromt of it [ad_2] solved Want to concatenate multiple row data in in cell along with its values if present. Emp Id should not come if no data available infromt … Read more
[ad_1] As @BigBen said in the comments, it is down to your use of If…Else…If twice. The first correction is: If Result = True And Worksheets(“Sheet3”).Range(“C4”, Range(“C4”).End(xlDown)) <> “” Then Worksheets(“Sheet1”).Range(“C4”, Range(“C4”).End(xlDown)).Value = Worksheets(“Sheet1”).Range(“C4”, Range(“C4”).End(xlDown)). _ Value + Worksheets(“Sheet3”).Range(“C4”, Range(“C4”).End(xlDown)).Value ElseIf Result = True And Worksheets(“Sheet3”).Range(“D4”, Range(“D4”).End(xlDown)) <> “” Then Worksheets(“Sheet1”).Range(“D4”, Range(“D4”).End(xlDown)).Value = Worksheets(“Sheet1”).Range(“D4”, Range(“D4”).End(xlDown)). … Read more
[ad_1] Your setup as best I could understand it: And… This is the code I wrote: Option Explicit Option Base 1 Sub CopyData() Dim XLout As Workbook ‘Excel_Out.xls Dim XLin1 As Workbook ‘Excel_In1.xls Dim XLin2 As Workbook ‘Excel_In2.xls Dim ProductList ‘Product/Company List from XLin1 Dim ProductListO() ‘Concatenated Version of above Dim DataList ‘Product/Company List from … Read more
[ad_1] I think that should do the trick. This macro will take folder path from cell A1 and list recursively its contents and subfolder contents with hyperlinks. Update: fixed, now it’s working. 🙂 Public Position As Integer Public Indent As Integer Sub ListFileTree() Position = 0 Indent = 0 Call RecurseFolderList(Range(“A1”).Value) End Sub Private Sub … Read more
[ad_1] I’m not 100% sure what you are asking. You say “sum the number” but do not specify if the number you want to sum is the number of rows counted or if you want to sum the value of the cells found. -Edit- Give this a try: This will start at the bottom row … Read more
[ad_1] Try this, it makes use of a temporary PivotTable… Option Explicit Sub TestMakeTree() Dim wsData As Excel.Worksheet Set wsData = ThisWorkbook.Worksheets.Item(“Sheet1”) Dim rngData As Excel.Range Set rngData = wsData.Range(“Data”) ‘<—————– this differs for me Dim vTree As Variant vTree = MakeTreeUsingPivotTable(ThisWorkbook, rngData) ‘* print it out next to data, you’d choose your own destination … Read more
[ad_1] Here I assume the value to be found is in cell A1 and the retrieved data will be placed in cell B1 Sub RetrieveStuff() Dim v As Variant, rDest As Range, Nm As String Dim sh As Worksheet With ActiveSheet v = .Range(“A1”).Value Set rDest = .Range(“B1”) Nm = .Name End With For Each … Read more
[ad_1] This will delete rows that don’t equal a whole number when divided by 50 Sub Button1_Click() Dim FrstRng As Range, Lrw As Long Dim UnionRng As Range Dim c As Range Lrw = Cells(Rows.Count, “A”).End(xlUp).Row Set FrstRng = Range(“A2:A” & Lrw) For Each c In FrstRng.Cells If Int(c / 50) / (c / 50) … Read more
[ad_1] So the problem turned out to be that because this code was run from word, with the Excel library as a reference, the Range type defaulted to a MS-Word Range object instead of a MS-Excel Range object, meaning that my function was looking for the wrong type of parameter. Here is the corrected code … Read more