I have two formula's available for a cell. Its actual value, and a previously forecasted value. The actual value is found using an index formula and the forecasted value is a forecast function. i want to create a macro and link it to a button such that i can change which formula used in the cell accordingly. Is this possible?
View 1 RepliesI have a group of huge data as follows. I want to create a macro to just choose the first immediate date of each individual (in another word hide the second rows onwards for each Name). How can i do it?
Name
Travelling date
A
2-3-2014
A
4-4-2014
A
5-7-2014
B
3-3-2014
B
8-9-2014
I have the following
Private Sub cmdSave_Click()
SaveRow
On Error Goto ErrorHandler
ThisFile = Sheets("Data"). Range("B2").Value
ActiveWorkbook. SaveAs Filename:= _
"C:Documents and SettingsMy Documents" & ThisFile, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
Workbooks.Close
ErrorHandler:
MsgBox ("Error locating file. " + Err.Description)
End Sub
This saves a workbook with the File Name based on a cell in a worksheet in the above Path Name.
Is there a way that I can change this code to ask for the Path Name with the File Name, from the worksheet, as a default.
I would like to write a macro that would insert a hyperlink to another file an would prompt the user to select the location of the file once the macro was run. I am not sure how I would get my macro to prompt the user to define the location (which is located on a network drive).
View 7 Replies View RelatedI am making a macro that requires that user to enter a directory path into a input box and the macro will open the file. Right now it works fine bit i would like to change the code so that instead of using an input box it brings up a box that will allow them to browse files on their computer and then select one to open.
View 4 Replies View RelatedI am after some VBA code that chooses each value from a drop down list (in cell A1) then copies a separate sheet 1 (sheet 1) into another workbook (can have both workbooks open at the same time or the first step will have to create a workbook) with the name of the added sheet renamed to the value chosen from the list. When the second value is chosen then sheet 1 should be copied into the newly created workbook as well.
Sheet 1 has lookups that are updated when the new value in the drop down list is chosen
There are around 85 values in the drop down list so I'm expecting a workbook with 85 tabs (copy pasted values and formats)
I'm trying to extend the range of a summation formula with a macro.
I've attached a dummy worksheet. I've named each cell in row 8 as well as the summation ranges they are calculating.
I am looking for the result of the macro to have each total's formula range to extend to to row 7 of its respective column.
I have a macro in a workbook that runs automatically when I open my workbook. The purpose of it is to find the last non-zero cell in column A of all but three worksheets in my workbook that are named 1 to n and enter the current date in the cell below it.
Once this is done, it is supposed to autofill the formulas from the last non-zero row in columns B and D down into the row that has the current date in column A. I don't understand what is wrong, because at one point the macro did what it was supposed to.
Since then, I added a "for loop" that makes the macro repeat for n number of sheets as opposed to one, and now all it does is adds the date in the last non-zero cell in column A in every sheet 1 to n....it no longer autofills the formulas down in columns B and D.
Here is a look at my
Private Sub Workbook_Open()
Dim xlWs As Excel.Worksheet
Dim fillRow As Long
Dim rng As Range
Dim i As Long
I want to link cells from one sheet (totaldata) with the corepsonding cells in an another sheet (data), the problem is that the columns in these sheets to be linked are not placed in the same distance from each other - so a standard formula can't be applied her. I came up with the following piece of
Sub total()
Worksheets("totaldata").Activate
Range("C3").Select
ActiveCell.FormulaR1C1 = "=SUM('data'!R[2]C:R[79]C)"
ActiveCell.Offset(0, 1).Activate
ActiveCell.FormulaR1C1 = "=SUM('DE PL'!R[2]C[7]:R[79]C[7])"
This code links only two cells in the totaldata sheet, and this way I would have to make this code execute additionally over 30 such lines, which would force me to change the C[7] parameter to C[14] etc etc manually.
Is there a way to speed it up without having to produce such a long code?
I have a template work book that has several worksheets with numerous formulas thorughout.
For example;
=SUMPRODUCT(--ISNUMBER(SEARCH("MCSH",P9:P218)),--ISNUMBER(SEARCH("HWD",G9:G218))).
When I set up another template for a different crew I manually have to go through the entire work book and change the crew identifier (MCSH) in every formula.
I need to find a way to autosort a series of numbers, inputted manually for example, in cells A1 through A15. The autosort function needs to put the numbers in descending or ascending (either way, doesn't matter) in cells B1 through B15.
In effect, if the numbers inputted into the first cells change daily, then the correct order of these be automatically sorted so that they are in a specific order.
The closest I can come WORKS, but I'm using about 300 filter formulas to do so.
Macros that will insert formulas into cells, and I've been able to modify the macros to insert additional (simpler) formulas well enough on my own. But I can't seem to find the pattern of where to put the quotation marks! Here's the macro code that will enter my formula, including the formula (and the portion of code that the debugger highlights):
r.Offset(, 1).FormulaR1C1 = _
"=VLOOKUP(rc[-1]"",""'Blend List'""!""A2"":""E250"",5,FALSE)"
And here's the code in it's entirety:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range
If Intersect(Target, Columns("d")) Is Nothing Then Exit Sub
Application.EnableEvents = False
ActiveSheet.Unprotect
For Each r In Intersect(Target, Columns("d"))
If r.Row 1 Then
If Not IsEmpty(r.Value) Then...........
HI you know any macro to hide formulas in cells?
View 9 Replies View Relatedfound a great macro which copies data from multiply worksheets and pasts them onto master sheet.
I have adjusted the macro to my spreadsheet, but my macro should past formulas as values.
I am trying with no progress to join these parts of code to shorter part - to obtain formulas in format = 1 234 (for 1'123,342) in one. note, it is running after Application.Calculation = xlCalculationManual . It will makes my code much easier to read.
View 1 Replies View RelatedI am looking for some VBA programming that will allow me to use 1 button to consolidate rows (delete them). The problem I am running into is that the rows I want to delete have formulas in them so I can use "find all blank cells & delete" type of macro.
Capture.PNGCapture1.PNG
Attached are a couple of brief images on a small scale what I am looking to do.
I want a user to be able to have this sporadic list of choices, consolidate them so the are all one right after each other, then export to a word file for editing later on.
I am looking for a macro that will copy the row above it, to include formatting and formulas, however not the actual contents. I need the row that's being copied to be hidden from view, which is where the problem stems from. The macro is also copying the 'Hide' formatting and I want to exclude that. This is the current macro I am using:
[Code] .....
I have below code which pin out numbers from IBAN in Column P. Its formulas run by macro. It works fine. But what i really need is that instead of putting the formula in the cell, is that it compare the value from the formula, with the excisting value , in each cell . And it mark the cells where there are a difference with yellow colour I have been thinking how to do this but cant really work it out. If some have a better solution its ok but i need a macro for it.
have a look at below code, which change the cell and correct the error, but which i want just to compare the values.
I have attached a sheet to test with.
[Code] .....
Attached File : Testforcompare.xls
I have 2 speadsheets, I want to copy rows of Formulas from Spreadsheet 1 and copy them into Spreadsheet 2 but convert them into Values.
Is it possible to create a button on Spreadsheet 1 and assign macro to do exactly what i want? I also want the macro to look for the first empty row in the database so that the information being copied are on the next row and not overwritten.
I was tired of making my list smaller to upload it here, so I uploaded the complete thing on megaupload :P Here is the link:
http://www.megaupload.com/?d=1W6PBADE
On the bottom of the list is a button; "Add New Anime". When you click on that you get a UserForm where you can enter; Title, Type, Total Episodes, D/L Eps and Watched Episodes. When you press ''Add Anime" the entered values are entered at the bottom of the list, underneath the corresponding list names on the top.
Now there are 4 list names left (Left, Status, Status 2, and Progress (%)) I still have to make a option to enter Status 2 as well in the UserForm.
Now the question:
In columns F (Left), H (Status) and I (Progress (%)) are formulas. How can I copy those formulas automatically when I add something new with the UserForm? And can it automatically insert a new row, since I have to move the buttons down with every new title.
I have an existing macro that copies a worksheet and pastes it into another workbook, renames it and then attaches it to an email. My problem is that it pastes just the values. I need it to paste part of the original worksheet as values and part copy the formulas. So on the new workbook Columns A through F will be values only and G through Z will copy the formulas.
View 10 Replies View RelatedWriting a macro to insert two columns with IF formulas into a Worksheet. The 2 columns to be added would be after Column G on Sheet 1.
The new Column H would be
"=IF(G1>=Q#,G#/Q#,0)"
And the new Column I would be
"=IF(G#>=Q#,MOD(G#,Q#),G#)",
Where # represents the row number.
I am looking for a macro that will insert a new row and copies the formulas from the row above, but not from all the columns.
This is the current situation:
A
B
C
D
E
F
G
H
I
J
K
L
M
Name
Project Nr.
Work Order
hours
[Code] ......
What I want is that if column G contains "11374340" an empty row will be inserted above and the formulas from column E until M to be copied a row down. And the sum function in the last row of M needs to be changed automaticly as well (if possible).
I already have the macro to insert a new empty row which is as follows:
Sub InsertRows()
Dim i, LastRow
LastRow = Range("G" & Rows.Count).End(xlUp).Row
For i = LastRow To 1 Step -1
If Cells(i, "G").Value = "11374340" Then
Cells(i, "G").EntireRow.Insert
End If
Next
End Sub
I need to be able to copy a formula from a row that is 180 rows before the current cell.
I then need to edit the formula so that the rows all start at 6. then i need to change the column references.
The formula that I am editing looks like this after it has been copied from before:
=IF('Entry Form Portrait'!$d870="m",IF('Entry Form Portrait'!$n870="a",'Entry Form Portrait'!$a870,""),"")
So I need d870 to be changed to d6, the n870 to be changed to o6 (current column +1), the a870 to be changed to a6.
Then this resultant formula to be copied to the next column and a6 changed to b6, then copied to next column again and b6 changed to f6.
Then all 3 columns to be copied (or autofilled) down 105 rows.
I tried to do it by recording the macro, however it only works for the first time that i use it, and then just keeps repeating in the same place. I need it to use the cell i have selected as the starting point.
I was searching through this site and found this macro ...
View 9 Replies View RelatedI need to paste the format and formulas of the last row of data into a new row beneath it. I've seen a bunch of different end row functions, but which one is best and how do I only paste the formulas and formatting?
View 8 Replies View RelatedWhen I click this button, a new row is inserted. Depending on whether this checkbox is checked, I want some of the formulas to adjust.
Public Sub AddEntry()
thisone = ActiveSheet. Name
FinalRow = Range("A65536").End(xlUp).Row
Range("A" & FinalRow + 1).Select
Selection.EntireRow.Insert 'just inserted a new row
x = Range("T1").Value
Sheets("Calculator").Select
Rows(x + 3).Select
Selection.Copy
Sheets(thisone).Select
Rows(FinalRow + 1).Select...........................
I used VBA to write a number of formulas into various cells in a workbook. After the VBA is done running, I change the values in the cells that are referenced by the cells I wrote formulas in. The formulas do not update after I enter this new information. My calculation is set to automatic, and F9 doesn't help.
View 3 Replies View RelatedHow do I change the code so that the macro looks up the next sheet (instead of by sheet number, which is how the code was recorded) and transfers data to the summary page until there are no more new sheets. See attached zip.
View 6 Replies View RelatedI have never written a macro and when I record one I usually have trouble with the relative references.
I am trying to write a macro to transform formulas into values every month.
I want to transform formulas:
- across a range of tabs: each tab is exactly alike and is named page-1 to page 25
- on a different column every month (same column across all tabs)
- on the same rows: L168 to L227 and L266 to L277 (same rows across all tabs)