i have a spread sheet that has data based off other data based off other data in a continuous cycle.. i know how to hide the #DIV/0 using VBA but what i want to do is change it to another name..such as N/A untill the data is being entered.
so #DIV/0- N/A
This may have been answered on here but can not seem to find it. My situation is I have values in A1,A2 & A3 that are like counter reading so the value is always changing. What I am looking to do is change the cell color if one of the values is over 500 from the other two values. Say A1 is 3000, A2 is 3250 and A3 is 3500. I would like the cell for A3 to change color.
View 2 Replies View RelatedMoving between different sheets changes the procedure window, which is a good thing, but can also can be error prone and annoying to search through several windows searching for the procedure or module currently working on. Is there a setting somewhere to prevent VBE from changing the current procedure window?
View 4 Replies View RelatedI have imported data, in text format, that has just the month and year (02/09). I need to convert that to month, day and year(02/01/09). The day of the month isn't relevant, it can always be the first.
View 3 Replies View RelatedI currently have 2 columns of data. One is a column of numbers, and the other is just commas to separate these values. How do I convert this into one row of data?
for example, I currently have:
row 1 | row2
324132 |,
324929 |,
010101 |,
How do i change it to
324132, 324929, 010101,
I will be able to use notepad/any other windows program
Having trouble getting my head around this one...
i want to enter hh.mm eg. 3.30 and then use it for sums...
I have a simple spreadsheet for keeping track of gas mileage which I have been using for over two years with no problems. I have five columns A through E and right now I'm on row 72, that's where the problem started. When I enter the starting mileage in A and tab to B it changes the mileage number to a date and as my mileage number I type in changes the date also changes as I tab to B. See the sample below.
A B C D E
15357155968.491 28.14745024239
9/12/19420:00 10.44423.36269628244
The part that puzzles me is it does it in row 72, 73, 74, 75, and 76 then it goes back to working normal, and even though it puts the date in A and zeros in B it does the math right. I looked at column A and B for those rows and there isn't any formulas in them.
I Have a formula in column D =(B72-A72)/C72 and column E is just =B72-A72. Other then that the only other formulas I have is in column B row 177 it gives me total miles, in 178 it gives me total gallons, and in 179 it gives me average MPG.
I am looking for a way to change the fill color of a row to just a portion of a row based on a on cell in the value on on cell in the row. I understand it should have something to do with ifthen but not realy sure of the exact phrases.
View 7 Replies View RelatedI am running a macro to copy the formats of one sheet to the next
sheet. One of the formats that I need to copy is the tab color. I
have the macro recorders code but it is for a specific sheet
Sheets("Jun-94").Select
ActiveWorkbook.Sheets("Jun-94").Tab.ColorIndex = 53
I have modified the Run as Regular Macro code from this site to only changing the x axis, select different cells and I also only used object 1 instead of the name of the chart.
The problem is that when it is run which ever value is changed last, minimum or maximum, it will turn on auto and mess it all up.
I have the below formula....
View 6 Replies View RelatedWhen opening 1 of my files (made to be used by others) a lot of .tmp and .emf files are made. When I close the file most, but not all, are deleted and new ones are added. Simply deleting all .tmp/.emf files doesn't work because some are still in use (by other files).
There for I have made a bit of code that checks if direcory "C:...Temp|Temp_PandL" exists. If not, then it will be created:
I was working on a workbook that unhides certain sheets depending on who's logged in. It seems to work fine. However, after it's run (and sheet2 has been unhidden), when I go to unprotect the workbook, the password ("test") no longer works.
View 5 Replies View RelatedI added a text box to my sheet with this
View 5 Replies View RelatedVBA Changing file name
I'm currently using this code found on Mr Excel
=IF(AE6="","",LOOKUP(AE6,{0,0.8,0.9},{"Failed SLA","Borderline SLA","Passed SLA"}))
The above formulae I have works but this changes this to failed when a 0 is shown in the field.
This 0 is shown in the field this is due to the item having no data how can I cahnge the formulae so the cell stays blank
I found the following function in an old thread.
Public Function cleanString(fNameStr As String)Dim i As IntegerConst BadChars = "=+/'*?).,#%~!($[]÷™©" For i = 1 To Len(BadChars) fNameStr = Application.WorksheetFunction.Substitute(fNameStr, _ Mid(BadChars, i, 1), "") Next i cleanString = fNameStr
End Function
How would this be modified to work as a macro on selected cells (with selection?) I lost the formatting
I have a workbook with multiple sheets. Each sheet has a title (just words in a cell). Is there VBA formula that will have the sheet take the name of the title and also chnage when the title name changes?
View 9 Replies View RelatedI'm using the bellow code which I think Igot from this site, to change the excell icon to my own, Only thing is that in the top left hand side of the workbook still says: "Microsoft Excel - My File Name". Does any of you fine people know how to get rid of the Microsoft Excel and show only "My File Name"?
Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Integer, ByVal lParam As Long) As Long
Declare Function ExtractIcon Lib "shell32.dll" Alias "ExtractIconA" (ByVal hInst As Long, ByVal lpszExeFileName As String, ByVal nIconIndex As Long) As Long
Const WM_SETICON = &H80
Sub setExcelIcon()
Dim lngXLHwnd As Long, lngIcon As Long, strIconPath As String
strIconPath = "My Path:MyIcon.ico"
lngXLHwnd = FindWindow("XLMAIN", Application.Caption)
lngIcon = ExtractIcon(0, strIconPath, 0)
SendMessage lngXLHwnd, WM_SETICON, False, lngIcon
End Sub
I have a string that may consist of one, two or three digits. If it has one digit, I want to append two zeroes to the left of it. If it has two digits, I want to append one zero to the left of it. The string is titled "Result". In this case, the original string was "64" and I wanted to change it to "064".
I tried this, to no avail:
If Len(Result) = 2 Then
zero = 0
Result = (zero & Result)
MsgBox ("The new value for result is " & Result)
End If
-----------------------
How can I alter the value of a string depending up its length (in this case)?
If I use a new variable, the transfer of value works okay, as so:
If Len(Result) = 2 Then
zero = 0
Result1 = (zero & Result)
MsgBox ("The new value for result is " & Result1)
End If
-----------------------
In my sheet I have a cell that has the year in 4 digits plus 5 other digits for incidents in our fire dept. (ie 2008#####) what I want is to have the year automatically change to 2009 on the first day of the new year.
View 9 Replies View RelatedI have been handed 503 spreadsheets containing system data for multiple business units in my company. Each spreadsheet is identical in structure (same column headings) but just varies in the number of rows of data
I’ve now been asked to add (4) additional columns to each of the 503 spreadsheets. The (4) new columns are to be called:
“Class”
“OpCat Template ID”
“Agent”
“Last Occurrence”
And added in columns L, M, N, and O.
Is there a way to do this automatically instead of having to manually enter this data 503x?
I am entering a list of usernames in a spreadsheet and some are in different cases.
When I am trying to enter 'FDante' for example, excel keeps changing it to 'Fdante'
I have tried formatting the cell and was even going to try vbcode to resolve this but I assume there must be a simple option that I have overlooked.
what needs to be changed on this code to only apply once for the row and not over and over again.
Dim Cell As Range
If Application.Intersect(Target, Range("ActivityRange")) Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each Cell In Range("ActivityRange")
If Len(Cell.Value) > 0 Then Cell.Value = Cell.Value & Chr(10)
Next Cell
Application.EnableEvents = True
I was thinking of placing my total line above my data instead of at the bottom. How would the formula look to sum each column?
For instance, = sum(A6: to what?) How do you specify what the last row is without having to do something like = sum(a6:a65536)?
I looked in all the copy threads and did not find a solution that applied. I feel I should know the answer; but, it is late (or early). I am working on a spreadsheet where formulas will start out in only on one row. The user will paste information in column A and invoke the macro which, among other things will copy the formulas in columns B through E down to the rows equal to the rows in column A. The number of rows will vary each time the user pastes their information in column A. I started out recording the macro - that may be my problem - and even though I "end down" - the row is hard coded. This time there are 16 rows next time there may be 116.
In my spreadsheet I do have a cell (G1) where I counta column A so I know the ending row but have not figured out how I can incorporate that as a value in my code.
How do I automatically update the value of the row?
Range("B3:E3").Select
Selection.Copy
Range("A3:B3").Select
Range("B3").Activate
Range(Selection, Selection.End(xlDown)).Select
Range("B3:B16").Select
ActiveSheet.Paste
Application.CutCopyMode = False
I Am trying to create a web query that will query all data from Yahoo business news the problem I am having is that they keep changing their URLs. That is they constantly change by date and title e.g. today wa have:
http://news.yahoo.com/s/ap/20070108/...re/wall_street
http://news.yahoo.com/s/ap/20070108/...sprint_outlook
http://news.yahoo.com/s/ap/20070108/..._ge/home_depot
Is there a way to create a web query that can query the news every day and automatically adjust for URL changes?
i was wondering how i can get excel to hide everything except for the worksheet, as soon as it starts and then put it all back when you quit.
View 2 Replies View RelatedSub Macro1()
Dim blue1 As String, blue2 As String, blue3 As String, num As Integer
blue1 = "one"
blue2 = "two"
blue3 = "three"
For num = 1 To 3
ActiveCell.Value = blue & num
ActiveCell. Offset(0, 1).Select
Next num
End Sub
What I am trying to achieve is to use the appropriate "blue#" variable according to the num variable. For example, when num = 1, I want the macro to use the variable blue1. I am not sure how I should code this.
Trying to increment worksheet in order to summarize data from all other worksheets. I have 70+ worksheets and I'm trying to copy and paste worksheet and cell reference so that the same data on each worksheet is summarized on one sheet. Worksheets are labeled Cost (1), Cost (2), Cost (3) etc. When I copy the cell reference I can't get the worksheet name to change to the next worksheet:
=+'Cost (1)'!$E$19
=+'Cost (1)'!$E$19
=+'Cost (1)'!$E$19
I want to get this
=+'Cost (1)'!$E$19
=+'Cost (2)'!$E$19
=+'Cost (3)'!$E$19