I need a slight mod to the below code which works perfectly otherwise. What it does is it divides whatever is in column K by 30 and puts in its relative cell in column M( There a bunch of mini tables below each other with blank rows in between).
The thing is sometimes there are characters in column K like "N/A" for instance and the macro crashes since its only designed to take into account numerical and blank cells in column K.
I have a cell that is acutally a concatination of several other cells. The formula is something like this....
=A1 & B1 & A1 & C1 & A1 & D1 & A1
Cell A1 contains the formula =char(13). This forces a line break between each data element.
My problem is that on some peoples machines the cell displays with a square at the end. I'm not sure why it does this only on some machines. Is there a better way to do this or is there a way to suppress the square?
I use the macro below to loop through files, which works fine. Can the code be modified so that when a specific file is opened, the entire macro stops? Exit Sub only seems to stop the inner loop, but I would like the entire macro to be stopped.
Sub Loop_Through_Files()
Dim FSO As Scripting.FileSystemObject
Dim FF As Scripting.Folder
Set FSO = New Scripting.FileSystemObject
Set FF = FSO.GetFolder("D:")
DoOneFolder FF
[Code] ........
I'm trying to validate a string that can have only dots and numbers, but there can't be two dots in a row. String has to begin with a number and end to a dot
So these are ok
1.1.1.1.1.1.
and
2.452443.1.5.21.5.42131.
but
1..1.
and
1.1.t.
and
.1.1.
and
1.1
are not ok.
I have a macro on: Sheet101 (DAY 1) that adds additional sheets.
Example: When I click active button it will add Sheet102 (DAY 2) & can add up to Sheet130 (DAY 30)
I want a macro that will delete any of the sheets added from Sheet130 (DAY 30) to Sheet102 (DAY 2) But WON'T Delete Sheet101 (DAY 1)
Example:
If I have added 3 sheets I want it to delete those 3 Sheets:
Sheet104 (DAY 4) <--DELETE
Sheet103 (DAY 3) <--DELETE
Sheet102 (DAY 2) <--DELETE
Sheet101 (DAY 1) <--DON'T DELETE
& give a vbOkCancel MessageBox saying: Do you want to Delete Added sheets?
Okay the code below works, but one thing!
It deletes DAY 30 to DAY 20, then it skips DAY 19 to DAY 10, then Deletes DAY 9 to DAY 1.
So it's not deleting DAY 10 to DAY 19...
Do I need to add a wildcard?
Here is the Code: .....
need a macro that will open the following folder
c: my documentskatyexcel
I've got this macro I pulled from some other site and it's not working quite like it should. In my eyes at least...
OK, first, a little back story...
At work we used to have a custom Outlook form that was used for submitting office supply order requests.
The form was stored on our public drive for all to access it when needed.
Well, the form broke somehow and no one knows how to fix it.
We need something to replace it.
Keep in mind that we have no access to the Internet, nor can we send/receive emails outside of the company.
So, I created a simple form in Excel with a drop-down list of all the items in the supply cage, a form field for the senders name, etc...
What the macro does is, it copies the active worksheet the form that just got filled out) to a new workbook (dropdown data is on other sheets in the original) and then opens Outlook (2003) to send it as an attachment.
The only catch is that when the email arrives the only thing filled out is the form fields, NOT the drop downs...
The ONLY way I can get the drop downs to send with data in them is to leave the original open.
Oh, and for some reason every time I sent this while testing, it would name the attachment Book1, Book 2, Book3, and so on. I can't have that...
Here's the code I'm currently using:
In Col D of my spreadsheet, I have a list of security codes, in this list there is a security code "all", i need a macro that will add 1 to the code, so it reads "all1", now i need the macro to run as soon as new data is pasted to sheet "Lending", the ranges in Col D do change on a daily basis therefore cell reference for security codes is not fixed.
Can this be achieved? ............
Hello folks! Here's to hoping I can eventually be a source of information rather than questions. But enough about me - on to the topic at hand.
I have a single-source web query that I'd like to expand.
Here's what I have in the IQY file:
VB: AutoLinked keywords will cause extra spaces before keywords. Extra spacing is NOT transferred when copy/pasting, but IS if the keyword uses "quotes" ...
I have accounts that I need to compare to see if they exist on my system the account that has a listed date, exist on my system then if I can fill the dates in the accounts the match then I will be able to delete the other accounts that don’t have a date see attach file for more understanding.
View 2 Replies View RelatedI've searched through the forums for this information, and I have found a couple of similar issues that have been solved, but copy+pasting the code into my form has not had favorable results.
Basically, here is what I am after:
I have a sheet that already has 4 buttons active, with a handful of other subs that they are calling. I am wanting to be able to add new buttons and have them call a universal macro that will forward them to my existing subs. However, I'm wanting a variable to be set based on the Caption of the button that was clicked. For example:
I'm only starting to get into the Macro side of excel, and I've created a couple of macros to automatically format cell contents and the like.
However I'm having trouble trying to divide some cell contents by 100.
I have 2 files I'm working with, one contains information regarding cd's and percentages, however the percentages in this file cannot be formatted to percentages (so the powers that be say) for whatever reason.
I copy all this information into another file which does have the percentages formatted as percentages, the result is that the values get multiplied by 100.
So values that read:
100
50
50.25
for example, appear on the new sheet as:
10000%
50000%
5025%
Is it possible to write a macro that will divide these percentages by 100 so the read correctly as:
100%
50%
50.25%
the macro will have to work on selected ranges.
Is there anyone who can suggest a solution to my problem below ?
Basically, what I need is a macro which would ideally work like this:
if D32 = 1, then the background colour of D4 should be red
if D32 = 2, then the background colour of D4 should be orange
if D32 = 3, then the background colour of D4 should be yellow
if D32 = 4, then the background colour of D4 should be green
And then I plan to use it for columns E, F, G
I'm trying to come up with a Macro that once it see's the word "Rolls" in column M, I would like for it to go to the row below the word and divide the information on column K by 30
then for it to perform this formula for the next 17 rows and on the last row have the cell in gray color.
Then for it to keep doing this recursively down the column of the sheet and once finished to go to the next tab and do the same algorithm(there's like 40 tabs !!)
i have the following spreadsheet with dummy data however, there is a before and after scenario i have posted is this possible with a macro ...
View 9 Replies View RelatedI'm currently working on a program in which some sort of recursive looping seems logical.
The relevant part of the code I've currently written looks like this:
Sub hello()
Dim Max_stream As Integer
Dim j, jj As Integer
Dim P_sum, P_min As Double
Dim antal_in, antal_ut, rad_in, rad_ut, ut1, ut2 As Integer
Application. ScreenUpdating = False
Max_stream = WorksheetFunction.Max(Worksheets("Data"). Range("B65536").End(xlUp).Value)
For j = 1 To Max_stream
The problem with the code above is the last If, in which the function "rekursivloop" calls itself. I don't know if this type of formulation is correct, but for the cases the If condition is TRUE (ie ut1 <> ""), the function does not return any answer. Does anyone see an error in the logic or have some example for how to write similar recursive loops?
I create this spreadsheet as a loan schedule using average daily balance method. (1/payment is constant, fortnightly 2/interest is 5.5% per annum)
In the interest column, at the beginning of each month ( when the day is 1) the interest will be added up from calculation of previous month daily balance.
My idea is that at interest column(let start at 1/08/2013) if (day(A49)=1, average the 30 or 31 cells above E49, 0).
I will manually make adjustment for February where 28 or 29 days applicable.
I know there is a way to divide by 0 in Excel and get 0 (or NA) but can not remember how to do it. Help. (Is there a problem with printing the resutls?)
View 9 Replies View RelatedWhen I debug this script I get: division by zero!
In 3 lines over the debugging line, the script are writing out the variable "prosentref" as 1. Whatt can be wrong?
(the value in cell T8769 is 1)
Option Explicit
Sub optimaliseringDrift()
Dim Pgm1 As Integer
Dim Pgm2 As Integer
Dim PL As Integer
Dim lamda_gm1 As Double
Dim lamda_gm2 As Double
Dim deriv1 As Double
Dim deriv2 As Double
Const Pgm1_max As Integer = 200
Const Pgm2_max As Integer = 300
Const Pgm1_min As Integer = 45
Const Pgm2_min As Integer = 40
Const deltaP As Integer = 5
I have a column where I am dividing 2 values in 2 different cells in 2 other columns. Obviously, wherever there is a zero in the denominator, the result is '# div/0!'. How can I get these to just be zero in these cases?
View 8 Replies View RelatedI have a macro that I found somewhere on the net to look within a folder and list all the files of a certain file extension.
The macro to do this is in the attached example and is called 'Get_File_Names_Within_Dir_ext'.
I have created a basic userform outline, 'UF1' for the user to define:
Select File Extension
Select Folder to Search
Destination Sheet
I just don't have any idea how to sync the two.
If you type 'exe' into 'TB1_File_Extension' of 'UF1' the macro should search for '*.exe' files within the specified folder.
The search folder 'RefEdit1' box should open a windows explorer box (or some such) so that the user can select the directory in which to search for the previously specified file extension.
'TB2_Destination_Sheet' is a text box for the user to type the sheet within the workbook in which to list the files found within the specified directory.
'CB1_Find_Files' should activate the macro to find any files for the specified criteria.
There is also a Button 'Find File Types' in Sheet1 of the file which should activate the userform 'UF1'.
In the attached file I am simply trying to get a percentage for a given group based on the column "annual starts". When I try and copy down the formula for the other cells it gives me an error because its using the wrong denominator (the errors show up in the excel sheet as #div/0).How can I make this consistent?
View 2 Replies View RelatedI am puzzled with the following formula (entered with CTRL+SHIFT+ENTER) and it will not work. I get a error due to division by 0
{=SUM((MONTH(A$1:A$25)={1,2,3,11,12})*B$1:B$25)/SUM(MONTH(A$1:A$25)={1,2,3,11,12})}
My intention is to simply get an average of the values in column B if its respective row in column A is month # 1,2,3,11, or 12
A
B
1
01/15/2013
100
[Code] ......
Refer to the above table. Since A1, A2, A5, and A6 are having the months I seek, I then need Excel to use the values in B1,B2,B5, and B6 to offer me the average. In this case it should be (100+200+500+600)=1400 then divided by the number of occurrences column A had months that matched my criteria=4... so the final answer i would need is 1400/4 = 350
I'm trying to get the % increase but when its goes from 0 to "xx value", I get the division by zero error.
For example:
2006 Sales: 0
2007 Sales: 10,000
It would show the correct % increase in sales.
I have a forecast for a week, all days I type in, the real order, in the end of the week I got a difference between the forecast and the real orders. the difference can be positive or negative, in any case I want that difference to be added on the next cell on next week, plus the difference between the forecast and the real order from the day before, and so on.
I'm using this formula "E4*($L$3+1+IF(K4,ABS(K3/K4-1),0))" but if the real order is less than the forecast the formula add the difference increasing in a positive way, when what I want is decrease the value if the difference shows a negative tendency.
hope this make sense.
I attached a file with what I intend to do.
I work in a finance dept and we often need to divide our numbers into 12 months but I need to only work with whole numbers, not decimals. Is there a way for example of forcing either in a formula or with VBA code to get 10,000 to divide equally by 12 months? At the moment it comes out at 833.333333. What I want is something that randomly assigns 833 to any one of the 11 months of the year and for the other remaining month put in the difference which is 837 to get the total to add up to 10,000exactly.
View 9 Replies View RelatedI have this formula. I'd liked the entire referenced amount(s) to be divided by 2. Nothing I tried worked.
= SUMIF( 'X'!$B$1:$B$3000;'Z'!$B34;'X'!$F$1:$F$3000)/7,6+(SUMIF('X'!$B$1:$B$3000;'Z'!$B34;'X'!$G$1:$G$3000)/7,6)+SUMIF('Y'!$B$1:$B$3000;'Z'!$B34;'Y'!$F$1:$F$3000)/7,6+(SUMIF('Y'!$B$1:$B$3000;'Z'!$B34;'Y'!$G$1:$G$3000)/7,6)
I work in a finance dept and we often need to divide our numbers into 12 months but I need to only work with whole numbers, not decimals. Is there a way for example of forcing either in a formula or with VBA code to get 10,000 to divide equally by 12 months? At the moment it comes out at 833.333333. What I want is something that randomly assigns 833 to any one of the 11 months of the year and for the other remaining month put in the difference which is 837 to get the total to add up to 10,000exactly.
View 9 Replies View RelatedI want to use offset in a macro based on a callvalue. for example I have
200 in 'A1'
25 in 'B1'
current selected cell is 'C1'. I want to go 8 (200/25) cells down. something like offset(A1/B1,0).
I have attached a sample file where I want to concatenate data in colums A2:H2 into one column with line breaks. But CHAR(10) function alonwith CONCATENATE is not working. I have formatted the destination cell for wrap text.
View 9 Replies View Related
