I started changing it and then realized that I can keep going by adding new elseif's but i think an array would be a better option(i think). But i am really a monkey see monkey do kind of vba user. i can backwards engineer but cant come up with it.
here is code
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("$z:$z")) Is Nothing Then
If Target.Value = "NO CONTACT" Then
Target.Offset(0, 1) = Date + 1
ElseIf Target.Value = "NO CNTC FCL" Then
Target.Offset(0, 1) = Date + 1
ElseIf Target.Value = "NO RES" Then
Target.Offset(0, 1) = Date + 1
you see i keep adding "results" and assigning lift dates. what i would idealy want to do is take my list (that i use in a drop down box in $z) and next to it assign the number of days i want to set the next call date(the offset 1 cell) to be. then have the code just read cells a1:a10(the results) and assign the corresponding b1:b2(the amount of days to wait to call). I can keep adding elseif's but then if i decide to add new results codes or change the days from 1 to 5 i have to go back to the code.
develop a macro to send e-mails upon certain criteria, I then wanted to add a layer to the macro which I have, and it works... sort of. I'm wanting the Macro to send a specific e-mail if the dates fall between a certain range, a different e-mail if they fall between another range, etc... Here is the code I came up with:
Private Sub Workbook_Open() Dim LR As Long, i As Long, s As String, sb As String, sc As String, eml As String With Sheets("Vendor-Rltshp")
I have an enquiry where I'm trying to order error messages which occurs in if, ElseIf and Else Statements. The code I have works. However, I would like the error messages to be checked in a particular order. I tried rearranging the conditional statements but had no success. Here is my
In row 6 column O I need for the formula to look at column I and determine if the month is 1, If yes then I need for it to determine if the date is one of the following,
Day(I6)>=1,Day(I6)<=5), If TRUE Then do the following Cells(J6)*1.04)*2080/12 If False then ElseIfDay(I6)<=6,Day(I6)=<12, If TRUE Then do the following Cells(J6)*43.33)+((J6*1.04)*129.99 If False then ElseIfDay(I6)<=13,Day(I6)=<19, If TRUE Then do the following Cells(J6)*86.66)+((J6*1.04)*86.66 If False then ElseIfDay(I6)<=20,Day(I6)=<26, If TRUE Then do the following Cells(J6)*129.99)+((J6*1.04)*43.33 If False then ElseIfDay(I6)<=27,Day(I6)=<31, If TRUE Then do the following Cells(J6)*1.04)*2080/12, but put this into column P
If row 6 Column O if the month is greater then 1 then I need the formula to do the following, =J6*2080/12
What I am trying to do is have excel take columns O through Z look back to column I and determine which month the performance review is due in. Then I need for it to determine which week the increase rate will be effective for. So for each month the “If the month is >1” would change to 2,3,4,5,…..and so on.
In addition I am going to have the formula look at the shift and if it =3 then (J68)+1.00, but for the increase I have to have it -1.00 then J6*1.04+1.00. I will also have to create a table that holds the max rate for each grade, and then figure out the formula to figure out this new rate.
I have created a user form with a combo box and three text boxes. The drop down box is populated via the initialization of the form; code is posted below.
the "add item" names are names that are already on the worksheet in column 1. What i have done is used the command button "submit" to populate the information from the textboxes to the worksheet.
what i would ike to do is populate the empty cells below this area with the information that is in all three text boxes and the combo box. for the IF / elseif statments i have used the combo box as the condition. So the quandry is I cant input the information into the empty cells, i have tried a few things, my vba is very basic, as you can tell ihavent even DIM'd anything (i think that is another thread though :D )
here is the initialize code and below is the "submit button" code.
Private Sub UserForm_Initialize() 'sets values for text boxes cbomaterialdiscription.Value = ""..........
My code is below. See how I have repeated the same code over and over for each row. Is there a shorter way to write this, perhaps a cleaner way. Im not an expert and I write what I know and most of that is long code that can be shortened.
I have two large spreadsheets that are arrays. Column A contains 40 items in both files. Columns B through X contain unique names. The arrays are filled with dollar amounts. Is there some formula or function in Excel whereby I can easily combine both spreadsheets, essentially adding the dollar amounts for each corresponding cell?
I'm trying to automatically add and remove one criterion from an autofilter without disturbing the previous existing criteria The autofilter code sounds like this:
How would I go about referring to this array and adding another item called "Hard" to any pre-existing elements in this array?
I plan on attaching this code to a button and use it as a filter, so when the button is pressed, the item will be added to the list of criteria, and if it is "un-pressed" the item will be removed.
On this is a column of Categories and a Column of sizes. I want to use these in a user form. The user will select their Category from a drop down list and the second drop down list will include only the sizes that appear next to the chosen category So for example in the attachment if the user chose 'AUD' as a category they would get the size choices of, '2x4 insert', '2x4 replica' and 'A4L' in the other drop down menu.
The master list of sizes will have to remain on a worksheet as this is what other operators will amend from time to time.
I have an Excel workbook with two sheets "DataSheet" and "Actual Peaks", "DataSheet" contains a column with dates and a column with values. "Actual Peaks" has a bunch of dates listed in a column. For each date in 'Actual Peaks', another column goes back to the "DataSheet" finds the date, then starts adding values until a specified sum is reached, once the sum is reached, it returns the date at which the sum was reached. However, I have found that sometimes it is off by a day or two.
See the attached workbook for a much clearer example : Excel_forum help 7-23-14 DD validation.xlsx‎
Basically it is a if statement saying that if the selected cell falls between 1/01/06 and 31/01/06 then Jan would be selected. The end part is not a problem; I’m just not sure how to write the one line of code that would test if the cell falls between the two dates. I attemped to create it as shown in the code attached below but wasn't successful. I used an else if statement to test the other 11 months.
Sub test() Dim SelectDate As Range Set SelectDate = Range("SelectedDate") If selectedDate >= 1 / 1 / 2006 And selectedDate <= 31 / 1 / 2006 Then ActiveSheet. PivotTables("PivotTable1").PivotFields("PnLDate").CurrentPage = _ "Jan" ElseIf selectedDate >= 1 / 2 / 2006 And selectedDate <= 28 / 2 / 2006 Then
I've got a problem involving several Dim'd Variables needing to be added up, they're all Dim'd as Variants though as they can be either strings or numbers at any time.
I need a formula (VBA) to add them up (to add their actual numeric values - not just a "1" if they contain a number) and ignore them all together if they contain string values.
I have a userform that I'm using to add data to a worksheet, with the following
Private Sub CommandButton1_Click() Dim OutSH As Worksheet Set OutSH = Sheets("Sheet1")
OutSH.Cells(nextrow2, 1).Value = Surname.Value OutSH.cells(nextrow2,2).value = ID.value OutSH.cells(nextrow2,3).value = Date.value ...... I need to ensure that duplicate entries are not made for the same person on the same date. The ID is unique to each person.
IF statement that can check for a duplicate and then come up with a dialouge box with some custom text, and then exiting the sub?
I am trying to add numbers from cells if it IsNumeric and for some reason in column K the macro doesnt recognize numbers after row 14?
The range column is "E4:E" Search criteria is the letter "R" in column "E" Then using OffSet, I go thru other columns and process data. Most of the macro works except for column K after row 14?
in C a string is nothing more than an Array of characters ending with a null character.
in VBA this does not seem to be the case.I am trying to use the BlowFish code from David Midkiff for some encryption, however the code sometimes fails:
When encrypting a string a string of a specific length should be returned. however sometimes one of the characters of the encrypted string is a null character, and the returned encrypted string (with a embedded null character) is causing me problems. I want to check for the null character and if present redo the encryption. But how do I check for the presence of this null character in a unicode (double-byte) string?
Checking with Len(encrypted) gives the correct length (!) but trying to move each (unicode)character into an array fails when using the Mid() function past the null character in the string.
Now I can use
byteArray() = StrConv(unicodetext,vbFromUnicode)
to get the lower byte of each character into an array easily, but how do I do this for both bytes of the unicode characters?
Using an integer array does not work other than through
intArray(j) = CInt(AscW(Mid(Outp, j, 1)))
which fails on the nullstring in my encrypted text.
I have tried with variants but so far no luck. I thought if the string was entered into a variant then I could also address the variant as an array of bytes, but that does not seem to be accepted by VBA.
I am trying to populate many arrays with the same code using something like this. For this test, assume the following data in A1.
1 2 3 4 5
6 7 8 9 10
11 12 13 14 15
16 17 18 19 20
21 22 23 24 25
Code: Sub populate() Dim firstArr(5), secondArr(5), thirdArr(5), fourthArr(5), fifthArr(5) As Integer Dim r, c, num As Integer
[Code]....
The above code does not work of course and falls over. I am unsure whether I should try and concatenate with something like this eg "" & arrName(i) = Cells (r,c) or go down a different route.
I have a class module with several private variables, including one that is an array of a user-defined type. I am trying to set the values of a single element of this array with "Property Let ..." from a string array:
Say I have 3 columns of data: A1:C10 and I want to run a Match() function on them all together to see if I get a match any one those cells, say the value of have in X1.
Since, Match only allows a One-Column lookup array.. is there a way to "concatenate" or "append" the 3 columns together within a formula so now I would be looking to Match in an array that is 1 column * 30 rows?
Basically want to convert =Match(X1,A1:C10,0) to =Match(X1,A1:A30,0) without moving around the raw data in the sheet.
And I want to avoid doing an AND or OR formula that uses 3 separate MATCH() for each column.
I have a hunch that the MMULT or MMULT/TRANSPOSE functions are involved, but can't seem to get it right.
I'm only starting to get to grips with arrays. I have what I consider to be a lot of data that I need to 'cut' into separate workbooks. I have written some code that does this by simply looping through each line, 250k+, checking against a variable and copying the row into a separate sheet. This took longer than it would have doing it manually. It was suggested to me that I use arrays to speed up the process. I have managed to store the test data into an array but am struggling to find a way to loop through and pull out an entire 'row' from the array based on a variable. I have looked for 2 days in various places to find some way to loop through the data held in the array, but to no avail.
That code will appear here from about 8am GMT tomorrow. I know that once I've cracked this I'm on the road to some very significant time saving and comprehensive report writing.
I then copy and paste 'values' and filter out the 'false' to get my final result.
This has worked in the past, but for some reason that I simply can't figure out, the formula isnt working! I've attached the example, and I've highlighted a number in blue (cell E522 and C103), (that should be being found in the 'LOOKUP' function) but is returning a "FALSE". I have looked over the code and simply can't figure out why Excel isn't returning the right value.
This is obviously happening for a quite a few of my numbers, as my filter result is returning an array that is about 1500 shorter than it should be. I have highlighted E522 as the 'example cell' to look at.
