I use the code below to automatically update formulas with the most recent data added daily to my spreadsheets. Can I copy this module, edit it so that instead of finding the last_cell_value it finds the value one_week_ago, and apply it to the same spreadsheet that the Last_cell_value module is used?
View 9 RepliesAfter all the awesome macros I've obtained with the help of all of you, I now have over 30 macros, each in its own module. I have tried without success to re-name the modules with no luck. How is everyone organizing these?
View 2 Replies View RelatedLet's say i have 2 Modules on my VBA forms, is it possible to Copy all the Codes in Module 1 and Paste it to Module 2 by using a Command Button?
View 1 Replies View Related i got a question how i can make a msgbox in one module with 'Yes' linking to a different module.
such as: this is located in module2
Code:
If MsgBox("Do you want to activate module1?" & vbCr & _
"" _
, vbYesNo, "Choose") = vbNo Then Exit Sub
i want that vbYesNo different
No as in Exit Sub
yes as in activate Module1
What i have at the moment is a module that contains code where i call a different module that i use as a procedure.
Module1
Code:
If Sheet1.Range("C4").Value < Sheet1.Range("A2").Value Then
If Sheet1.Range("K4") = "" Then
MsgBox "Please check 06:00 tasks not done yet!"
Cell = "Range(" & Chr(34) & "F4" & Chr(34) & ")"
If Sheet1.Range("C4") + 0.042 < Sheet1.Range("A2") Then
Run "EmailProSheet"
End If
End If
End If
EmailProSheet is what i call but now i want to use the variable "Cell" in the procedure as well?
Module3
Code:
MsgBox Cell
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
[Code]........
As it is now everything is working fine but does not return a value in "Cell" if the procedure is called. Is there another way?
I recieve an error :
Run-time error '50289':
Can't perform operation since the project is protected.
When i try and run my code.
The code does add parts to modules and workbook events, but I need the code to be protected.
I have some code that loads and unloads a command under the "DATA" menu bar. Is it possible to add an item to the menu bar and put the commands under the new menu item? For example: Add "XYZ Consulting" as a menu bar item so it would like as follows: File, Edit, Insert, Format, Tools, Data, Window, XYZ Consulting.
View 5 Replies View RelatedI am importing some external data and I need to move/modify columns using VB. My spreadsheet looks like this: ....
View 9 Replies View RelatedI have a code here, which thanks to Jindon helps on yesterday, But now, I want to change the code to count by column instead of rows, to continuous copy & paste my date,
How can it to modify:
Sub test()
Dim home As Worksheet
Dim Filename As String, myDir As String, fn As String
Set home = ThisWorkbook.ActiveSheet
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = False
If .Show = -1 Then
Filename = .SelectedItems(1)
myDir = Left$(Filename, InStrRev(Filename, ""))
fn = Mid$(Filename, InStrRev(Filename, "") + 1)
With home.Cells(Rows.Count, "E").End(xlUp)(2).Resize(2)
.Formula = "='" & myDir & "[" & fn & "]MAN_SUM'!k6"
.Value = .Value
End With
End If
End With
End Sub
Find below a vba code that i attached to a button that converts my print area in excel to PDF via Cute PDF writer.
Sub PDF_Sheet()
Dim Filename As String
With ActiveSheet
Filename = .Range("A1")
.PrintOut Copies:=1, ActivePrinter:= _
"CutePDF Writer on CPW2:", Collate:=True
SendKeys Filename & "{ENTER}", False
End With
End Sub
I need a VBA code for the Excel sheet that i am cuttently working in that will do the following:
i have text and pictures in range O86:W97 that must be automaticaly romoved & replaced by another range after the above mentioned code hase complete
the range that must be replaced with the following,
O101:w112 Pdf code must run then
O113:w124 pdf code must run then
O125:w136 pdf code must run then
O137:w148 pdf code must run then
O149:w160 pdf code must run then
note that the ranges run in a sequence 101to112 then 113to124 then 125to136 and so on
Now this needs to be repeated 190 time starting from range O101:w112. The range O86:w97 forms part of my print area of A1:W97, this means i will have 190 PDF saved pdf sheets when i the vba code is complete.
I have one excel file with three sheets marked 1,2 & 4
Sheet 1. is the place we add the description and the member number. When a member number is entered it gets the member name from sheet 4.
Sheet 2 is the page I need to change. Currently we can enter any score number in each box between 1 to 5. If we try to enter other numbers we get an error message.
I need to change the form so we can enter 1 to 10. Any other number needs to generate the error message.
I am facing a problem with programmatically Add or Modify the Validation of cells...
The sample code in the Excel 2003 VB Reference is:
I've (almost) got a macro that modifies & saves a file. I don't want to post it yet because I need to clean it up and I've got a ton of REM'd out statements that I need to flush before I go public....
How can I modify it to open each file in the folder? I'm looking at several thousand files that need to be changed and put away in the correct folder.
(and I say almost because I had it and accidently ran it on my personal workbook and it deleted itself....
I need to run two macro when I modify a value in a cell
Here is the code for the macros:
modify this code to save as .csv? I tried just putting it after the file name and that didn't work.
View 2 Replies View RelatedIs it possible to modify this code so that it will give the lowest values the opposite ranking from which it is now. (Please take a look at the example sheet)....
View 5 Replies View RelatedDue to the lack of time and not being able to make it work otherwise this is what I have right now:
If Range("g59") = 1 Then
Sheet1.Activate
Application.Run "Edit_view"
Sheet3.Activate
Application.Run "Edit_view"
[Code] ...
There was a formula to enter sheets names in a cell range (ie: A1 would have value SHEET1, A2 SHEET2, etc) and use a FOR loop to cycle through the named sheets.
i have admit forms for multiple patients, about 200 or so already done, and its set up to make an upload sheet, which we then add all of them to one big file to import to access. i am in the process of cleaning things up, but we need to add a formula to a cell to determine length of stay, and several similar things, but i'd like to not have to go and do it file by file.
is there any way to update all the files at once? some kind of automation? then changes would be in the same cell for each file.
or maybe some way to do a batch modify or something, so it can make the change, and run the two macros, one to make upload for file, one to dump it in the file that goes into access.
It's a linked post from:
[url]
I managed to do the combine the row if column B matches. However column D(quantity, number value), i want the quantity to add-up if column B matches. Any idea how do I modify the code below to do that?
for example:
TDG-**002 Tuna Cheese Pizza Bar (KG) KG 30
TDG-**002 Tuna Cheese Pizza Bar (MG) MG 30
TDG-**002 Tuna Cheese Pizza Bar (KG) KG 30
will combine to become
TDG-**002 Tuna Cheese Pizza Bar (KG) KG 60
Hi I have these formulas that look in a specific column and if the argument is true then it sums the numbers in the same row but different column.
I need it to count instead of sum. These are array formulas so in order for them to currently work you must press ctrl shift enter.
{=SUM(IF('sheet1'!I7:I510>D7,'sheet1l'!G7:G510,0))}
{=SUM(IF( 'sheet1'!I7:I510>B8,IF( 'sheet1'!I7:I510
I'm using sum to calculate numbers can I modify it not to calculate numbers? like it'll return blank or something else. Because it calculates alphabets in a row and returns a 0.
tSumthing = "=SUM(RC4:RC[-1])"
then based on the 0's I got above, it'll delete the row. I got diffculty modifying the formula below.
Columns(1).SpecialCells(xlBlanks).EntireRow.Delete
i use the formula below in a wb. When it runs its searches col A for what ever is in H36. Is there a way to make it search col A & B for what ever is in H36?
=IF(ISNA(VLOOKUP(H36,'PRICE-GAS'!$A$149:$N$6000,5,FALSE)),0,VLOOKUP(H36,'PRICE-GAS'!$A$149:$N$6000,5,FALSE))
I'm constructing a dynamic chart and I'm having difficulty naming the series formula.
I wish the Y values to pick up values in a different sheet and have entered the following formula :
=Rnd4Projects.xls!,Timeline!ChtYLeics
Workbook is called Rnd4Projects and the data is contained in a different sheet, called Timeline.
However, I keep getting an error message to say there is a problem with the formula. how this formula needs to be entered in Series Values box?
I need to touch(modify) my file stamp for test, is it possible using vba?
View 3 Replies View RelatedI have an interesting dilemma. I have Microsoft Excel 2003 and in one workbook I have a choice of certain colors for autofill and for font colors. In another workbook I have a DIFFERENT pallete of colors to choose from. Both workbooks are in excel 2003. Is there a reason why one workbook has one set of colors and another does not? Also, is there a way to change the second workbook so I have the same set of fill in colors in both?
View 2 Replies View RelatedI have the following formula:
=TEXT(D2-C2-TIME(,60,),"[h] h
s: m mi
s")
It's taking out 60 minutes for lunch. Now I want to use this to calculate straight time and not deduct any time. How can this be modified?
I would like to insert images (like a questionmark symbol) into a spreadsheet I'm working on, and then when the user hovers the mouse over the questionmark a "help-text" pops up. I've noticed that it doesn't seem to be that simple making a tooltip, however, is it possible to replace the small red triangle that is inserted into the sheet to indicate a comment with an image?
View 14 Replies View RelatedI have a workbook with lots of analytic data from my company which I want to share with other people not associated with the company. Obviously I can't share private data so I'm wondering if theres a quick way to modify all the data I have with 'fake numbers' which means I could share it. With fake numbers no privacy rules will be breached etc.
Is there a quick way to randomly change all numbers by +/- 10-20%?
[Code] ....
U17 = 17
Sum($T$17:$T33) = 33
Result that I am seeking,
[Code] ....
I am collecting data the temperature of an animal, i am also collecting ambient temperature at the same time when i plot the two data sets there is an 89% Correlation so i know the animal temperature is affected by the ambient temperature. i want to be able to take this influence of ambient temperature out of the recorded temperature. I dont think its a linear relationship
View 5 Replies View Related