What I'm trying to do. I have two separate requirements:
(1) To add the whole array to a listbox on form initialization - see Sub UserForm_Initialize()
(2) To clear the listbox and re-add only certain items based on what's typed in a textbox - see Sub txtSearchTerm_Change()
I have two errors:
Error 1 in UserForm_Initialize()
The listbox contents need transposing! It is displaying as
Code:
1 2 3 4 5
Tom Ben Heidi Julie Mark
Smith Jones Evans Simpson Petersen
x@yo.com a@bo.com c@do.com e@fo.com g@ho.com
02071001022 02071001026 02071001027 02071001028 02071001029
Friend
When it should be displaying as
:
Code:
1 Tom Smith x@yo.com 02071001022 Friend
2 Ben Jones a@bo.com 02071001026
Is there a way to transpose the array?
Error 2 in txtSearchTerm_Change()
I cannot find anywhere - even on MSDN - all the information I need how to correctly add a single record to a multiple-column listbox! What I'm trying is:
Code:
For i = 0 To UBound(SearchList) If InStr(1, SearchList(i), SearchTerm) 0 Then
With lstPeople
.AddItem
For j = 0 To UBound(PeopleList, 1)
.List(c, j).Value = PeopleList(j, i)
[Code] ......
How do I add a record to the listbox????
Full code for reference:
Option Explicit
Private PeopleList As Variant
Private SearchList As Variant
Private Sub UserForm_Initialize()
I have a long list of data with many columns and I'd like all the information to be in one column without manually copying and pasting each column and adding to the first column. The data has different amounts of rows and columns as well. An Example is below. I'm using Excel 2010. Is there a formula or something for this? This isn't the data I'm using but just an example since I do this frequently.
I am trying to populate a 3 column listbox in a userform from SQL Server via ADO. When the result set consists of more than one record, there is no problem and the data is displayed properly (ie each piece of data is in its appropriate column) eg...
however when the recordset returns only a single record, the data does not transpose and views as below (ie each piece of data in the record is on a different line in the first column
I'd like to apply multiple formula to a set of cells on a summary page. My summary page also contains 3 variable dropdowns, and I'd like to display data based on the text selected in those dropdowns (pulling data from 2nd tab "Variables")
The following formula works in the first instance:
=IF(AND(H4="Product Type A1", H6="External", H8="Existing"), Variables!C4, 0)
What I'm struggling to do is add additional formula to the same cell in order to deal with the remaining eventualities of the drop down variables:
Variable 1: Product Type A1 Product Type A2 Product Type B1 Product Type B2
I'm using Excel 2010 and I'm having troubles displaying hiding my column B if none of the cells of column A contains either "(Quasi Echec)" or "(Quasi Russite)" at least once.
PHP Code: [URL] ......
Below are my 3 attempts.
Code: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim c As Range If Target.Column 1 Then Exit Sub Application.EnableEvents = False On Error Resume Next
[Code] ..........
Code: Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Sub GetUniquesInColA() Dim rng As Range Dim c If Target.Column 1 Then Exit Sub On Error Resume Next
[Code] .........
Code: Option ExplicitPrivate Sub Worksheet_SelectionChange(ByVal Target As Range) Dim x As Long Dim LastRow As Long LastRow = Range("A65536").End(xlUp).Row
At work, we've got a program that outputs the results of a search into an Excel file, in column 1 below.
17,43,61,63 17 43 61 63
23,29,53,57,77,79 23 29 53 57 77 79
17,29,63,69,71,75,79 17 29 63 69 71 75 79
11,43 11 43
57 57
I need to get that list of numbers listed out to the right, with one number per cell. The list in column one could possibly contain from 1 to 20 numbers, and the last number is always without the comma after it.
We have a master worksheet, and I cant let staff see it, but I want them to update 'their bits' themselves, rather than email the admin staff and let them update the master sheet..
every job is on its own row and contains a unique ID.
is there a way i can merge their data in to the correct row and column(s) on the master sheet?
1 workbook, 2 worksheets (or tabs). On tab 1, I want a formula/alert that tells the user if any duplicate values exist in Column A of tab 2
Tab 2, Column A, has Unique ID's (6 digit numeric values)
The user manually inputs the ID's on new rows in Column A
Row 1 is reserved and in use for something else Row 2 is my header, so cell A2 says "ID" Row 3-623 currently contain unique ID's
When the user inputs a new ID into cell A624, then they return to Tab 1, I want my formula/alert on Tab 1 to tell the user that they have duplicates in Column A of tab 2. I know the Conditional Formatting, but if the user copies in 100 new values, they won't necessarily see the highlighted cells. My tab 1 is my "checks and balances" and the last place the user is suppposed to look to ensure that they haven't created any duplicate ID's. If the user sees a warning message that says duplicates exist, then I'll tell them that they need to look at column A (for cells that have been conditionally highlighted).
One issue that I'm running into with the conditional highlighting is that I want cells A3:A1048576 to already have the conditional formatting - this way when the user inserts a value into Cell A624, then A625, etc they conditional formatting is already there. Right now with data in cells A3:A623, cells A624:A1048576 are all highlighted with the Red/Bold Red Font (which is okay I guess), but ideally it would be nice to not count 2+ empty cells as duplicates and I'll have to have my formula on Tab 1 not include the blank cells.
I DO NOT want to use the Remove Duplicates feature of Excel 2010. If I remove them I could be removing data in columns B, C, D, etc that belong to the Unique ID. I just need the user to be told in Tab 1 that they DO have duplicates and I'll train the user how to research this and fix it.
The reason I want to look for duplicates in the entire Column A is because the list of Unique ID's will grow over time.
I am trying to compare values of an item one year vs the other in excel 2010. So my spreadsheet would look like:
A B C D 028042$500,000.00028042$500,000.00 028349$300,000.00028349$300,000.00 028390$300,000.00028390$300,000.00 028498$100,000.00028498$100,000.00 029477$135,140.00029477$135,140.00 030374$452,865.45030374$452,865.45 030375$452,865.45030375$452,865.45
What I am trying to do is compare A and B vs C and D to see if the cost associated with the number in A is the same as it is in C(which is obvious it is in this example but I have a spreadsheet with thousands of entries). Additionally, the AB value might be anywhere in the CD columns not just in the row next door.
how do I save multiple items selected from a userform listbox to a single cell on a worksheets? How do I have those values display in a listbox on a userform for editing data sets?
What is the VBA code for inserting text in all column B-cells of multiple selected rows?
I am creating a worksheet with a table containing various data related to orders of various materials (this is more or less data gathering from an older, paper-based 'system'). This table spans, columnwise, from A to D and expands downwards as more orders are added. The information in each column is: A=order number, B=type of material and C=material specs. and D=additional comments.
I've set it up so that the only thing I really have to do is to insert the type of material in the cells of column B, and the rest will sort itself out. Instead of having to insert a new row for each new entry and manually typing in the name of the material (these entries are often done in the midst of already existing data), I created several similar, macroed buttons for the different types of materials we use. These macros work by selecting the row of the currently active cell, inserting a new row and then add the name of the material in the column B-cell of this new row. What I am having trouble doing though, is to get the text-entry to work for a selection of multiple cells.
As an example, lets say that I would like to add 5 orders of "Grade A Steel" in the middle of the table - in the row above row 8. With the macro I currently have I can select cell B5, click the macro, and a new row will be inserted with "Grade A Steel" in column B of this new row. This action could be performed 5 times over, but would be easier if I could just mark a range of 5 cells, say B8:B12, click the macro and get the text/data inserted the column B-cells of all 5 of the new rows. So far I've been able to create a macro that inserts multiple new rows, but I've only been successful in filling the column B-cell in the first row leaving the 4 below empty.
I'm trying to create a single array from multiple ranges... I'm not sure what syntax to use:
Code: Dim dat4() As Variant Set r = Sheet13.Range("rsqlassetid") Set r2 = Sheet13.Range("rsqlparentcat") dat4() = (r , r2)
I can create an array with multiple columns from a range if the columns are next to each other but in this instance they're not.
These 2 ranges both have the same number of rows and I'm trying to combine them into a 2 column array, but not sure how to make it work without looping, rediming the array and using a secondary array to preserve the data...
Ok I'm writing an extraction formula. I've got my array of Trues and Falses, but now I need to go through each row and check if all the columns are TRUE, and then return the row if all the columns are true.
Right now I'm just working with a dummy set of data... my real table is some 50 columns wide and over 300 rows, but this gives you the idea of what I'm doing:
I tried putting in a Countif and using it like and array to count the different rows and return the number of times each row was returned... but countif goes nuts when you try an array for the criteria.
I have a worksheet with a few "Multi-Select" type listboxes in it. I cannot seem to select the items within those listboxes... the mouse icon doesn't even change, it just shows that little plus-sign that is the default when you're just in your worksheet. (they're also ActiveX listboxes)
I can select them if I go into my Developer tab and select and deselect the "Design Mode" button. then everything works fine (mostly). Or if I resize the subwindow for the workbook. Then it (again, mostly) works fine.
It's like I have to trick Excel into allowing me to click on the checkboxes in my ListBox.
Some people I've given the file to use Excel 2007 and the file works for them. (I have Excel 2010)
I've looked up info on bound/unbound forms, but I can't tell if it applies, or how to use that. They were not created with a VBA macro, they were made using the buttons in the developer tab.
One final thing : Below the listboxes, I have some shapes that, when pressed, will take the selected items from their respective checkboxes and put them into some cells.
Using Excel 10, Win 7, a User Form with list box control, I only need one column about 7 characters long. (At this point my horizontal scroll bar is taking up more room than the list box window so I'd really like to lose it). I don't see any property options for scroll bars to set.
I am building a marketing dashboard that shows the effectiveness of two campaigns based on where the campaign has been deployed (website, email, facebook, twitter, google+ etc.). In each campaign I have a checklist (using developer ribbon) with these different dimensions.
I want a formula that checks the checklist, identifies all the boxes that are unticked and returns them in a single cell. The cell would read:
To increase traffic to the Shampoo campaign (Campaign A), expose it to Google+ and Facebook as these channels generated 578 and 2009 visitors respectively for the Makeup campaign (Campaign B).
I already have it working for returning a single value with the following formula but need it to return multiple values.
=IFERROR("To increase traffic to the "&B4&" campaign, expose it to "&INDEX(L4:L10,MATCH(K5,M4:M10,FALSE))&" "&"as"&" "&"this channel generated "&VLOOKUP(INDEX(L4:L10,MATCH(K5,M4:M10,FALSE)),L26:N31,2,FALSE)&" visitors for the "&D4&" campaign","")
I am trying to rank records from multiple sections of a single array. Since there are over 100 sections I want to use a formula. Using the MATCH function I have the first row for each section of the array (Col D below). The psuedo code of what I'm trying to do is:
RANK(B1,Bfirst row of section:Bfirst row of next section -1)
Example (results in Column C):
A B C D 1 West 100 3 1 2 West 150 1 4 3 West 125 2 4 East 50 3 5 East 75 2 5 East 140 1
I will also need to increment the section as I complete each section of the array. Am I trying to do too much in a single cell??
I am building a marketing dashboard that shows the effectiveness of two campaigns based on where the campaign has been deployed (website, email, facebook, twitter, google+ etc.). In each campaign I have a checklist (using developer ribbon) with these different dimensions.
I want a formula that checks the checklist, identifies all the boxes that are unticked and returns them in a single cell. The cell would read:
To increase traffic to the Shampoo campaign (Campaign A), expose it to Google+ and Facebook as these channels generated 578 and 2009 visitors respectively for the Makeup campaign (Campaign B).
I already have it working for returning a single value with the following formula but need it to return multiple values.
=IFERROR("To increase traffic to the "&B4&" campaign, expose it to "&INDEX(L4:L10, MATCH(K5,M4:M10,FALSE))&" "&"as"&" "&"this channel generated "&VLOOKUP (INDEX(L4:L10,MATCH(K5,M4:M10,FALSE)),L26:N31,2,FALSE)&" visitors for the "&D4&" campaign","")
I know something very similar was posted before, but unfortunately, it wasn't what I was looking for. I have a Worksheet tracking several associates and the department they have done work in. (Our associates are contractors for other departments). I need to have a final worksheet that allows me to pull up a name, and display every dept they worked with in the past week.
Hope this is fairly easy to solve. I have Screen #1 with a listbox with a CLICK event. The event populates a combobox below (with items based on listbox's selection). Listbox is NOT multi-select. Code runs fine manually, i.e., user opens screen selects from listbox, then can select from combobox.
I now what to open and make selections from another form/screen, Screen #2. I've written code to select the proper item from Screen #2's listbox but this does NOT trigger the listbox's CLICK event for me so the combobox isn't populated so I can then make that selection from Screen #1, also. I've tried setting focus to listbox first, then making selection, but that doesn't work.
QUESTION: Is there code that selects from a listbox in a way that mimics the user clicking the selected item in the listbox?
The alternatives I can think of are: 1) Change Screen #2's listbox code from CLICK to CHANGE event, but I'd rather not. 2) Move CLICK event code to sub-procedure and then call from both listbox CLICK and Screen #2 code 3) Some sendkey string like ENTER?
Would be easier to just mimic the user click, if possible.
With frm_Screen2 'Select item type from listbox With .LBox_Items
I am using Excel 2010 and need to create something like a histogram that will have large regions of null values. In other words, my histogram might look like this...
x x x x x x x __x_________x___________x______ 100 250 420
[My laboriously drawn histogram does not display correctly. It is supposed to have 3 x's above 100, 2 x's above 250, and 5 x's above 420]
I have data in the form of: Value Prob. 100 30% 250 20% 420 50%
I have Excel giving me a histogram that looks like this... x x x x xxx xxx [This histogram also didn't turn out. Again, it is supposed to be 3 vertical x's, 2 vertical x's, and 5 vertical x's]
What I have tried to do is to create a list based on the lowest and highest numbers (e.g., 100 and 420) and split that into equal increments. I then intended to use a VLOOKUP or something to pull back the probability associated with the number in my list nearest the data value I have.
I couldn't get VLOOKUP to work correctly, so was happy when I found the following thread on Ozgrid. [URL]
However, in each of the solutions listed in that thread, I get faulty results. I am attaching a file that shows the errors and what I am trying to get : Example.xlsx
Perhaps there is some easier way to do what I am attempting to do. This architecture makes sense to me, but sometimes the perfect solution doesn't make sense until I see it...
I have various spreadsheets that get into infinite loops sometimes when I search for things. Hitting break breaks it, and the search window (find all) shows the same exact entry hundreds of times.
After adding worksheets, data to ranges, etc. I want to step through each worksheet and "Select" or "Activate" a specific cell (Mainly to clear all the range selections).
My code does the job except the second to last part of cell selection/activation.
I am running excel 2010. I have set up a variable " testvalue" type variant
I would like the prog to step through a row of data which can contain text or numbers. If any number is greater than 3,000,000 I want exit the do. However, if no number is greater than 3,000,000 I want to record this as a "bad file". I have the follow code which trips at the first "case".
Code: testcol = 1 testrow = myTextRows - 1 'penultimate row Do While Len(wbText.Sheets(1).Cells(testrow, testcol)) > 0 testvalue = wbText.Sheets(1).Cells(testrow, testcol).Value Select Case testvalue
