I used the macro editor to create a "simple" macro to edit 7 rows of text. The steps were basic and simple - F2 key, backspace about 25 times, and down arrow then repeat these 3 steps 6 more times. The macro I got gave me the result of the very first cell as I was recording the macro. I think I remember the old Lotus 1-2-3 macro text - {edit}{backspace}{down}. Wish it were still that simple.
View 5 RepliesI was wondering if it was possible for a macro to edit a file by opening each .txt file, searching for the word "Reference", and then replace it with the word "Ref". Then save and close the text file.
There are no delimiters in the text files.
I have an xl doc in which one of the sheet's column A changes value every 1 hour...
What I would like to know is.. if there is a method in which i can copy these values from column A to a text file every hour...
The range of cells containing values in Column A also varies every hour.
Also, the old values in the text file needs to get deleted before the new values are updated every hour.
Have a macro that copies a formula from each of 100 workbooks to a new workbook. I want to display these formulas as text and want a macro or someway to display these cells as text. I have tried to record a macro that presses the F2 key, the home key and the apostrophe. This works for the one cell but provides the following macro that does not work for anyother cell.
ActiveCell.FormulaR1C1 = _
"'=VLOOKUP($A$30,'G:Variance Reports FY07[Salary Dist Var Repts_Cur Mth.xls]end of July'!$E$76:$G$200,3)"
Range("B3").Select
I need the values that are copied from the template to copy over in text form from the "Data" Tab. Secondly, the master sheet has multiple lines for each vendor. For the area highlighted in red I'd like for it to copy all cells in column C for the vendor and search the vendor by name. Then, move to the next sheet.
View 10 Replies View RelatedI want to create 3 command buttons (active X) on a worksheet to toggle between showing rows which only contain the below text in column L (range L9:L30) and showing all rows containg the options (However, I also have some blank rows in this range and i always want them to remain hidden.)
My text options are:
High Ť‚
Medium ՠҪ
Low ’á
The text arrives in the cells via a VLOOKUP
Is the chinese text a problem? i can't type it into VB.
I've been using the following macro to hide and unhide rows with a command button in the same sheet:
Private Sub CommandButton1_Click()
Toggle_Hide_Unhide
End Sub
Sub Toggle_Hide_Unhide()
Dim rngCell As Range
Dim TakeAction As Boolean
If ActiveSheet.CommandButton1.Caption = "Hide" Then
TakeAction = True
ActiveSheet.CommandButton1.Caption = "UnHide"
Else
TakeAction = False
ActiveSheet.CommandButton1.Caption = "Hide"
End If
For Each rngCell In ActiveSheet.Range("I9:I30")
With rngCell
If .Value = 2 Then .EntireRow.Hidden = TakeAction
End With
Next rngCell
End Sub
I have cells (all in one column) containing text separated by commas e.g. (SD-299, SD-200, SD-300)
I am trying to transpose the text in these cells into rows.
VB:
Sub SplitAndTranspose()
Dim N() As String
N = Split(ActiveCell, ", ")
ActiveCell.Resize(UBound(N) + 1) = WorksheetFunction.Transpose(N)
End Sub
The problem with that is that when transposing it does not shift the cells down (/ insert new rows) so I lose the data already in the cells underneath.
Also, that macro would only apply to one cell I would like to be able to apply it all the data in the specific column on my Sheet.
I need a macro that can look at multiple (say three) rows and delete those rows if they match another three rows in the worksheet.
For example:
1 0
3 1
5 7
4 4
6 5
8 3
1 8
5 2
3 9
6 5
8 3
1 8
7 5
If three rows are the same, then one set is deleted (it can be either bottom or top set). I would like to do this for rows 1-500 in the worksheet.
I have an autoshape with a text box in it. It is one of those "scroll" shapes. I want to be able to edit the text in the box with VBA, but I don't know how to call it because I can't seem to find the properties for it. I tried recording a macro to do it, and it called it:
View 5 Replies View Relatedhow can I open a .txt file with vba and replace alll "," with ";" ?
I am trying to develop a procedure that allows the user select a customer from a drop down list (using Data validation), thereby providing the user with a summary of customer information. That I can do. What I would like to do is allow the user to add new information in the summary section which is then appended to the customer record further down the sheet (see attached example). New data could only be added to the last two columns. The user would be required to press a form button to append the data.
The catch would be if the customer was selected again, the new data would also display in the summary section.
Is it possible to edit multiple =VLOOKUP formulas to add in a "[range lookup]" = FALSE without editing each one individually? I was going to use a find and replace for the "col_index_num" and add the FALSE to the end of that, but in this case my "col_index_num"s vary too much.
View 6 Replies View RelatedI have a macro enabled spreadsheet in which I am able to paste in the customer number as text, but I need to touch the cell in order to make the customer name populate in the next column.
Double click - enter and F2 -enter both will do this, but I'd rather not be doing this for 200 rows every time I have to use it. Is there a way to touch all of these rows without VBA? The spreadsheet was built by our IT department years ago and is rather fickle. I can't risk interrupting the code or I end up having to close it and start over. It's password protected.
Text to columns doesn't work.
I need to edit multiple validations in a worksheet. There are over 990 validation for each month. I want to change the word in the sentence. Example: April to May. Right now I have to edit each one seperatly. I have tried a macro but it changes to whole sentence. Find and replace do not allow editing in a validation.
View 9 Replies View RelatedI am using the below code to pull information from multiple sheets into one master sheet. Currently, it is funcitoning fine with one slight problem. In each of the sheets I have formulas copied down until row 500. However, some of the sheets may only have formulas populated up until row 50 for example. edit the below macro to where I am only pulling in the rows that have populated formulas?
Code:
Sub Consolidate()
'Author: Jerry Beaucaire'
'Date: 9/15/2009 (2007 compatible) (updated 4/29/2011)
[Code]....
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.
View 3 Replies View RelatedI use excel to read in an ASCII data file in which every line beginning with H is a header line and lines beginning with S are data line.
I want excel to filter out the header lines and output the file with the data lines only then outputs these in a new file.
I also want it to read the values from column 72 to 75 and subtract 6 from them then output them again to the same new file.
Sub read()
Open "d:/intouch/0502SPSS.LOG" For Input As #1
Dim i As Integer
Dim lines(9999) As String
i = 0
Do While Not EOF(1)
Line Input #1, z
i = i + 1
If Mid(z, 1, 1) = "H" Then
i = i - 1
ElseIf Mid(z, 1, 1) = "S" Then.................................
the problem is that the new file is generated but it is always empty
I have a spreadsheet with about 10,000 lines/rows of text and I want to insert 4 lines/rows under each of these. way to do this efficiently without having to insert under every line/row?
View 9 Replies View RelatedI need to copy information from one excel file and paste it into the same location (A1-A1, B6-B6, etc) in many other files all at once. Essentially I have several thousand files that need to be updated right now. I need to copy and paste both the cell contents and the formatting.
View 1 Replies View RelatedI have an excel template that is used for communicating in projects to teams their responsibilities and defining which stream/group has Accountability/Responsibility/Consulted/Informed (known as a RACI model) for the activity.
For any given project activity, there are rules:
One person is responsible for performing the activity (R)
Only one person can be accountable (A)
Many people can be consulted through the activity (C)
Many people can be informed of progress (I)
You must have an A and and an R for each activity
And one person can be accountable AND responsible A/R
Given that project activities can run to 100 + activities in some of our more complex programmes.... I'm looking to provide a flag in the spreadsheet for when an error has occurred and a gap in Responsibility or Accountability is created. (Less worried about testing for C's and I's)
See attached, I hope it is clear.
I believe this would be a very simple query for some but I am a complete novice in VBa and am using a msgbox in one of my file but I need to split the message text into multiple lines but I do not know how to do the same.
View 6 Replies View RelatedWhen I list my macros using alt+F8 the edit option is no longer available, any ideas what I have done?
View 9 Replies View Related- I have some buttons set-up to insert pre-set diagrams from sheet “FISH PARTS” onto sheet “THE JUMPER FISHBONE”
- However, before inserting a new diagram onto sheet “THE JUMPER FISHBONE” I need to make sure the default diagram components are all gone.
- There are seven components all with structure Like “BONE_*”
- If one or more of these components are present I want to display a message to confirm the user wishes to delete the default diagram and insert new chart (or abort is they do not wish to delete the default)
- some explaination of structure below
- spreadsheet also attached
I want my macro to protect my sheet again after it is done to allow editing objects and formatting rows. I am really stuck and have come up with the below, but it isn't working.
View 5 Replies View RelatedI have a datafeed program that offers multiple DDE link types to retrieve different types of data. After pasting the DDE links into Excel, all works well. However... To modify the links means manually editing upto 12 different array formulas located on different pages then pressing Ctrl + Shift + Enter after each to change the data source being analysed.
I found that I can manually copy and paste a few characters into each linked array formula, Ctrl+Shift+Enter but it needs to be by Macro referring to a couple cells (Say A1, A2) containing the variables that alter the data source being linked to.
I need to make a VBA Macro that refers to these two cells containing text, and modify the 12-odd linked array formulas.
eg. {=SUB33|getlocation!'N,pg,9,vp,A,30'}
In this example Linked Array formula, the bolded PG and 9 would be variables pulled from cell A1 and A2.
All other components of the array formulas stay unmodified, it is only the "pg,9" text that needs to change in each linked array.
sample merging file.xlsx
I have a data array of 7 col and 256 rows. I want to focus only on the first 2 columns though. Here is what is going on. In the first column I have numbers from 1-256, these numbers are merged to the appropriate size to accommodate the data in column 2. However, the data in column 2 is not merged, it consists of 1,2, or 3 lines of text. Excel deletes the text in the rows below 1 if I try to merge, excel does not allow multiple row merge selections. What I am looking for is a formula that looks at column 1, finds how many rows are being merged in column 1, then looks at column 2 and identifies the number of rows needed for merging and displays that text in only one row. Basically, it's a formula that merges multiple rows of text in col 2 depending on the number of merged rows in col 1.
No.Customer/Operator
1Fairbanks Morse /
Beloit, WI
2H+H Umwelttechnik /
[Code]....
I am trying to consolidate an .835 raw text file that is full of receipts and payments into rows by specific values/characters. Once opened within notepad/text format, I figured out that the ~CLP is the referencing value/character used to break up each individual payment.
I need to somehow use the ~CLP as the value(s) to sort the text into new rows, with each row pre-leading with the ~CLP.
For example:
~CLP*442.24*DTM*B6*PR~CLP*124.32...etc
New Row format needed:
~CLP*442.24*DTM*B6*PR
~CLP*124.32
At a high level, I have several thousand rows of data. For the sake of simplicity, assume there are two columns: 1) Name; and 2)A comment (optional, could be blank). See below for example..
Sheet 1
Row 1: Mike │ "Great to work with"
Row 2: Mike │ "Bad manager"
Row 3: Tom │ "Great guy"
Row 4: Mike │ (blank)
Row 5: John │ "Cool"
Row 6: Mike │ "Best boss"
On a separate sheet, I want to be able to somehow use a lookup function to combine all the comments for each name in a nicely formatted package (notice how I skipped the blank space so it wouldn't take up a line.
Sheet 2
="Comments for "&[ref to cell containing "Mike" in another sheet]
- Great to work with
- Bad manager
- Best boss
I need a VBA macro that deletes whole rows if the cell in one of the columns does not match one of three texts. For example, in column B, if the text in there isn't "Mary", "Joseph", or " " (empty space), the row must be deleted.
View 4 Replies View RelatedThe workbook has multiple sheets, and a cover sheet. Each project has a sheet and the status, costs, updates ect. are updated to the cover sheet.
Problem:
What happens is there is a cell which has conditional formatting on it, e.g. If text is Green then colour cell green, if cell text is amber then colour amber and if cell text is Red then colour Red. This works fine.
If the cell text is green then I need a number of rows hidden, if it is amber or red then show the rows.
I have found the following
Sub
If Target.Value = 1 Then
Rows(Target.Row).RowHeight=0
Else
End If
End Sub
Which I have changed to:
Sub
If F4.Value = Green Then
Rows("5,6,7,8,9,10.Row).RowHeight=0
Else
End If
End Sub
So this does not work, need to know why and how to make this work for each sheet I am on e.g. ActiveWorksheet?