i am not good in programming.In an outlook i am trying to write a maro.
I am reading lines from a text using readLine(), how i can store each line into an array using vbscript. I write the code as follows,
While Not F.AtEndOfStream
s = F.readline
Start = InStr(s, "@")
If (Start > 0) Then
- Here i need an array, when start>0 , store that line into an array
I am trying to loop through column A and I want to store in an array where I find "App" within the cell value. I am trying to find "App" but will store the whole cell value in the array. I could not figure out the Find method, so I tried the MID function but am having no luck.
Here is my code:
Code:
Sub Arraytest()
Dim arr As Variant, lastrow As Long, i As Long, f As Long, l As Long
f = 0
lastrow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
ReDim arr(1 To 1, 1 To lastrow)
[Code] .....
In VBA, is it possible to write to a text file, on 1 single line (without carriage return or line feed) the values of a dynamic array? For instance, I have the array:
Dim my(n) As Double 'n is changeable
my(0) = 0
my(1) = 1
my(2) = 2
.....
I want to write to a text file, like this: Write #filename, my(0), my(1), my(2) '..... until my(n) but n is changing
I want them on 1 line, because I use the other direction (up - down) for other things).
I am trying to write a VBA procedure that uses nested For/Next loops to store the contents of a range in three worksheets to a 3 D array and then input the array into another location.
I have no problem doing this for a 2 D array, but am completely stumped on what changes I need to make in order to perform the same task on a 3 D array.
I will post my code for 2 D arrays below and will also post the uncompleted 3 D code, although, it is currently of no use because I am so lost on the 3 D array.
How do I reference sheet1,sheet 2 etc. when declaring my 3 D array? I know how to reference the rows and columns for my 2 D array by using the cells or range object, but what to do for the different sheets.
Two D Array:
Code:
Public Sub For_Next_Two_D_Array() Dim I As Integer
Dim J As Integer
Dim MyArray(4, 4) As Integer
For I = 1 To 5
For J = 1 To 5
MyArray(I - 1, J - 1) = Cells(I, J).Value
[Code] ........
Three D array:
Code:
Public Sub Store_ThreeD_Array()
Dim I As Integer
Dim J As Integer
Dim S As Integer
[Code] ......
I would like to use array v to store all the results of the for loop u...How can Ido it?
Dim myRange As Range
Dim AnsRange1 As Integer
Dim AnsRange As Range
Set myRange = Application.InputBox(Prompt:="Select row to insert 10 rows below", Type:=8)
AnsRange1 = myRange.Row
Dim u As Integer
Dim v As Integer
Dim var() As Single
v = 0
For u = 23 To 24022 Step 9
var(v) = u
Next u
If Not (AnsRange1 = v) Then
MsgBox AnsRange1
Else
Range(AnsRange1 & ":" & AnsRange1 + 9).Insert Shift:=xlDown
End If
End If
I can't quite seem to figure out the syntax for pulling a Range variable out of an array of type variant. I always seem to receive the error message "Object variable or With block not set", an example of my code as follows:
Dim currentRange As Range
For i = 2 To UBound(myArray)
currentRange = myArray(i)
' insert code here
Next
I have also tried:
Dim currentRange As Range
For i = 2 To UBound(myArray)
Set currentRange = myArray(i)
' insert code here
Next
Which results in the error "Object required". What is the correct syntax?
I have in column B, cells that have any number, one number or several like B2.
#
A
B
C
D
E
[Code]...
Thinking that I have a loop for rows 1 to 3:
When in column "A" is "No", I want to consider values in C and D to get:
Code:
a[1]=Cells(1,"C") & "-" & Cells(1,"D") & "-" & "ABC"
But when in column "A" is "Yes", I want to store in array each value within cell in B (in this example B2) to apply later a For/For Each
over each number (in this example are 3 values only within cell B2), something like:
Code:
a[1]=Value_In_B2[1] & "-" & "ABC"
a[2]=Value_In_B2[2] & "-" & "ABC"
a[3]=Value_In_B2[3] & "-" & "ABC"
In an excel i have 3 columns they it contains around 12000 records
Group FA Title
A S1 bbbb
A M1 xxxx
A M2 eeeee
A S1 ffffff
A S1 pppp
A M3 aaaaa
A M2 ooooo
A M2 qqqq
A M1 ttttt
Here i need to get the unique FA, so i filter the column FA, my question is, After filter with FA column ,is there any way to store these unique FA(ie S1,M1,M2,M3) into an array using vba?
I have a list of names from cell A1:A10 in sheet "Input." Each of these names has its own corresponding sheet in the workbook. I want to be able to run the same exact VBA code for each sheet. In other words, I am trying to get my name variable to automatically change to the next value on sheet "Input." I'm sure this is pretty simple to do, but I can't seem to find anything that works!
View 5 Replies View RelatedI want to perform some operations (basically a secondary filter) based on the values which are currently filtered within a single filter column.
.Autofilter.Filters(n).Criteria1
and
.Criteria2
are great, but what if there are more than 2???
i.e. I have a column containing values L01 to L20.
My column is filtered on L05, L06 and L07 (or some other combination).
I want to extract the values and L05, L06 and L07 and do what I will with them.
how can i store the values of an autofilter's list in a array using VBA.
View 4 Replies View RelatedI have a macro which will import data to the worksheet, then perform some formatting on the data, then assign the month & job description based on the lookup table. The problem is that when I import in the data, the data in column B&C will be store as text instead of number and the date in column E will store a 2 digit year instead on 4 digit year which cause error to my macro. I have try to preset the column format to number, i even try to change the column format to number when i run the format macro data. But the problem is still there.
View 5 Replies View RelatedI want to store number as text like green sign display at the top left corner.
View 7 Replies View Related1. Store the value in Cell A1 to a text file located in C: with name TEXTFILE.TXT (Replace the existing value)
2. Store the value to the text file as additional line item (append records)
I have a spreadsheet where on a weekly basis data is copied in to various tabs. I then have a "formula" tab where I have a single line of formulas which look up the various data tabs and extract the results I want to show.
Currently each week, before I import the new data into the various tabs, I copy the last row in the "formula" tab and paste to the line below it. This contains all the working formulas. I then paste values only on the line that I copied, thus "locking in" the values it calculated with that weeks data. This means that each row then contains the results with that week's data, and this will grow week by week.
What I am looking for is a formula that automates this process. So let's say that row 30 is the final row of data on my formula tab, it contains the formulas I want to use. I would need a macros that does the following:
1. Looks up last row (row 30)
2. Copies last row (row 30)
3. Pastes to next empty row (row 31)
4. Pastes values only to second last row (row 30)
I'm not too great with writing macros, I've found plenty that can find the last row, but I can't get them to work to highlight that row.
I need to multiply an array of logical results ( returned as {1,0,0,0,1,0 et.}) with a text array (a reference column) and return the text in the reference column in case the value in the logical array is 1.
View 3 Replies View RelatedAm currently working on a sheet (Attached), which is attempting to take data input in a sheet (PnL tab), and work them into manageable data on the Frontsheet, via the seperate F&O and Equities tabs.
Now, in the Equities section of the frontsheet, my array formula to pull through the data from the Equities sheet is bringing up an erroneous blank line, despite the forumal being told to ignore blanks.
As far as I can tell, this is referencing formulated tabs, and how to stop this from happening.
I have a problem with an array formula. The file is attached for better comprehension.
I've built a multi-criteria lookup based on INDEX and MATCH.
The idea is in Sheet 1, in column F to put 1 (one) if each record of columns A,B,C and D (all combined) match any record in Sheet DATA. If not then 0 (zero).
The array formula works nice for each of the records. However, I get a ZERO in Zinc! If you check DATA, you can see that Zinc is there, from line 221 to 224!
I can't understand why this happens since i've pasted the formula for each of the lines in column F.
I have the following two columns, and would like to obtain for each individual Company, the corresponding Country values excluding duplicates as text in a single cell.
Company 2Country B
Company 2Country C
Company 3Country C
Company 3Country C
Company 5Country A
Company 5Country C
Company 5Country C
For example:
- For Company 2, a cell containing "Country B, Country C"
- For Company 3, a cell containing "Country C"
- For Company 5, a cell containing "Country A, Country C"
I've approached generating an array using an IF statement, as in =IF(INDEX(A1:A8="Company 5",,),INDEX(B1:B8,,)," "), which returns the following array: ={" ";" ";" ";" ";" ";" ";" ";"Country A";"Country C";"Country C";" ";" ";" ";" "}.
The question is: how do I get that array to produce, as text in a cell: "Country A, Country C". Note that the duplicate Country C has been removed.
There are a few "StringConcat" User-defined functions that I've found elsewhere on the internet, but they don't seem to be able to handle to conditionally generated IF Index array, which I would think is key to parsing between Countries corresponding to each Company in the list.
Simple question: Can an Excel Macro insert a line of boilerplate text under the last thing copied to a worksheet? I want to paste something into a worksheet, and add a message under it, and a blank line.
View 5 Replies View RelatedSheet1
Removed screen shots - Please DO NOT use actual personal information, sample data only! - Moderator
Some of the people on this list have a second name entry on the line below their first name entry. ALL of the people on the list have an Address2 on the line below their Address1.
How would I move the second name entry into the cell next to the first name entry, and then concatenate them, and then also move the Address2 entry into the cell next to Address1 (but not concatenate these)?
Like this:
Sheet1
I've been searching the forums and web trying to figure out how to extract a text value from a 3D array result, to no avail or well beyond my understanding.
I've attached a file which shows an abbreviated version of the search (from the original 49 col x 400 row, which shows the name to date array which can return a sum.
What I'm looking to do is identify within this array result if there has been a text entry, and return the text as opposed to a sum of the remaining values. This is essentially a double check to make certain that there aren't hours scheduled when other events such as vac/loa/etc are also booked.
I need to add extra text line in textbox by Toggle Button or checkbox. The problem my extra line every time I select it makes extra lines
View 5 Replies View RelatedI am entering large amounts of text into a text box uisng VBA code.
In VBE I need to line break the text using "space _" in order I can keep the text manageable on the screen. Problem is VBA is telling me there is a limit to the number of line breaks I can have in a sub, how I can solve this, ideally would like to paste a para of text at a time.
I'm using the following code to read a text file that I downloaded from a mainframe file.
Do While Not EOF(FileNum)
Line Input #FileNum, myLine
Debug.Print myLine
Loop
It reads and prints the first line, but then drops out of the loop. According to the help file, "Line Input" is looking for a carriage return (Chr(13)) or carriage return–linefeed (Chr(13) + Chr(10)) sequence. I have pasted a sample of the text file below. I'm not sure what the characters are at the beginning of each line, but perhaps I could find a way to replace each of them with a carriage return.
SAMPLE:............................
I use the Append command to write a line to a text file. I have about 3 excel files appending data to the same text file.
If someone were to write a line in error I would like to have the ability to delete it.
I have a problem here.
I am trying to create an inputbox such that the text will display line by line instead of one line display
EG:
Selection = InputBox("Enter Your Selection No. 1 for aaa. 2 for bbb")
Above code will display text as:
"Enter Your Selection No. 1 for aaa. 2 for bbb"
But i would want the text to be displayed in:
"Enter Your Selection No."
"1 for aaa"
"2 for bbb"
I have a text document with 1000 lines. All the lines are in this format
important< %#$ other words
other important thing < %Z$different words
some important text < $# some other words
I want to know if is such a excel filter that will delete all the characters starting from "<" until the end of the line.
So, the output of this 1000 lines will look something like this:
important
other important thing
some important text
I'm using the formula '=LEFT(A2,4)+0 to shorten a numbered line of text.Is there a way to adopt that formula to do the same thing to an alphanumeric line of text (i.e. 80A0267AA1 to read 80A0267) or even better substitute a "-" in place of the "A" (i.e. 80A0267AA1 to read 80-0267)? I know this is really picky, but I figure it's worth a shot.
View 10 Replies View RelatedFile I have attached or made a link for here Untitled-2.jpg
I am trying to make that amazon cell, all text to appear as one line like the second line foreign conversion fee i want it to appear like that. Rather then having to delete and modify 100's of rows like this.