User Profile
kheldar
Iron Contributor
Joined Aug 31, 2021
User Widgets
Recent Discussions
OneDrive: We Couldn’t Merge The Changes in Office File
Hello, I'm not sure if this feature is used by many but I do. I access my company's SharePoint and collaborate with my team mates on Excel files using OneDrive. However, there's an annoying bug while using this feature. I've Googled just a bit and found out others are facing this issue as well. Also, all the users in company who I made try the feature experiences this issue and I've got two different computers. I resort to this community because basic troubleshooting like resetting OneDrive and repairing Office etc. don't work. I've got AutoSave enabled. If the file I'm editing is open by another user(all of them access the files using Excel for web), after closing the document, I always get this error in OneDrive notification tray "OneDrive: We Couldn’t Merge The Changes in Office File". I open the document manually save it, still the same error. I think it saves but I just get this error. Do you have any insight on this? Thank you.Loop Through An Array of Dates
Hello, I'm a beginner in VBA so please bear with me. I'll have a table that'll contain thousands of raw data. Resolve time is the name of the column that I need to work with. I've assigned it to a named range. This is how it looks like: It's formatted as dd/mm/yyyy hh:mm:ss I'm in need of a specific operation that'll use an array and loop through the elements of that array. At least that's what I think I need. What It needs to do: assign the whole column to an array, loop through every employee's record(Resolve Time) and "do sth" if one subtracted from another and the result is less than AHT of the first case for the same employee. The real life application for this macro so you can have a clearer picture: We do content moderation. The raw data source includes the cases they moderate and their resolve times. If an employee's record indicates overlapping values like the one I highlighted in the picture, it means they are moderating two cases at the same time which is what we don't want. So the next case they take should be at a time longer time than their AHT(Average Handle Time). In this case: Employee X finished moderating a case at 23:58:30 with 14 secs AHT and another one at 23:58:37. Second case, with 14 secs AHT of the first case should have been taken at 23:58:44 but it was taken at 23:58:37 which tells us that he was moderating two cases at the same time. Now the macro needs loop through every record for every employee and "do sth" if such cases found. The formula should be : IF (Second Case Resolve Time) - (First Case Resolve Time) is less than AHT of the first Case "Do sth" The thing to note is that: since it'll contain records for other employees as well, the operation needs to work with every employee individually. This is my incomplete macro: Sub Identifier() Dim Raw As Worksheet: Set Raw = Sheets(2) Dim RTArr() As Variant Raw.Activate RT = Range("RT").Value End Sub I'm attaching a sample raw data source. I hope I was clear enough. If not please tell me so I can try to explain better. I'd really appreciate your help Thanks.Filter Function Skip Every 9 Cell
Hello, I need the result array of this filter formula to skip every 9 cell for the sake of argument. =FILTER(Bucket[Name],Bucket[DM]=C1) Let's assume the formula is in A1 so the function needs to populate a1,a10,a19,a28 and so on skipping the cells in between. Also I don't really need to use FILTER. It can be any other dynamic array method. I've tried googling extensively but my keyword choosing skill is kinda poor and as you know Google likes to show you only the top results instead of the really relevant ones. I'd really appreciate your assistance. Thanks in advance.VBA and SharePoint URL Longer Than 255 Characters
Hello, Is there a workaround to bypass this error? Sub CallFile(ByVal File As String) Workbooks("Master Workbook.xlsm").Activate Debug.Print File Workbooks.Open File End Sub This is my macro. The argument that's passed to the macro through a command button is longer than 255 characters and I'm getting an error. URLs that are shorter than 255 characters work without a problem. I'd really appreciate your assistance.Macro to Retrieve Values From A Password Protected Workbook on SharePoint Without Opening It
Hello, I'm relatively new to VBA so please bear with me. I'm in need of a macro to retrieve data from a password protected workbook that's on SharePoint. The masterbook that I'm working on will retrieve the values from a sheet that's inside the protected workbook and set values of a sheet inside the masterbook accordingly. It needs to run without any user prompt and without opening the workbook. I'm familiar with basics of VBA. Working with objects and such. I can do this by opening the workbook but It's not what I need. This is what I could find on Google. Sub Dummy() Dim rgTarget As Range Set rgTarget = ActiveSheet.Range("G2:H10") 'where to put the copied data. rgTarget.FormulaArray = "='D:\[Product_Details.xlsx]Sheet1'!$B$4:$E$10" rgTarget.Formula = rgTarget.Value End Sub I'm not sure if it'd would work with a SP address and I'm not sure where to put the password. I'd greatly appreciate your assistance. Thank you.Export To SharePoint List - An Unexpected Error has occurred
Hello, I wasn't sure if I should create this topic in SharePoint section but since I'm getting this error on Excel, I've decided to create it here. I'm trying to get started with SharePoint lists. However, I have hit a road block before even starting. I've googled extensively before creating this thread. None of the articles were helpful and I gave up on Google. Details: I created a dummy file with a dummy table. No special characters anywhere. Uploaded it my SP. This is the error I get when I try to export the table to SP Lists. I then contacted my IT to check if I had permission. I didn't. Then I was granted permission. I can create lists manually on SP. I can even import Excel files with tables through the browser. However, I still get the same error using Excel. Since the error doesn't indicate anything else I couldn't expand my research. What I've tried so far: Sign out and sign in. Removed my credentials from Credentials Manager on Windows and sign in again Tried changing file format to XLSM or XLSX Made sure there are no special characters in the table headers. Validated my SharePoint address through my IT. I can also access my SP using PowerQuery with the same URL. Unmarked "Ignore other applications that use Dynamic Data Exchange (DDE)" in Excel options Ran gpupdate /force through elevated command prompt. Note: I'm accessing my SharePoint using OneDrive. I'm in need your assistance. Thanks.wb.Name Like VBA Not Working the first time
Hello, I had actually created another topic for this issue then thought it was fixed. However, turns out it's not. The code below doesn't work first time the workbook is opened. If I close it and re-open it works. How can I fix this? Sub CopyRAWMMP() Application.DisplayAlerts = False Dim Ct As Long For Each wb In Application.Workbooks If Trim(LCase(wb.Name)) Like "data-*" Then Ct = Ct + 1 wb.Activate Exit For End If Next wb If Ct = 0 Then MsgBox "MSG" Exit Sub End If Range("A2:N2").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Application.CutCopyMode = False Selection.Copy Workbooks("Real Time.xlsm").Activate Worksheets("Raw MMP").Activate Range("RawMMP[Date]").Select ActiveSheet.Paste Application.CutCopyMode = False Application.DisplayAlerts = True End Sub Immediate window doesn't recognize that the workbook is opened. I was advised that the workbook is opening in a different instance. But I don't even know what that means. I'd really appreciate your help.VBA - UserForm - Local Language
Hello, I created an Excel file that'll be used on different computers with different regional settings. Some of them are set to the local language some of then are on English. For the ones that are on English, the code written in VBA doesn't display text values in the local language so there are issues with special characters. Please note that this is not an editor issue. Hardcoded text values are not displayed correctly in Excel. Is it possible to force VBA to treat the code in a specific language? From what I understand it's possible fix the issue by changing non-unicode language to the local language but it's not possible for us that change that setting on our computers. Thanks.Recursive Lambda Confusion
Hello, I'm trying to build a Schedule Generator using recursive Lambda based on RANDBETWEEN function. Basically, what it needs to do is : 1: Generate a random date using RANDBETWEEN based on 2 other cells which includes start and end of the month. 2: Lookup A2#'s shift on that day, if it's not "OFF", set the cell's value to that date. If it's off, generate another date. This is my messy formula. Of course it does not work because I still am not sure how recursive lambdas work. Also, I believe there is a logical error in the formula. =LAMBDA(f,l,counter,LET(randomd,IF(counter>70,"",RandomDate(RANDBETWEEN(f,l),counter+1)),shiftlookup,TEXT(XLOOKUP(a2#,Names,XLOOKUP(randomd,Dates,Schedule!$B$3:$AE$67)),"hh:mm"),IF((shiftlookup="08:00")+(shiftlookup="16:00")+(shiftlookup="18:00"),shiftlookup,randomd+1))) As of now, this Lambda doesn't let me call it using cell references. The exit condition of the loop needs to be IF(A2#="","",Lambda) I'm not sure if I set it up right. I'm attaching a sample file for you to see what I'm working on. Note: There is a simple macro embedded to calculate the sheet but I saved it as a macro-free workbook. I'd really appreciate your help.Re: Lambda Without Paramaters N/A Error
mtarler This is how it's entered in name manager. =LAMBDA(LET(calc,ModStart('Real Time'!$S$3,'Real Time'!A2#),IFS(ModMatchStart('Real Time'!$S$3,'Real Time'!A2#)>0,calc,ModMatchStart('Real Time'!$S$3-1,'Real Time'!A2#)>0,ModStart('Real Time'!S3-1,'Real Time'!A2#)/1440, ModMatchStart('Real Time'!$S$3-2,'Real Time'!A2#)>0,ModStart('Real Time'!S3-2,'Real Time'!A2#)/1440,ModMatchStart('Real Time'!$S$3-3,'Real Time'!A2#)>0,ModStart('Real Time'!S3-3,'Real Time'!A2#)/1440, ModMatchStart('Real Time'!$S$3-4,'Real Time'!A2#)>0,ModStart('Real Time'!$S$3-4,'Real Time'!A2#)/1440,ModMatchStart('Real Time'!$S$3-5,'Real Time'!A2#)>0,ModStart('Real Time'!$S$3-5,'Real Time'!A2#)/1440, ModMatchStart('Real Time'!$S$3-6,'Real Time'!A2#)>0,ModStart('Real Time'!S3-6,'Real Time'!A2#)/1440,ModMatchStart('Real Time'!$S$3-7,'Real Time'!A2#)>0,ModStart('Real Time'!$S$3-7,'Real Time'!A2#)/1440, ModMatchStart('Real Time'!$S$3-8,'Real Time'!A2#)>0,ModStart('Real Time'!$S$3-8,'Real Time'!A2#)/1440, ModMatchStart('Real Time'!$S$3-9,'Real Time'!A2#)>0,ModStart('Real Time'!$S$3-9,'Real Time'!A2#)/1440))) I enter the formula as : =LAMBDA(LET(calc,ModStart($S$3,A2#),IFS(ModMatchStart($S$3,A2#)>0,calc,ModMatchStart($S$3-1,A2#)>0,ModStart(S3-1,A2#)/1440,ModMatchStart($S$3-2,A2#)>0,ModStart(S3-2,A2#)/1440,ModMatchStart($S$3-3,A2#)>0,ModStart(S3-3,A2#)/1440,ModMatchStart($S$3-4,A2#)>0,ModStart($S$3-4,A2#)/1440,ModMatchStart($S$3-5,A2#)>0,ModStart($S$3-5,A2#)/1440,ModMatchStart($S$3-6,A2#)>0,ModStart(S3-6,A2#)/1440,ModMatchStart($S$3-7,A2#)>0,ModStart($S$3-7,A2#)/1440,ModMatchStart($S$3-8,A2#)>0,ModStart($S$3-8,A2#)/1440,ModMatchStart($S$3-9,A2#)>0,ModStart($S$3-9,A2#)/1440)))() But name manager automatically converts cell references to cell references including sheet references as displayed above.
