Rtyjn

Excel VBA to stack columns in different sheets I have an Excel workbook with 2 sheets inside. Both sheets have tables that can change in length (row count). I want to be able to have VBA take table1 in sheet1 and align it within table2 on sheet2. Here's where it gets tricky. The columns do not line up. For example, "Company" in sheet1 might be in column E. However, in sheet2 it's in column V. There are also blanks in the columns. Which is ok as far as data goes, but it's going to make getting to the end of the table's column harder. I know I'm going to need to list out which columns go where for each column. In sheet1 there are 36 columns. In sheet 2 there are 49. This is perfectly acceptable. Any guidance would be greatly appreciated. It takes an insane amount of time to do this manually and I'm just trying to speed it up. If it's easier to get everything to compile on sheet3, that's fine. Here is what I have so far, but it's not liking th...

VBA Count occurrence of an element in a multi-dimensional array, how? I have defined a multi-dimensional array using range in vba, for example Dim Arr() as Variant Arr = Range("A1:F5") This resulted the a 5x6 array Arr(1,1) to Arr(5,6) I want to count the occurrence of a string, say "ABC" in Arr(5) (i.e. Row 5) only. The following code can find the count of "ABC" in the all of the array For Each x in Arr if x = "ABC" then Cnt = Cnt + 1 Next But if only want to count dimension 5, Arr(5) return an error. Arr is Arr(1 to 5, 1 to 6). Which 5 are you talking about? – Jeeped Jun 30 at 9:22 2 Answers 2 Sub FindABC() Dim Arr(), cnt, x Const ROW_NUM = 5 Arr = Range("A1:F5") For x = 1 To UBound(A...

Is there a faster CountIF As the title says. Is there any function or VBA code which does the same function as a countif and is a lot faster. Currently in the middle of massive countif and it is just eating up my CPU. It is just a basic countif inside the worksheet. Not in VBA. =countif(X:X,Y) However the lists are massive. So both lists are around 100,000~ rows =countif(X:X,Y) Not sure if it's faster or not, but you could try filtering the column on the IF part, then get Range.SpecialCells(xlVisible).Count . Note: not 100% certain xlVisible is the proper enum, but you get the idea. – FreeMan Apr 30 '15 at 15:39 IF Range.SpecialCells(xlVisible).Count xlVisible All I am trying to do is to see if x list is in y list. I just want any zeros. So not sure if I can split it that way – Sam ...

VBA to get the href value I am writing macro to extract the href value from a website, example here is to extract the value: '/listedco/listconews/SEHK/2015/0429/LTN201504291355_C.pdf' from the html code below. The href is one of the attributes of the html tag 'a', I have add the code getElementbyTagName'a' but it did not work, my question is how to extract that href value to column L. Anyone could help? Thanks in advance! <a id="ctl00_gvMain_ctl03_hlTitle" class="news" href="/listedco/listconews/SEHK/2015/0429/LTN201504291355_C.pdf" target="_blank">二零一四年年報</a> Sub Download_From_HKEX() Dim internetdata As Object Dim div_result As Object Dim header_links As Object Dim link As Object Dim URL As String Dim IE As Object Dim i As Object Dim ieDoc As Object Dim selectItems As Variant Dim h As Variant Dim LocalFileName As String Dim B As Boolean Dim ErrorText As St...

VBA Chart Blank Values in Cell Range How the cart should be displayed with information, it is being displayed correctly because I added a zero value in cell B16 The error chart, not displaying properly because mortgage1 doesn't have a zero value in its cell How the macro is being run and information from user form Where my information lives from the userform I have a chart that is generated with a macro for a range of cells. At times some blank cells will exist within that range. I'm facing a problem when generating my chart with an empty cell value, the chart will start plotting at the first non empty cell, causing the category axis to not be reporting correct. How can I make my chart show a blank value on my chart using VBA. I would prefer not to use a formula on my cell range. Here is my code: Sub createchart() Dim wb As Workbook: Set wb = ThisWorkbook Dim ws As Worksheet: Set ws = wb.Sheets("contactunder") Dim CellRow As Integer wb.Sheets("ContactsFront...