I frequently review DAT files. These DAT files use a variety of characters as delimiters. I'm trying to devise a macro that will perform a text-to-column operation, using the content of a specified cell as the delimiter.
Here's my code, which uses - (ASCII 0254) as a delimiter:
Code:
With Sheet1
Range("A1", ActiveCell.End(xlDown)).Select
Do
[Code]....
Instead of having the delimiter hard-coded, I'd like my macro to use whatever character I enter into Sheet3.Range("i5") as a delimiter. That way, I can easily change my delimiter character as necessary.
I'm currently trying to compare all of my companies data sources for staff, ie helpdesk, telephony, ldap etc. and I have made quite a bit of progress, mostly thanks to this forum, however one issue I am running into, is the fact that when I use the text to columns funtion (in a macro) it's splitting up names which are double barrelled into 3 columns. The names are in the format of...
I am trying to create a formula that is able to calculate an end result based on the cell contents of 3 columns, the results are predefined in 3 other columns, here D, E & F
For example:
Column A Column B Column C Column D Column E Column F Column G (Results) Royal Mail 1 100 2.8 2.3 1.2 Royal Mail 2 100 2.9 2.4 1.3 Royal Mail 3 100 3.0 2.5 1.4 DPD 5 200 4.5 2.8 1.5 DPOST 1 100 1.2 3.2 1.7
I am trying to create a calculation that in Column G will work out, IF Column A=Royal Mail AND Column B=1 AND Column C<=100 THEN Answer is [@[Column F]], IF Column A=Royal Mail AND Column B=2 AND Column C<=100 THEN Answer is [@[Column F]], IF Column A=Royal Mail AND Column B=3 AND Column C<=100 THEN [@[Column F]], IF Column A=DPD AND Column B=5 AND Column C<=200 THEN [@[Column E]], IF Column A=DPOST AND Column B=1 AND Column C<=100 THEN [@[Column D]]
Here are an example of what I have tried, amongst many...
=IF(AND(AND([@[Default Post Postal Service]]="Royal Mail"),AND([@[Default Postal Format]]=1),AND([@Weight]<=100)),1,0) Everything is zero.
I have a workbook with 2 worksheets, A and B. Sheet A contains source data and sheet B a pivot table of this data. I want my user to click on a cell within the pivot table,click a button that runs a macro to find the instance of this value within Sheet A. I did record a macro but it did not work.
Been racking brain, searching through the forum here, and my Excel 2003 Bible all day trying to figure out this problem to no avail. I would like to clear the contents of any cell in a given range if the cell immediately to the right of is formatted as bold.
I have some dynamic named ranges in sheet1, and in sheet2 I have data validation dropdown list which has the names of all the DNR's in it.
What I want it to do (in sheet3, no less) is for the user to be able to pick a named range from the dropdown list, and have a particular column in sheet3 then display that entire named range.
I have made a 'data' worksheet which is the source for the data validation in sheet2. Each item in the list identically matches the name of each of the named ranges. I was hoping to be able to use some form of =INDIRECT but alas, no such luck.
So it's almost like a copy and paste function I'm after, where:
If you pick "Schedule_From" out of the DD-list, then DNR 'Schedule_From' is what is pasted in Column B in Sheet3.
I have a string of text in one cell on Sheet 1 (ie., A1, Sheet 1), here is a excerpt:
A-dec International Inc., A. Bellotti, A. DEPPELER S.A., etc ...
What I need to do is split the cell into separate rows, using the comma as a delimiter. I will be reading the cell from another sheet and need a formula that will provide me with
A1: A-dec International Inc. A2: A. Bellotti A3: A. DEPPELER S.A.
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.
I thought this was a pretty simple formula but I am having difficulty creating it. I am attaching a little test spreadsheet. Sheet 1 is where the data will be entered. The Reimbursed column has a drop down choice of yes or no. The next 2 columns are the cost of registration and the cost of accommodations. On sheet 2 is where I would like the formulas. So in cell A4 I would like a formula that says if B3 on sheet 1 is Yes populate this cell with the contents of Cell C3 only, B4 of sheet 2 would then be B3 if A3 on sheet 3 is Yes and so on with the Not reinbursed if sheet 1 the Reimbursed column is no.
I am just learning VBA. I need to move to a cell location that is named in a range. As follows:
The cell "Sheet2!A1" has a formula that results in "Sheet1!B3". I would like my VBA code to read that cell and select Sheet1!B3. When using something like:
Application.GoTo Range("Sheet2!A1")
I end up of course at Sheet!2A1. I want to end up at Sheet1!B3 or wherever the cell reads at the time the code is read and operated on.
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column <> 13 Then Exit Sub If InStr(Target.Value, "Other (specify in next column)") Then Columns("N").Hidden = False ElseIf WorksheetFunction. CountIf(Columns("M"), "Other (specify in next column)") = 0 Then Columns("N").Hidden = True End If End Sub
but I have a lot of columns that I need to perform as above and I have put the code together as below
Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next
If Target.Column = 13 And InStr(Target.Value, "Other (specify in next column)") Then Columns("N").Hidden = False ElseIf WorksheetFunction.CountIf(Columns("M"), "Other (specify in next column)") = 0 Then.................
Using the above code, when I selected more than one cell anywhere in my workbook and pressed delete I was bugging out with a runtime error 13 message. You can see from the above code that I inserted "On Error Resume Next" - this got rid of the runtime error 13 message, but now when I select more than one cell and press delete, hidden columns are incorrectly revealed in my worksheet. how I can extend the working code at the top of this posting so that it works for a number of different columns in my Worksheet i.e. without the runtime error 13 occurring and without columns being incorrectly revealed.
I have tried experimenting with LEFT, RIGHT functions. I want to edit a text string using a delimiter. For example: "NOS|NOS". I have a column of 256 rows of such entries, a name; the pipe; a name. I need to be able to strip out one side or the other using that pipe as the delimiter, leaving only "NOS". With 256 rows of items with this format - the length of the text string will vary.
I have a text file, which is been imported to excel sheet. There are 2 fields in the text file. Each field has to be placed in one column. The first field has values with space. Due to this the first field is split into 2 fields and totally 3 columns are imported in the excel sheet. Have set .TextFileSpaceDelimiter=True so that it would split the 2 fields, but since the first field has space in it, it splits into 3 fields. Is their any alternate way to obtain the above?
How do i split some text in range if it does not have a delimiter? I would like to have a comma after each letter/number or have the option to split to individual cells
I know how to use the vba split function with a delimiter but cant figure this out.
I have a script to import all the data in a text file into an excel sheet. However, the data that is imported are in the first column of every row.
for e.g. 123454566788329929201012827192019128278111111111abc213123123123123
there is no comma nor / to separate the string of alphanumerics is there any vba script that i can incorporate to separate the first 3 digit in the first column then 5 digit in the second column then 7 digit in the 3rd columns and etc.
Search a worksheet for a user defined text string, and have excell return the contents of a predetermined column in the same row in which the text string was found.
A prepopulated worksheet has the text "gold" entered in cell T278.
1. user searches for "yellow_metal" 2. Excell finds "yellow_metal" in row 278, say in cell A278. 3. Excell then goes to predetermined column (programed as part of macro or VB), say "T", and returns the text contents of the cell in that column, T278 in this example. 4. Excell returns "gold"
I am looking for a solution to split text from numbers.I have found a couple examples on the web but I cannot get the examples to work with the correct syntax to function.
Sample cells. The string could be any integer or floating point number with text. (The text is always after the number.)
I have an excel column that contains the vbnew line box between two pieces of data. If I want to do text to coulmns how do I choose the vbnewline as the delimiter?
I have one table in word format and a sheet in excel:
word table: Part # ID 1001 C1,C5 1002 C2~C4,C6-C7 1004 A10
excel sheet : ID Color part # C3 white C1 blue 1001 C2 red A10 black C4 red C5 greed 1001 C6 grey C7 pink
I need to write a script which will take an id in excel sheet , then look up the part number in word table , copy the part number into excel sheet. So the sheet will look like this:
ID Color part # C3 white 1002 C1 blue 1001 C2 red 1002 A10 black 1004 C4 red 1002 C5 greed 1001 C6 grey 1002 C7 pink 1002
at the end, I need to export the sheet as text file with tab as delimiter
I am having a problem reading from a file. I am working with some old code. I am reading information in from a txt file. The information on the txt file is separated by commas on each line. My question is, how can I read in for example, the third section on the first line. By section I mean...
line 1: section1, section2, section3, section4, section5
I want to read section3 into a string variable. If anyone can help me please post. I am having the hardest time finding this online.
I have a listbox populated with a number followed by description. How do I write code to extract the number and description to different cells. Listbox example: "1234ES - Project Description". Now I want "1234ES" in one cell and "Project Description" in cell left of number.
I am trying to convert text to rows but in the same column Take the following example:
Column A: 1-2-3 ab-cde-fg 54-ty-12345 the waht i am looking is convert it to the following format: (the - is the delimiter) Column A
1 2 3 ab cde fg 54 ty 12345
i have found the following VBA code on those forums but i don't know how to edit it to suit my needs
Sub Txt_To_Rows() Dim arrText() As String Dim varItm As Variant Dim rngText As Range Dim rngCl As Range Dim i As Integer Dim j As Integer Dim x As Integer Set rngText = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row) j = 2...........................
I am attempting to concatenate the dept numbers for each user in a list. For example, the first user listed below is associated with 6 depts. I would like the resulting macro to concatenate the dept's into one cell next to the Name.
Below is an example with the solution I am attempting to produce.
Sample Table
Name Dept
Abbruzzese,James L 188100
[Code] ......
Output
Name Dept
Abbruzzese,James L 188100, 231100, 600377, 600656, 600663, 600708
I have created a desk planning spreadsheet in excel. Each week a mailmerge sends out an email to everyone in the office asking them to confirm when they will need a desk in the next week.
As the normal employee doesnt get involved in desk planning I want them simply to state whether they will be in the building or not by entering "GH" (our building) in a cell under each day in the email they get sent.
When the reply comes in we select the cells from the email and then paste them into the desk planning spreadsheet. -----
WHAT I NEED TO DO: Select some cells (must be in the same row - that we have just pasted in). Press a button on the Worksheet labelled "Auto Assign Desk", which does this:- For each cell that contains "GH", replace with the right-most cell to the left of the selection that starts "GH_"... (this is the start of a unique desk reference (GH_1_1, GH_1_2,etc.) -----
This will assign the employee to the desk they were sat at last, when they are in the office next week.
