MrExcel Hot Topics
https://www.mrexcel.com
Recent top posts at MrExcel Forumenconsult@mrexcel.com (Bill Jelen)Tue, 22 May 2018 05:03:11 GMTTue, 22 May 2018 05:03:11 GMTExcel Of Coursehttps://www.mrexcel.com/img/mrexcel-logo.pngMrExcel Excel Forum Top 10 Posts
https://www.mrexcel.com
How do I determine an Array's index?
http://feedproxy.google.com/~r/mrexcel/YavB/~3/Na6B3jxaa3c/showthread.php
https://www.mrexcel.com/forum/showthread.php?t=1056414(13 responses) I'm toying around with using arrays instead of working in ranges. Here is a simple array Code: Sub dotest() Dim DirArray As Variant starttime = Timer DirArray = Range("c:t").Value For Each x In DirArray 'Debug.Print x Next MsgBox Round(Timer - starttime, 2) End Sub While I want to load the entire c:t, what if I wanted a conditional statement in the FOR loop such as: Code: if <== that syntax is wrong I know. for more go to rodericke.com/xlsuper<img src="http://feeds.feedburner.com/~r/mrexcel/YavB/~4/Na6B3jxaa3c" height="1" width="1" alt=""/>Mon, 21 May 2018 12:33:00 EDThttps://www.mrexcel.com/forum/showthread.php?t=1056414File Not Found Error in VBA Workbook_Open
http://feedproxy.google.com/~r/mrexcel/YavB/~3/_Rk1QQVrDSg/showthread.php
https://www.mrexcel.com/forum/showthread.php?t=1056052(13 responses) So I have a macro enabled workbook that when i open it it always says file not found and highlights workbook_open. But if i go to my one module and delete all the info in there and tab out a few areas of my code in the workbook_open then it will work. Then when I re add back that stuff and untab the stuff it works. Makes no sense to me lol. Here is the module and the thisworksheet module. I cannot for the life of me figure this out but I think it might have to do with one of the api's not being able to find the correct dll? Since it highlights the workbook_open and never triggers the error handler I cant figure out which file is not found. Code: Option Explicit Public Sub Workbook_Open() On Error GoTo ErrorHandler 'Speed up vba code Application.Calculation = xlCalculationManual Application.DisplayAlerts = False Application.ScreenUpdating = False 'Icon Update 'Call SetIcon(ThisWorkbook.Path & "\Images\LOGO.ico", 0) ' THis is one i tab out to fix error since routine is in module that i delete to fix error 'Seperate Instance Declaration Set ExcelGUIUpdates.App = Application 'Delete itself from history Call ExcelGUIUpdates.DeleteRecentlyOpened Set fso = CreateObject("Scripting.FileSystemObject") 'Create Regex Object string testing Set RegEx = CreateObject("VBScript.RegExp") 'Application.DisplayAlerts = True<img src="http://feeds.feedburner.com/~r/mrexcel/YavB/~4/_Rk1QQVrDSg" height="1" width="1" alt=""/>Thu, 17 May 2018 13:11:00 EDThttps://www.mrexcel.com/forum/showthread.php?t=1056052copy 4 columns into one, missing empty or #VALUE! cells
http://feedproxy.google.com/~r/mrexcel/YavB/~3/rgXEqWkWHh8/showthread.php
https://www.mrexcel.com/forum/showthread.php?t=1056360(11 responses) Hi folks, i have 4 columns of data that vary in length to copy to one column and i cant figure out a loop to get the 2,3 or 4 columns copied to the bottom of the first i have this so far Dim x As Integer Dim y As Integer x = 1 y = 2 Do Until Worksheets("Sheet4").Range("A" & x) = "" Worksheets("XML").Range("A" & y) = Worksheets("Sheet4").Range("A" & x) y = y + 1 x = x + 1 Loop any guidance much appreciated. Carl<img src="http://feeds.feedburner.com/~r/mrexcel/YavB/~4/rgXEqWkWHh8" height="1" width="1" alt=""/>Mon, 21 May 2018 04:53:00 EDThttps://www.mrexcel.com/forum/showthread.php?t=1056360Copy Data to multiple Worksheet Based on cell values
http://feedproxy.google.com/~r/mrexcel/YavB/~3/VFKBNY9Kh-A/showthread.php
https://www.mrexcel.com/forum/showthread.php?t=1056393(11 responses) Hi All, I have an workbook. and below sheets contains :- 1) RAMSES : this is main sheet from where I need to copy only column F and paste in multiple sheets. based on cell value mentioned in column C. 2) CTA : data needs to paste in this sheet if any cell value of column C in Ramses is mentioned as "CTA" 3) IDF : data needs to paste in this sheet if any cell value of column C in Ramses is mentioned as "IDF" 4) MED : data needs to paste in this sheet if any cell value of column C in Ramses is mentioned as "MED" 5) NOE : data needs to paste in this sheet if any cell value of column C in Ramses is mentioned as "NOE" 6) SWT : data needs to paste in this sheet if any cell value of column C in Ramses is mentioned as "SWT" 7) WST : data needs to paste in this sheet if any cell value of column C in Ramses is mentioned as "WST" 8) BLANK : data needs to paste in this sheet if any cell value of column C in Ramses is mentioned as "#N/A" or any Blank Cell in column C in Ramses. I have attached the sample file as well in below link. https://1drv.ms/x/s!Ap80Ku6M2Tw5gTbGRwA5Q6nGCIGO Thanks in advance for your valuable support. Regards, Rajender<img src="http://feeds.feedburner.com/~r/mrexcel/YavB/~4/VFKBNY9Kh-A" height="1" width="1" alt=""/>Mon, 21 May 2018 10:01:00 EDThttps://www.mrexcel.com/forum/showthread.php?t=1056393Nesting IF functions with SUMPRODUCT to create a TOTAL option in a custom list
http://feedproxy.google.com/~r/mrexcel/YavB/~3/1ZTCqjAG9lI/showthread.php
https://www.mrexcel.com/forum/showthread.php?t=1055870(10 responses) First off, this is my first time posting, so sorry if I'm missing any important guidelines or formatting! I've been working on a small tool at work that I'm trying to make as turnkey as possible. I'm attempting to use the SUMPRODUCT function to parse my data by two separate custom lists I've created. As with custom lists, the "total" option has to be written manually into the formula, which I was able to achieve with just one custom list. Adding the second one is a little more difficult, as the formula keeps returning 0. The logic I want this formula to follow is to just return the total for the data being pulled if both lists = "total." I'm also trying to make this formula dynamic in the sense that if one of the drop downs = "total," one can still parse out the data further with the other drop down. Any and all help is appreciated on where I am going wrong! My hunch is that I may need one more IF statement, but I'm unsure. Thank you all in advance! The formula that works is as follows: =IF($B$1="Total",SUMPRODUCT(--('Gap Analysis data Pull'!$J$2:$J$362=Matrix!$B$2)*--('Gap Analysis data Pull'!$D$2:$D$362>Matrix!$A6)*--('Gap Analysis data Pull'!$D$2:$D$362<=Matrix!$B6)*--('Gap Analysis data Pull'!$D$2:$D$362<>""))/SUMPRODUCT(--('Gap Analysis data Pull'!$A$2:$A$362<>"")*--('Gap Analysis data Pull'!$D$2:$D$362<>"")),SUMPRODUCT(--('Gap Analysis data Pull'!$J$2:$J$362=Matrix!$B$2)*--('Gap Analysis data Pull'!$I$2:$I$362=Matrix!$B$1)*--('Gap Analysis data Pull'!$D$2:$D$362>Matrix!$A6)*--('Gap Analysis data Pull'!$D$2:$D$362<=Matrix!$B6)*--('Gap Analysis data Pull'!$D$2:$D$362<>""))/SUMPRODUCT(--('Gap Analysis data Pull'!$A$2:$A$362<>"")*--('Gap Analysis data Pull'!$D$2:$D$362<>""))) I've attempted to next another IF statement into this formula, which looks like this: =IF($B$1="Total",SUMPRODUCT(--('Gap Analysis data Pull'!$J$2:$J$362=Matrix!$B$2)*--('Gap Analysis data Pull'!$D$2:$D$362>Matrix!$A5)*--('Gap Analysis data Pull'!$D$2:$D$362<=Matrix!$B5)*--('Gap Analysis data Pull'!$D$2:$D$362<>""))/SUMPRODUCT(--('Gap Analysis data Pull'!$A$2:$A$362<>"")*--('Gap Analysis data Pull'!$D$2:$D$362<>"")),IF($B$2="TOTAL",SUMPRODUCT(--('Gap Analysis data Pull'!$J$2:$J$362=Matrix!$B$2)*--('Gap Analysis data Pull'!$D$2:$D$362>Matrix!$A5)*--('Gap Analysis data Pull'!$D$2:$D$362<=Matrix!$B5)*--('Gap Analysis data Pull'!$D$2:$D$362<>""))/SUMPRODUCT(--('Gap Analysis data Pull'!$A$2:$A$362<>"")*--('Gap Analysis data Pull'!$D$2:$D$362<>"")),SUMPRODUCT(--('Gap Analysis data<img src="http://feeds.feedburner.com/~r/mrexcel/YavB/~4/1ZTCqjAG9lI" height="1" width="1" alt=""/>Wed, 16 May 2018 11:10:00 EDThttps://www.mrexcel.com/forum/showthread.php?t=1055870