Clear Array Data At Specific Indexes
Jun 11, 2014
I am using arrays to try and move select data in a row between workbooks. I read in the data to an array, I set the value of the range I want to write to to the array. That part works well.
However, the range I am trying to write to is part of an Excel Table (ListObject). There are formulas in that table that I need to keep as formulas. I know (well, believe) that if I populated the data into the array manually and skipped over the formula cells it would not change those cells at all.
How can I (after populating the entire array with data) go back and say "The data at index 3 is really nothing, so exclude it."
I've tried setting the value to Null, Empty, and vbNullString. My array is currently typed as a Variant.
Example:
myArray = Sheet1.Range("A1:A10").Value
myArray(3) = ????????
Sheet2.Range("A1:J1").Value = Application.Transpose(myArray)
View 3 Replies
ADVERTISEMENT
Jul 5, 2012
Is it possible to count the number of indexes in an integer array that have a value > 0 without looping through the array? I found something using Filter, but that only appears to work on string arrays.
View 2 Replies
View Related
Apr 19, 2013
I have set up a macro to clear data in COl A from row 2 onwards. I need to amend the code to clear the data in the following columns from row 2 as well
Col C to I and Col L to O
Sub ClearData()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR
With Range("A" & i)
.ClearContents
End With
Next i
End Sub
View 4 Replies
View Related
Jun 18, 2014
I have a row (will always be row 3) where each cell contains a day of the week, the days repeats for a year or so, making the row almost 400 cells.
Like this,
Mo - Tu - We - Th - Fr - Sa - Su - Mo - Tu - We - Th - Fr - Sa - Su - and so on...
Though, A3 doesn't have to be "Mo" because the days in this case can change (A3 can start with "Tu"), hence I think I need a macro.
So if this row contains a weekend, "Sa or "Su" I want all the cells in the column beneath that which contains a specific value to be cleared.
Example, if "Sa" or "Su" has 3 values in the columns under them, all the values that contain "X" or "Y" has to be cleared.
Like this:
Rows (1,2,3...,)
1----
2----
3 Mo - Tu - We - Th - Fr - Sa - Su - .. and so on..
4 A --- B --- X --- Y --- X --- B --- Y
5 A --- B --- X --- Y --- X --- X --- X
6 A --- B --- X --- Y - --X --- Y --- C
After the macro it should be:
1----
2----
3 Mo - Tu - We - Th - Fr - Sa - Su
4 A --- B --- X --- Y --- X --- B ---
5 A --- B --- X --- Y --- X --- ---
6 A --- B --- X --- Y - --X --- --- C
Notice the two examples in the excel file.
Excelforum.xlsx
View 6 Replies
View Related
Jul 30, 2014
I have this code:
Code:
Private Sub CommandButton2_Click()
Dim myarr() As String, size As Integer, i As Integer size = WorksheetFunction.CountA(Worksheets(1).Columns(2))
user = Environ("username")
[Code]...
when the FOR-LOOP finds the username inside the column B (many times will have more than one result) I would select all the rows that have this username and display that data into a listbox but not only the rows that have the username but also I would like to get the next column C and D in the same row and display all those data into a listbox.
ID
username
date
status
1
usuario1
25/01
ok
[Code]...
taking the table above as example I have USUARIO2 twice in the column B so I would like to get that row and also the DATE and the STATUS from this USUARIO and display it in a listbox. If I'm the USUARIO2 and I click in the button Then I will have a listbox like this:
USUARIO2
26/01
denied
USUARIO2
27/01
ok
What should I add into my code to get this result?
View 5 Replies
View Related
Jul 1, 2014
I've been writing a UserForm in Excel that serves the purpose of a front-end contact/reporting spreadsheet.
I've managed to get a multitude of elements to work together as of yet, however have been having problems writing data to the correct place on the spreadsheet.
What I've already achieved:
The first interactive portion of the userform currently has a TextBox (ArtistAgentAddEmail) - this is the email that we would like inputting on the spreadsheet.
a ComboBox which is populated on 'Userform_Initialize' into an array (ArtistAgentAddDestination) - The range of this array is A1:AC1.
[Code]......
What I would like to do next:
The issue that I've been having is getting the email to be written into the correct column. Once I have typed in my email and chosen my destination from the combobox, I would like to write this email address in the column of the destination chosen.
E.G. The first 5 elements of my ComboBox array are: Neil O'Brien, CAA, CODA, ITB and PRIMARY. If i got an email from somebody in the CAA organisation to add a new contact to the reporting, I would like to be able to add this address via the userform underneath the previously added contact.
The code that I am currently using for my confirm button is:
[Code].....
Attached File : Userform Contact Sheet - 27th June.xlsm
View 2 Replies
View Related
Mar 7, 2007
I have created one-dimensional array and now I try to copy the data from array to some specific Cells in worksheet. But it seems impossible all the time!
Sheet2.Cells(56, 3 + m) = LossLocationInt(m)
NB: LossLocationInt(m) is an array containing value in it. But, this doesn't copy to the Sheet2.Cells(56,3+m). For clearness: the array has type variant
View 4 Replies
View Related
Mar 8, 2007
I would like to make up an array that includes certain userform controls, such that I later on can use a loop to run through those controls.
My Problem:
First, in line 1 the Sub cbStep1_Click calls the Sub unloadSlotValues, but then the latter throws me back directly to the Sub cbStep1_Click into the next line (line 2). There is no error or something, the rest of unloadSlotValues simply gets ignored. How can I ensure to loop through all my userform controls listed in that array? (Finally, there'll be around 15 controls, I think.) Maybe my array-initialisation is incorrect?
This thread extends a simillar one you can find How to clear userform controls
Private Sub cbStep1_Click()
Call unloadSlotValues '<-- 1. line
frmStep2.Enabled = True '<-- 2. line
frmStep3.Enabled = False '<-- 3. line
End Sub
View 4 Replies
View Related
Jun 6, 2008
For various reasons I need to copy a the range "a2:s251" in the sheet "FWD Input" to another sheet but I need to remove any formulas.
To do this I have used the
Sheets("FWD Input").Range("A2:S251").SpecialCells(xlCellTypeFormulas).ClearContents
This works fine if there are formulas in that range, however if there aren't it gives me: " Run time error: 1004 No cells were found"
So I think I need some code that counts the number of cells in the range with formulae in them and either goes ahead if there are some or quits if there aren't.
View 3 Replies
View Related
Jul 17, 2013
I'm attempting to clear the contents from a range of cells on rows where a cell string may equal R, X, XX, Y, Z, ZX, #N/A.
The macro runs fine until it gets to a cell that contains #N/A. How to get this to work?
Sub Recalculate()
Dim r As Integer
r = ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For Row = 13 To r
[Code] ........
View 2 Replies
View Related
Apr 27, 2014
Looking to code a loop to go to sheets whose names begin with "day" and a number and clear a specific range. How would i code this without affecting the other sheets in the book?
View 3 Replies
View Related
Jul 3, 2014
I am currently using this Code to search column1 for a Key Word that is entered into textbox "Kunde" on my userform.
[Code] .....
What i would really like to be Abel to do is :
Search for the cell in column1 with =Kunde.Value
Clearcontents of this cell and the 2 adjacent to the right.
i.e. Word is found in A7
Cells A7:C7 contents are cleared and fill Color returned to default colorindex 0
View 3 Replies
View Related
Jul 29, 2014
Below is my initial code that can't get past the first line.
if right(cells(2,i),4) = "D_MA" then ..... ?
I'm trying to clear the contents of all data in each column that has the text conaining "D_MA" in that columns row 2. For instance, if cell F2 has 30D_MA, I want the macro to clear all contents in Column "F" -- but to do this for all columns that contain "D_MA" in row 2.
View 8 Replies
View Related
May 10, 2014
I have a userform where the user will identify a record to delete. I need to search another worksheet (Month) for the event's name which is associated a date. Once it finds this event's name I need to clear the contents of that cell.
Here is the code I am using for the record deletion from the 'Data" worksheet. I need to also locate and clear the cell as stated above.
View 1 Replies
View Related
Jun 10, 2014
I need to start with a worksheet without any objects, namely pictures. Without knowing the name of the object, is there a way to clear all objects on a worksheet?
Second: on a different worksheet there are products with pictures with the product number. When a user selects the product number I want to copy that picture and copy it to J42 of the first sheet. My issue is positioning the picture.
View 3 Replies
View Related
Sep 13, 2010
I am trying to select specific values within an array.I have two tables, weight table and a factor table. These tables are a lot bigger than the example. I am trying to multiple the weights times the corresponding factors and then sum the result in one cell using arrays.
The third table is the result I am looking for. However, I would like to use some type of index/match or choose to get the result but cannot figure out how to do it. A couple of functions that I tried that did not work are next to it.
The index fails because the index function does not output an array (The result of the index function is just 4% instead of {4%,3%,4%}). The choose fails because the choose function does not select values within an array (ie I would have to put each factor as a separate value (F4,G4,H4) rather than the entire array (F4:H4). There are so many factors that I would like to avoid that.)
Sheet3
ABCDEFGH1 2 3NameWeightFactor DateFactor1Factor2Factor34Name125%Factor3 7/1/20105%3%4%5Name235%Factor2 8/1/20107%2%3%6Name240%Factor3 7 8DateReturn Does not work 97/1/20101.04% 4.00%#VALUE! 108/1/20102.65% 3.00%#VALUE!
[Code] .......
Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!
View 3 Replies
View Related
Mar 29, 2013
I am trying to use the below code to fill a combobox with unique values. It works great except I can't figure out a way to replace BLOCK 1 with BLOCK 2. I want to use BLOCK 2 to populate mgNames so that only specific values are filled into mgNames. Currently BLOCK 1 just fills mgNames with an entire range without any conditions. how I can get BLOCK 2 to work in the way i'm intending it to?
Code:
Sub findNames()
Dim mgNames As Variant
Dim myCollection As New Collection
[Code]....
View 4 Replies
View Related
Feb 3, 2014
Lets say I need an array of 100 numbers from zero to 100. I want a mean of 75, and SD of 12. Or the same data based on a skewness and kurtosis value. Can I do that in Excel?
I'm just generating fake datasets for my stats class to analyze.
Edit: I found that =norminv(rand(),,) will work.
View 1 Replies
View Related
Jan 22, 2009
I want a macro that if a certain cell on a row in column F on my worksheet is "6" then copy the cell in columns B and H on the same row are to be copied into an array and then when the loop is done, display the copied cells in a msgbox.
Is there a simpler way to do this without fidgeting with an array?
View 5 Replies
View Related
Jul 12, 2009
I am attempting in the code below to copy all the worksheets from a specific folder into an array (for later manipulation), not to a single worksheet, The files open correctly, but the reading of the worksheets into the array is my downfall....
Sub FindOpenFiles2()
Dim FSO As Scripting.FileSystemObject, folder As Scripting.folder, file As Scripting.file
Dim directory As String
Dim wksht As Worksheet, i As Long, wkshtnames() As Variant
Dim wbNew As Workbook
directory = "C:Users"
Set FSO = CreateObject("Scripting.FileSystemObject")
Set folder = FSO.GetFolder(directory)
For Each file In folder.Files
Workbooks.Open file
Next file
For Each wksht In ActiveWorkbook.Worksheets
i = 0
i = i + 1
ReDim Preserve wkshtnames(1 To i)
wkshtnames(i) = wksht.Name
Next wksht
View 9 Replies
View Related
Aug 23, 2006
I want to get each distinct values from a range (say, c1:c20) and put them into an array for further processing.
View 3 Replies
View Related
Apr 15, 2014
I'd like to have a sheet with multiple columns of data (say A thru K for instance.). Id like to reserve column A for ONLY imputing an X. The rest of the columns b-K would have data in the cells. I'd like to have a macro that when it saw an X in column A, would copy all of the data in cells B-K in that row, paste it into the next empty row of a second sheet (for history tracking), then go back to the original sheet and continue looking for additional "X"'s and repeat. Once all of the X's were copied, it would "clear" (Not delete because some of the cells would have formulas in them that would need to remain for future use.) the cells based on the "X" then finally move all of the remaining data up to the empty rows to fill in the empty rows. This last piece would be more for esthetics to have a clean looking sheet.
View 1 Replies
View Related
Mar 6, 2013
How can i split a string of "name*sirname" by the * character. At the end i want to msgbox the both 2 results as "name" and "sirname"
Code:
Private Sub CommandButton1_Click()
Dim myArray() As String 'DEFINE THE ARRAY
i = 0
[Code].....
View 2 Replies
View Related
May 8, 2013
I have a table like the one below, only it has a few hundreds of rows and columns and I need a solution in order to fill in the blank spaces with 0 and 1 in order to get the total by row and by column. Is there any way to do this with a formula/macro ?
1 May
2 May
3 May
4 May
a
2
b
[code]....
View 4 Replies
View Related
Aug 21, 2008
Given three criteria (category, name and date) I want to find the specific value within the table or array. refer to attachment.
View 4 Replies
View Related
Jun 14, 2008
I looking for a macro which will help to open a file with current week number in name.
The problem is week 1 is starting on 30/03/2008 (finacial year) and ends on 28/03/2009.
I've made two dimensional array (week number, weekday) with all the dates from that period.
I have problem with code to search through the array for given key, return index and write it into variables.
For now my code looks like:
Sub week()
Dim i As Long
Dim j As Long
Dim k As Long
Dim week(51, 6) As String
View 5 Replies
View Related
Oct 25, 2012
I have an month input in cells B2 (user can enter values from 1-5)
I then have a data table that has month 1,2,3,4,5 running across range G9:K9. the data is held in range G10:K19.
So if user types in 4 in B2, what should happen is that months 1,2,3 and 4 the data for these months should be cleared and data in month 5 moves forward into month 1.
So basically, anything left of the month entered is B2 should be cleared and replaced with anything right of the month in B2.
1
2
3
4
5
£11
£133
£29
£193
£100
[Code] ........
View 3 Replies
View Related
Feb 17, 2009
Sum every instance of [SPREAD]*[SIZE] where [NAME] occurs within a text string.
Please note, I'm trying to do this without creating a new column [SPREAD]*[SIZE].
View 11 Replies
View Related
Dec 30, 2009
I'm having difficulty creating an array formula. In a multi-column sheet, I am looking at a column with classes and a column with a date (in the format 7-Oct-09). I need to list the number of a specific class for a particular month (any day). I have tried the following which gives only the number of classes:
=COUNTIF(A4:A2500,"AA")+COUNTIF(H4:H2500,"10/??/09") and
=SUM((A4:A2500="AA")*(H4:H2500="??-Oct-??")) which gives me 0. Maybe an array formula is not the way to do this.
View 9 Replies
View Related
May 21, 2008
I am trying to enter only the named ranges in the active worksheet into an Array.
The amount of named ranges can be from 4 to 7 per sheet.
I'm self taught at VB (This posts on this forum have taught me - thank you!) and have got the below code working on a Workbook - but not on a worksheet level.
ActiveSheet.Names.Count returns nothing, changing to ThisWorkBook.Names.Count returns the sum of all .Names in the workbook.
Sub aTest()
Dim sArray() As String
Dim sJoin As String
Dim y As Long
x = 1
y = ActiveSheet.Names.Count
z = ActiveSheet.Name
View 3 Replies
View Related
