Im trying for quite some time now to set the array in the 2nd dimention of a 2D-array to the result of a function which returns an array of dbl but i just can't get it to work.
The result of the Interpolate-function has the same length as mintSpan.
ReDim dblProductionRate(1 To 5, 1 To mintSpan)
For intHC = 1 To 5
If CheckHC(intHC) And Owner.CheckHC(intHC) Then
dblProductionRate(intHC) = InterpolateProductionProfile(intHC, mdblReserves(intHC), GetProfiles(intHC))
I am trying to set an Array to prompt user with a series of questions and input answers into a specific row. However, I want to skip over certain columns and leave those blank. How would I modify my code to do this?
Code:
Private Sub CommandButton2_Click() Dim ArrQues() As Variant Dim ArrInput(2) As Variant Dim lngLstRow As Long Dim strPNfromSales As String ArrQues = Array("Enter the Part Number.", _ "Quantity Being Returned?", _ "What is the Reason for Rejection?") For b = LBound(ArrQues) To UBound
[Code]...
I want the first two questions to be input into Column B & C, but then the third question to be input into column I.
I need to setup an array to look in 408 or so names and I don't want to have to write ("billy", "beth", ... ) etc - I just want it to scan array A3:A408 instead and unsure how to write the macro to do this.
Sub Echo_Monthly_CSR_June() Windows("Monthly Macro Insert.xls").Activate Sheets("CSR Data").Select Dim MyArray As Variant, x As Long, c As Range
MyArray = Array("A3:A408").Select For x = 0 To 405 With Workbooks("Monthly Macro Insert.xls").Sheets("Copy & Paste Echo") Set c = .Cells.Find(What:=MyArray(x), After:=.Range("A1"), LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False) If Not c Is Nothing Then..............................
I do this each week with with approx, 40,000 records, (single dimention) copying and pasting is becoming a chore. Does anyone know a method to address this via functions, macros, formulas, etc. Number of rows and columns vary each time I repeat the exercise.
I'm trying to set a range variable ('IngFore') as equal to a number of rows long and a number of columns wide via 'Cells'. The range needs to be dynamic in that the total number of rows and columns will vary over time. Hence, I've defined 'lastdate' to determine the last row in the array, and 'lastcol' to determine the last column in the array. The starting point for the array is I12.
It would appear that I can't quite get the syntax right.
Code: Set IngFore = (Cells(12,9 to lastdate,9)),(Cells(12, 9 to 12, lastcol))
I've tinkered with it here and there, removing and adding brackets but I can't get it to work.
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.
What I want to be able to do is set up a thing that when you're in the excel sheet you click on it. Up comes a box where you can enter data and when you click submit. It puts that data back into excel spreadsheet. I have attached a spreadsheet to show what I mean. I have deleted some stuff that are unnecessary so don't worry about them.
What I need is for you to click a button that will create a new row/copy into the next row, from A9-AH9. Then the button will automatically bring up a box that will ask for Time, Timber, Clay, Iron, Warehouse, Hiding Place and then will enter that data into there respective cells which would be D20, F20, I20, L20, O20, Q20 respectively. That is the main idea. maybe it would be better to have te button come up with a box asking for the information and then will create the row and insert the data. If it's easier you can just do the box with just time or something so it's easier. I can then do the rest.
Tyvm. This to me seems really complicated so not sure how you would do this at all. i've looked at other stuff on the internet but haven't gotten anywhere.
P.S. If you have a more generic thing similar to this then show me the code for that or upload it and I can change it too suite my needs.
I have the word "Gross" in a cell at the end of each month on one sheet. I need to calculate totals for each month above this columns so lets show it as this: A B 1 100 2 150 3 220 4 300
Gross 770
How can I set up a macro to always calculate what my gross is setting a condition to find "Gross" and then somehow select the cells above it up to A1? I will have another cell as "Gross" for February under the rows of information for January. I am not at all sure if this can be done.
I am trying to set up a database to collect information on students behavior. We need to collect the frequency of 3 things-time of day, behavior, and correction. Can I do this in Excel? How? Is there a way to write a correlation that will count the occurrence of words?
How do you set the range with whats below? Right now its looking at 4,2 only and if extend to look all the way to 19,2 it will just copy the whole screen. I need it to look at 4,2 and then move to 5,2 and then so on once it has copied all that information to the sheet ...
I just received a massive worksheet full of information for mailing labels. It's all in columns-which is excel. Is there an easy way to set this up for mailing labels? I've tried to import it into word, but I'm not getting anywhere. Is it possible to change the way excel displays the cells? To a point where I can have the address beneath, then the state, zip, etc?
I have an excel workbook with multiple worksheets(tabbed across the bottom of the workbook). How can I set up a menu of the worksheets on the left hand side of the workbook that enables me to jump to that worksheet?
I have a problem with an file that I have exported from another software program (I believe in CSV). The file seems to be in tekst format but I can not change it to date format. I need to know which weekday is which date. So january 1st is a tuesday and so on..
I am setting up a few spreadsheets for a restaurant I work at and I am stuck on an excel matter. I am trying to have an ordered number transfer to a new spreadsheet that will apply that ordered amount to a monetary value. So all my cooks would do is put the prep list on dropbox and it would take the ordered amount by the recipe cost to get an invoice for that day.
If I want to set a global variable when I open my workbook which will be used in code on the individual spreadsheets, how would I do this? I want to set the time the workbook is opened to a variable (constant) and then compare that time to current time on each calculation in the worksheets.
I have a WorkBook with each sheet being a calendar month. I want to set the ActiveSheet as the current month so each time I open the WorkBook it opens to the current month WorkSheet not the last WorkSheet I was viewing.
how to write code to set page breaks every 4th column, or in certain intervals. For rows I have a simple .FitToPagesTall = 1, but I'm looking for something more dynamic for the columns.
I am wanting to create a function, callable from a worksheet, that sets the background colour for any given range of cells to a given RGB value. I am confused about the Color and ColorIndex properties. I'd have thought the following piece of code was trivial and would work but it doesn't.
