I would like to construct in VBA "n choose r" Boolean arrays of length n which will give all possible combinations of these variables as you scroll through the arrays.
Typical values of n and r would be 40 and 5 respectively and I'm looking to get the fastest possible output on these 660,000 or so arrays.
I have been playing around with various "for, to" statements but haven't managed to turn out anything meaningful thus far.
I am retrieving a CSV file from the net. In this file there are 'x' amount of row data and 7 columns. I only care about the values in the 7th column for each row. I also don't care about the entire first row. A graphical version would be represented something like this, with the values I want colored in orange:
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
.
. extending until the end of the data set
.
I've managed to dice this thing into a jagged array by first splitting it using vbLf as a delimiter, and therefore adding those to an array called Lines(). Then I split Lines() up using commas as the delimiter and threw those into a jagged array, let's call it Breadcrumbs()(). I want to throw all the values from Breadcrumbs(i)(6) into an array of its own. Here's my code so far:
Code:
Public Sub CSVparser(file As String)
Dim Lines As Variant
Dim j As Integer
Lines = Split(file, vbLf)
ReDim breadCrumbs(UBound(Lines)) As Variant
For i = 1 to UBound(Lines) - 1
breadCrumbs(i) = Split(Lines(i), ",")
Next i
End Sub
I have a timecard spreadsheet which im working on, each employee is represented by a single worksheet in the workbook, and the worksheet is named after the employees name. New employees are added by filling in a form which dynamically creates the timecard based on a template. Im trying to impliment a 'Fire Employee' button which sits on each employees sheet and can be clicked to set an employees working status to false by changing a boolean variable called 'fired' to true.
Ive been looking around the forum but I cant figure out if i should be using public variables or where exactly i should be placing them, so that each employees 'fired' status is set to false when the worksheet is first created, but can be set to true when the 'Fire Employee' button is clicked, this must only set true for that specific employee, and not all employees. If your wondering why i need this, the employees work status will be used on other forms to toggle views etc so thats why a boolean approach is best (at least i think it is if its not to difficult to do)
I have minimal excel knowledge, but enough to get by. I have 2 years in C++ programming.
Here's what I'm trying to do:
I'm trying to create a workbook to keep track of flags we send out, and receive back. We have a lot going and coming, so I figured the best way to do this would be to break down each region on different sheets. But I need to create a check box that says something like, "Yes/No" or "Here/Gone" or something like that. THEN, I need to take that information, and put it on the front sheet of the workbook so I know how many we have in/out total. Does this make sense? I know how I would program this in C++, but excel is a bit different.
once bTest is declared as a Boolean, it becomes equal to FALSE (I checked this after much head scratching). How does one assign a value to a boolean variable only after you make your test.
Sub foo()
Dim bTest As Boolean
If Range("a20").Value = False Then bTest = False '
I need help creating a code that does the following:
I want a Boolean variable (TheTD) to be True when and only when the value in cell A1 is "TwoOnly". Once it isn't then I want the Boolean to be immediately False. Once cell A1 is "TwoOnly" I want TheTD to be true immediately as well.
I am using a userform and the textbox1_Change macro can change the value of cell A1 while typing. So in other words The Boolean can be changed from True to False multiple times depending on what I type in textbox1.
I have cells in my Excel sheet that contains boolean values, ie. the values TRUE or FALSE. Is it possible to format these cells so that FALSE is written with red color?
View 2 Replies View RelatedThis might be a dumb question but is it possible to pass a boolean variable as a parameter to another function?
I have the following code that produces some compile error ("expected ="):
Are Boolean variables ALWAYS initialised as FALSE when they are created?
I am wanting to leave a 'marker' in a function so that I know whether it is the first time it has been used or not. Something similar to this:
Seems like this one should be pretty easy but I'm stuck... I just want to return a boolean based on whether the currently active cell contains the character "#". I've been trying to use the find method but I can't figure out how to convert a match or lack of a match to a boolean.
View 1 Replies View RelatedI have strings in cell a1. I will put a formula in a3 if string "xyz" is found inside a1, it will show value from a2, else shows nothing.
i tried simple formula cell a3
=if(a1="xyz",a2,"")
here it is exact match, i need partial match, or multiple partial match in a single cell. How to do it?
I want a boolean function to return whether the PC is connected to the Internet. How to start creating this.
Reason:
I have code that checks an online database and downloads data after the user presses a button.If the host computer is not currently connected to the net, I would like to display a message of my choice instead of trying to run the code.To determine whether to run my message or not, I need a function that will return TRUE/FALSE if the host PC is net connected or not.
i need someone that can think through this boolean logically. its been a long day at work and now I'm trying to walk through this boolean thing to make sure my data will post on the right pages.
I have an Excel Macro that will capture inputted customer/vehicle data from an input screen, at the end, based on questions answered, i need to place that data on 1 of 4 sheets in my excel document.
The questions are:
Is this a Nationwide claim? (Y.N) (boolean = nw)
Is the vehicle here? (Y/N) (boolean = vh)
So here's what i got
if NW = true and VH = True then place data on "sheet1"
if NW = true and VH = False then place data on "Sheet2"
if NW = False and VH = true then place data on "sheet 3"
if NW = false and VH = False then place data on "sheet4"
I can't think of how to write this IF statement for this to happen. can't get the logic going.
OR IS THERE A BETTER WAY?? i'm sure there is.
I've got a worksheet with five columns of data. The headings run from a1:e1
The number of rows can be anything up to 200,000 rows (I'm using Excel 2007).....
I'm trying to figure out how to reorganize data based on boolean values in two different columns. My goal is to write something in VBA that will look to see if the boolean in the first cloumn is true then procede to check if the boolean in the next column, same row is true. If that is the case, it will take several values from the row and move them to another worksheet. If the boolean in the first column is true, but the second column is not, it will look down the rows of the second column until it finds true then move several values from rows it has passed through. Here is an example of the sample data I will be dealing with.
Date Time Duration Boolean1 Boolean2
6/22/200715:0032TRUETRUE
6/22/200718:0058TRUEFALSE
6/22/200719:0060FALSEFALSE
6/22/200720:0060FALSEFALSE
6/22/200721:0060FALSEFALSE
6/22/200722:0060FALSEFALSE
6/22/200723:001 FALSETRUE
Based on the example data my goal is to move the data in a new worksheet that would look something like this
Date Start End Duration
6/22/2007 15:28 16:00 32
6/22/2007 18:02 23:01 299
This macro adds a comment to a cell and some text from an input box. I store the last value entered in the input box via the registry, it is recalled as the default input box value. How do I capture the cancel property (Boolean, true or false?) so the ActiveCell.Value remains and comment is not added? When a user selects Cancel/X the macro deletes the value in the cell and add's a comment. This overwrites existing data with "" (nothing).
View 4 Replies View RelatedI am attempting to pass a boolean variable between two macros. The SUBMIT macro calls the STATUS macro to do some verifications and the STATUS macro should return the boolean variable RDY back to the SUBMIT macro, but does not.
I am using Excel 2007. Both macros are in the same Module. The STATUS macro is called from several places in the code.
Public Rdy As Boolean
Sub Submit()
[code]
Call Status
If Rdy=True then msgbox("You have completed your form") else msgbox ("Not ready")
[code]
end sub
[Code]....
Can I use dates as argument in Boolean arithmetic? I have a list of name with their date of birth and I would like to tell who is between 18 and 25. It's easy enough with number but with dates? Excel 2007
View 9 Replies View RelatedI have a function that works well for checking if a specified file exist within a specified path. I realized I do not know how to just check if the folder exist!
Example: I want to see if the following folder exist with boolean response...
"C:MyCompanyMyDeptMyFolder"
OK, so I have a userform with some text boxes that I have specially formatted to accept only date values in the form of mm/dd/yy. By default they are blank. I have a check in one of my codes that looks like this
Code:
If DateBox vbNullString And DateValue(DateBox) > checkdate Then
M1 = "NEOPRENE" & Chr(13)
Else
M1 = "" & Chr(13)
End If
Where DateBox is this specially formatted TextBox and checkdate is a future date being checked against.So if DateBox has a value in it AND that value is greater than the date being checked against the returend string is Neoprene, otherwise it is blank.
Well the problem I have is when the first condition returns FALSE, i.e. when DateBox is empty, the DateValue half still gets evaluated and returns a type missmatch error or something like that because DateValue("") returns an error. I have line of code 8 times, one for Neoprene, squeegee, etc. So the name of the text boxes are each unique and I am using M1, M2, M3, etc.
For other reasons, use of "On Error Resume Next" doesn't work for this situation because it causes a result opposite to what I want to happen.
I'm trying to enter "True" in a cell without it being interpreted by Excel as a boolean value. Initially it was entered through VBA, but I've found I cannot type it in without Excel capitalizing and centering it.
So far, I've tried formatting it as Text and General.
wondering is there a VBA equivalent of --() in excel that turns trues and falses to 1's and 0's?
View 14 Replies View RelatedI have been trying to solve this problem by breaking it down to it's core elements and it seems to hang up at a certain spot but I can't figure out why.
formula is: {=LARGE(IF(AND(Array1+8<20,6-Array1<0),Array2,0),1)}
where: Array1 is a sequence of numbers, say, 2 5 8 11 14 17
Array2 is a corresponding sequence of numbers 1 2 3 4 5 6
the resulting array should return the numbers 0 0 3 4 0 0
and my answer should be 4
instead my answer is 0
when I break it down and select six cells and use the formula:
{=if(Array1+8<20,Array2,0)}
my result is: 1 2 3 4 0 0
and another six cells and use the formula: {=if(6-Array1<0,Array2,0)}
my result is: 0 0 3 4 5 6
when I select a single cell and use the formula: {=large(if(Array1+8<20,Array2,0),1)}
my result is: 4
when I select a single cell and use the formula: {=large(if(6-Array1<0,Array2,0),1)}
my result is: 6
I've tried this method several different ways, even using "Yes" & "No" as my result and then try to match them up. I've tried doing this not using named array and just selecting the cells themselves for the formula. All with the same results.
How do I deal with arrays in vba? Below is a dummy code just to check the array I specified is working:
View 5 Replies View RelatedI have a worksheet that has 20 columns and 500 rows. I created a VBA macro to loop through the data to hide rows that do not meet certain criteria. After the data is selected I copy and paste those selected rows to another sheet. The macro works well but I would like to use an array to contain the data that I copy and paste to a new sheet. I have been trying to find information on multi deminsion arrays but I have not been able to fully understand how to get the information into the array and then how to get it out again. Most of the examples that I have seen are for two maybe three columns (dimensions). I am hoping that someone could point me in the right direction to get started on this. I also have a few books on Exel VBA but none of them seem to address my question.
View 9 Replies View RelatedI am trying to fill calumn B with the data from an array. There are more rows than entries in my array and I want to loop back to the start of my array when it reaches the end of the array results.
Sub test()
Dim i As Integer, MyArray As Variant, RowCount As Integer, ArrayCount As Integer
MyArray = Array("test 1", "test 2", "test 3", "test 4", "test 5")
RowCount = Cells(Rows.Count, 1).End(xlUp).Row
ArrayCount = UBound(MyArray)
For i = 1 To RowCount
Range("B" & i).Value = MyArray(i - 1 Mod ArrayCount)
Next i
End Sub
(if (c2 >= '1') AND (j2 >= '1') then '20' else (if (c2 >= '1') OR (j2 >= '1') then '10' end) end)
c2 & j2 being my two cells.
In English:
If C2 or J2 have any value print 10, if both have a value print 20.
The formula is designed to sum a set of data based on 22 variables between a certain date range. In order to keep the formula manageable, I have grouped the variables into arrays.
EX.
Array 1. - 20 potential choices (Service codes)
Array 2. - 2 choices (pkg types)
Array 3. - 2 choices (volume type)
Array 4. - 3 choices (company names)
Each array (listed horizontally on one spreadsheet) calls a specific column of data to match from a different worksheet. I have no problem if only applying one array but multiple arrays return incorrect values.
I have two 2 Dimensional String Arrays with data. I need to find a way to get the difference between these two Arrays. I am new to VBA, I don't know how to deal with these. I certainly feel that there is some efficient function for doing this. or Is the naive two for lop concept is the only way to go?
View 2 Replies View RelatedI have two arrays that output as two columns next to each other. Like this:
ID
Responses
1
4
2
3
3
2
4
5
5
1
I would like to sort the responses column and have it show as this:
ID
Responses
4
5
1
4
2
3
3
2
5
1
I am guessing that I need to some how merge them into one array so I can sort them as one entity rather than sorting two columns separately.
Here is my sample data and the macro I use to pull the data i need for the two arrays. sample.xlsm