I am trying to set up a budget spreadsheet for a friend. The sheet will have the usual incoming and outgoings with a surplus at the end of each month. This surplus needs to be accumulated each month in a particular Account (Cell).
Once the amount in this cell reaches £5000 any surplus accumulated each month from that point needs to be deposited in another account (Cell).
I have created a very long switch statement, which is too long to be placed in one row in VBA. I have attempted to put a space and underscore at the end of one line and continue the statement on the row below by placing a comma at the start of the second line. VBA will accept my efforts, but when I run the statement in the immediate window, the following error appears.
"Invalid procedure call or argument"
I understand that there are certain rules where I can split a switch statement onto two lines, yet I do not know what they may be.
i am needing to issue a dos command in excel? basically i need to send a target link to a file. i cant use a hyper link for several reasons, and this is the only way i know how to go about this.
View 2 Replies View RelatedI have a cell (B10) that has an IP address in it. I want cell B11 to automatically reflect the same first 3 octets of B10, but the last octet accumulated by +1.
Example: 172.23.0.128 would be in B10. I would like to have a formula in B11 to increment B10 by +1 to show 172.23.0.129.
I will try explain what I want to calculate but cannot find the way. I want to calculate a result from two digits, then remember the result and add to this sum next digit typing in the same cell. Example:
colum A names
colum B numbers
In cell B1 I will write 5, after enter result in B1 is 5.
In cell B1 again write 7 result in B1 is 12.
In cell B1 again write 6 result in B1 is 18....and so on.
In a cell I have a description, say "FOOD" then another cell with amount say £2, then in another cell I want it to be looking and if it sees "FOOD" entered, it takes the amount £2 and adds it to a running balance.
Cell A1 = Food
Cell B1 = £2
Cell H1 = (looks and if Food) adds £2 to cell
I need a formula or script that accumulates data that a person enters into a particular cell.
[INPUT] a6=enter service requests closed today
[OUTPUT] b6=Service requests closed last month
[OUTPUT] c6=Service requests closed this month
I want to make this as simple as possible and the person enters her data in the same cell every day (a6). The script or formula should (in the background) then take todays total and and add it to the accumulated total and show the outputs in b6 and c6.
If a person enters data multiple times on any given day, the script or formula should recognize only the last entered number for that day. This will control user errors in entering.
I thought about just entering the raw data in a separate spreadseet and using 1 cell for each day, but then a person would have to enter totals on one sheet and look at the results on another seet. not good
I also thought about taking the above solution and combining them on one sheet. this would work, I guess, but I really need a sleek lean sheet that they add to the same cell each day.Also there is additional data that I still need to show on the screen and I really hope I do not need to move this data.
My problem is that I have a worksheet tab (RawTimeSheetData) which contains a whole series of week/timecode values for a range of people.
I want to accumulate the hours for an invoice period / job code combination. As an example in the tab InvoicePeriodSummaryTimes cell D6 i want to sum all the hours from RawTimeSheetData where both cells A6 & B6 from InvoicePeriod tab = cells D6 & E6 from the rawdata tab.
I have main worksheet (target a) that I am trying to populate data from target(worksheet) b. The data I am trying to get from target b changes every month,(declining balance) based on a new month. So how can I get financial data from different cell each month from "b" into same cell in "a"? (so "A" # would be overwritten in same cell based on new # from "B". I have tried VLookup but can't be doing something correct.
View 2 Replies View RelatedI am using a piece of track changes VBA code mentioned on this site, which among other things creates a new column that reports the number of any cell that has been changed (e.g., $K$32). What I would like to do is, next to that cell, report the title of the row in which that cell appears. In other words, if someone changes cell $K$32, for easier reference I'd like others to be able to see that this cell appears in a row titled "New Sales". I'll be happy to clarify with more specifics if need be.
View 2 Replies View RelatedI have a user form that has several tab pages on it when the form opens it checks to see if the user has entered there default info if it is empty it needs to go to the default tab so the user can enter there info. How do I do this using vba in excel 2000 I have not been able to find any code that will do this if I use the set focus option for a text box on the tab page it comes up with an error saying the item is not visible! The user forme is names = UserDataInputForm. The tab pages I have are
1st = InputDailyTimesPage
2nd = OverTimePage
3rd = DataPage
4th = DefultDataPage
the form opens using the 1st tab page by default
in the user forms inisilisation code I have this check
I have created a database of names & addresses from a software program called "CLIP" into excel, which in turn will become a label mail merge in MS word consisting of 900-1000 customers (for a mass mailing).
The problem is, the names were created last, first and I would like to change them to first last. Is there a way to do that without manually typing them all in again?
The names are in one column, so I cannot just switch the columns around.
I try to code some vba here's my example
1 A B C D
2 3 5 1 2
3 1 2 0 1
4 1 3 0 1
5 1 0 0 0
I want to sum in line 2 all values below but i dont know how to switch to next collum in vba
have been using the simple formula below to determine between a friend and myself whos turn it is next.
=IF(Q22
The setup here is I have multiple codes for a product. Coding structure is as follows (options are contained in parantheses):
N -(T,P) - (5L,6,7,8) - (DFL,DJL,DBL,UEDF) - P - (1,6,9,16,19,1H,1H9) - (g,s,blank) - (a,blank)
Clearly, there are a ton of options. What I'm doing is screening those codes and breaking them down into their component parts.
ex) NT6UEDFP1a: N - T - 6 - UEDF - P - 1 - a
ex) NT6DFLP16: N - T - 6 - DFL - P - 16
I'm trying to do multiple if/then scenarios for each option. However, the way I have it now, I'm getting a FALSE when I switch between these two examples (the dfl works, the uedfp generates false). (there are other codes which is why the first line is there. I'm just having trouble with this particular "nt/np" group)
=IF(OR(LEFT($A$14,2)="np",LEFT($A$14,2)="nt"),
IF(OR(MID($A$14,4,3)="dfl",MID($A$14,4,3)="dbl",MID($A$14,4,3)="djl"),[code].....
I have several phone numbers that I copy and paste from the web:
I.E:
(888) 456-7890
(333) 234-5678
I need to switch all of them to just standard numbers: They should look like this:
8884567890
3332345678
I go to "format" - "cells" - "numbers" but nothing works. I wonder if any formula can do it. It should work for ALL THE LIST, I want something else than change them one by one by hand.
I have made a little program for a customer at work which has 4 different worksheets.
Each worksheet works out a cost based on different prices.
I would like to make a 'Welcome' worksheet, with buttons on it so my customer can click the button which corresponds to the option he would like to cost, and then excel automatically selects that worksheet.
how to create buttons but don't know the code to but behind it. It would be nice to also hide the other 3 worksheets when they have not been selected by the welcome page.
Is there a way to use a switch statement with a default value, for when none of the expressions evaluate to TRUE?
View 5 Replies View RelatedI have written VBA code to open a separate file and then copy back and forth between the two files. However, when I recorded, I got commands like the following:
Windows("DestinationFile.XLS").Activate
then the copy/paste stuff then
Windows("SourceFile.XLS").Activate
The problem is that my "SourceFile" may have a different filename. I need the code to be robust enough to handle the possibility of a different filename. I tried the following, but it did not work:
Dim MarketProfileName As String
MarketProfileName = ThisWorkbook.FullName
Windows("DestinationFile.XLS").Activate
then the copy/paste stuff then
Windows(MarketProfileName).Activate
Im having trouble switching between windows that are open in separate instances of excel.
I used to be able to switch easily between these workbooks when they were both being being run in the same instance of excel.
But since having to change it so the workbooks are opened in seperate instances, i cannot get it to work, the code just fails when it trys to activate the first window.
I currently have the below code:
Code:
Sub CloseAndSave()
Application.EnableEvents = True
'//Activate InfoHub workbook and put a tick in the checkbox.
Workbooks("403 InfoHub.xls").Activate
Sheets("HOME PAGE").ToggleButton1.Value = True
[Code]..
Is there anyway to do this?
I am working with Excel for Mac. This has presented a number of challenges but I have successfully handled them. I am having a problem though and would love some input. I have a workbook that contains a pivot table. The pivot table is based on "Sheet1" of my workbook. The user will hit a button on the pivot table spreadsheet and the first operation is to select a file from their computer. I am using a function called - MacScript to open the finder window and select the file. The file name (with full path) is stored in a variable called filepath. The file consists of a single sheet - Sheet1 and I select all and copy the cells. I then go back to the original workbook, select my worksheet with the source data for the pivot and I again select all and paste the new information over the old data. I have a named range called pivotsource that references the 9 columns that are required for the pivot table. What I would like to do now is go back to the original workbook and close it, do not save anything, just close it like nothing happened. I have tried to close it by using:
/* Workbooks(filepath).Activate
ActiveWindow.Close
*/
Since I had a large amount of data that is in the clipboard, I will also have to deal with the message coming up asking what I want to do with the clipboard. My bigger problem is that the activation of the filepath workbook doesn't work. How can I get back to the original workbook? I thought about the index of the workbooks but I don't know if the users will have other workbooks open at the same time. If they did, I could not reference it.
I'm trying to work out what the Hotkey is to switch between sheets in excel.For example there is Alt+Tab for switching between windows, Ctrl + Tab for switching between open internet tabs, surely there must be a way to switch between sheets in Excel too, right?
View 3 Replies View RelatedIf I have an excel workbook open and then, select the side-by-side option to open another workbook, and I want to write a macro that will switch between the two - how can I do this? The problem is, I don't want it to switch by file names (one workbook constantly changes names), I just want it to switch between active workbook and inactive workbook.
Once I have the code to switch between active workbook and inactive workbook, how can I have my macro look at a cell and see if data is present and if so, drop down to the cell below.
Windows("Job Number Request form.xls").Activate
Range("C9").Select
Selection.Copy
Windows("Backup Copy of 2007 Capex Job Log.xls").Activate
Range("E570").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
I have multiple anded conditions both Boolean and numerical. For testing purposes, I wanted to be able to switch off individual numerical conditions in the If statement. It seemed like an easy matter that I could simply comment out the And statement of choice and recompile. When I tried this, I received a compile error.
Another option would be to use individual togglebuttons to selectively turn off the 5 numerical comparisons. I have no idea how to set that up.
how to do this with either commenting or togglebuttons?
The If statement
If OptionButton4.Value = True _
And ToggleButton1.Value = True _
And Range("G3").Value >= Range("N6").Value _
And Range("AV22").Value >= Range("AU22").Value _
And Range("E3").Value = "Long" _
And Range("G3").Value Range("BG7").Value _
And Range("H4").Value >= Range("L4").Value _
And TradeFound = False And _
DeletedFound = False Then
im trying to write a macro that will switch two selected values for example if a select a1 and run then a1 & a2 values would switch places.
so far ive tryed and got nothing
also is there like a wait for user input or mouse click function.
so when i run a macro there is a point where it would wait for me to say, select another cell before continuing?
Any shortcut key that allows you to switch between worksheets within the same workbook?
Found the shortcut - CTRL and Page Up / Page Down
I have a macro that unprotects a worksheet copies data from that worksheet and pastes it into a newly created and saved workbook. However I have two problems
1) I'm attempting to copy the data from the first workbook by selecting all cells and then when switching to the new workbook, select all cells and the paste special (values) then paste special (formats). This works when recording the macro but when I attempt to run it it bombs out.
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
2) How do I get the macro to switch back to the workbook it got its data from. The name of the source workbook varies and there may be from 2 to 15 workbooks open. Is there a previous active workbook command? or do I need to create some kkind of temporary value to hold the file name of the source workbook.
Hi I want to be able to use VBA to open a 2nd sheet when the spreadsheet first starts ,with
View 8 Replies View RelatedI have two pages in the userform. What I was trying to do is that certain textbox (txtDate) needs to be entered on the page1 before going to page2. The user can still select page2 however I want the vba to bring the page back to page1 if the data is missing.
Here is the code:
Private Sub MultiPage1_Change()
If MultiPage1.Value = 1 And txtDate = "" Then
MultiPage1.Value = 0
txtDate .BackColor = &H40C0
End If
It didn't work completely - the tab selection on top was on page1 however all the controls shown are still the controls under page2. I have to manulally click page1.
I also tried MultiPage1.Page(0).Enabled=True but it didn't work either.
How can I do the following:
I need a toggle switch that switches between protecting and unprotecting the worksheet.
It should display the text 'Entry Mode' when the sheet is protected and 'Filter Mode' when the sheet is unprotected.