Tag: arrays

error when using an array to filter on sheet

I have a section of code that’s supposed to filter to show everything that’s not specific data and it’s giving me trouble: Sheets(“main”).Select Range(“A1”).Select Range(“A1:N1”).AutoFilter Field:=1, Criteria1:=Array(“<>PXC532”, “<>PXX338”, “<>PZR290”, “<>QBQ519”, “<>QBZ561”, “<>QCB023”, “<>QCF365”, “<>QCF386”, “<>QCG343″), Operator:=xlFilterValues it’s highlighting “Range(“A1:N1″)…” and stating autofilter method of range class failed, think it’s just something I’m overlooking but have […]

Php – Read Excel and Strore to Db

error_reporting(E_ALL); ini_set(‘display_errors’, TRUE); ini_set(‘display_startup_errors’, TRUE); $inputFileName =”../brief/phaseupload/” . $filename; // Read your Excel workbook try { $inputFileType = PHPExcel_IOFactory::identify($inputFileName); $objReader = PHPExcel_IOFactory::createReader($inputFileType); $objPHPExcel = $objReader->load($inputFileName); } catch(Exception $e) { die(‘Error loading file “‘.pathinfo($inputFileName,PATHINFO_BASENAME).'”: ‘.$e->getMessage()); } // Get worksheet dimensions $sheet = $objPHPExcel->getSheet(0); $highestRow = $sheet->getHighestRow(); $highestColumn = $sheet->getHighestColumn(); // Loop through each row of the […]

multidimensional arrays excel

Hi I have a loop that works, but it takes hours to run due to the size of the data. I am trying to use an array, which I have never used before. Based on all of the tutorial information I have looked through I think my array is correct, but it is not giving […]

Excel – VBA Converting matrix entries to percentages of row total

I have a matrix with an equal number of rows and columns of size numColors. The following code attempts to convert each entry from an integer to a percent of its row total. I receive a “Subscript out of range” error and need help fixing it. Thanks. For i = 2 To numColors + 2 […]

Excel: Array to List (Combine time columns with date rows)

In excel, I have a table with dates as rows and hours (0-24) as columns, and values at their intercept. I would like to change this array (365 by 24) to a list such that: Monday Hour1 value; Monday Hour2 value; Monday …; Monday Hour24 value; Tuesday Hour1 value; Tuesday … ; Tuesday Hour 24 […]

Spreadsheet Array Formula Tweak

Trying to tweak this array formula =INDEX(D26:W26,MATCH(MAX(COUNTIF(D26:W26,D26:W26)), COUNTIF(D26:W26,D26:W26),0)) & ” Winner” It works and returns AAA Winner It is for a contest – This counts name entries in each cell and returns which person has the most votes in the range. What I would like it to do is to put the text Winner first […]

Pass array to an excel single cell from Python using xlwings

Is it possible to pass an array from python using xlwings into a single cell in excel? When I attempt to pass a numpy array as a Range.value excel creates a horizontal range with each value in its own row. My code is similar to this: import xlwings as xw import numpy as np xlist […]

swapped rows vba code in excel

I’m trying to generate combinations id_users in excel that they have a relation..so I used this sample code to gave me this result: the input is a pivot table : ‘ id_row id_users 10 1 2 3 66 4 11 this is my output ‘source target label 1 2 10 1 3 10 2 3 […]

DrillDown to Array

I want to know whether or not it’s possible to achieve the following: On a double click on the pivot table’s value items, a data drill down table in a new sheet is created. I am now looking for a way to bypass the double click (via an event handler) and take the data and […]

VBA Joining arrays as if they were relational database tables

I have two arrays in my VBA code at the moment. They are structured very much like a database table except they are horizontal, so the first three rows (of 7) effectively determine the primary key within the array. I’d like to perform OUTER and INNER join operations on them as if they were database […]

MS Excel Spreadsheet is the best Office Software, Excel VBA and Excel Formulas make Spreadsheet work faster.