Macro Worked For 2 Years, Suddenly Started Giving Strange Results
Jan 20, 2009
I have a VBA macro that I pieced together* a couple of years ago to help with a pretty simple task. We have two excel sheets that pull in information daily from different sources. The macro copies and paste values for the worksheet, then 1) saves a copy of the values only worksheet with the current date, 2) resaves the values only worksheet as a .csv file that is then manually uploaded into our database.
View 3 Replies
ADVERTISEMENT
Jan 24, 2007
I have a problem with isnumber giving me two different results. One time it will evaluate the following as true:
ISNUMBER(0)
and in the same sheet it will evaluate the same as false.
I need ISNUMBER to NOT recognise "0" as a number all the time.
View 9 Replies
View Related
Feb 25, 2014
Cell A3 on my SHEET1 has the following formula =DATE(YEAR(C1),MONTH(C1) + 1,DAY(C1). It goes on like this for the next 12 months (+2, +3, +4....).
Column B on my SHEET2 has One of the five product codes (A, B, C, D, E) and Column D on my SHEET2 has manually entered dates
I created the following condition for each product code under each month in SHEET1
=IF(MONTH(Sheet2!$D$1:$D$39)=MONTH(Sheet1!$A$3),COUNTIF(SHEET2!$B$2:$B$39,"=A"),"")
The formula only works for two product codes (A and B), and then yields blank cells.
View 5 Replies
View Related
Jan 8, 2009
I am making a company wide estimate sheet and having trouble with the drop down box. I have a drop down to pick the branch, but when I name the source it only says branch, even though I named the data on the branch sheet. I would like to be able to pick the branch initials and have it populate the phone and fax number under the main heading but am getting a error there.
Also for the labor is it possible to have it insert the correct labor into the formulas at the labor lines according to which branch is picked from the drop down.
View 4 Replies
View Related
Jun 5, 2012
So I am using a macro to do a particular task. Part of the macro's process is to delete some unwanted rows.
The code below was for my original macro which works absolutely fine:
Note: The ranges are fixed (i.e. column "AT" is reffered)
Code:
Sheets(1).Select
Dim Current As Worksheet
Dim Flt As String
Flt = Me.TextBox2.Value
For Each Current In Worksheets
With Current
With .Columns("AT")
.AutoFilter field:=1, Criteria1:="
View 3 Replies
View Related
Sep 16, 2009
So I have a macro which is running slowly, though I can't figure out why. I don't remember adding/subtracting from it. All code prior to this macro being called runs fine...So this part below runs fine:
Private Sub Step_3_Click()
Dim YR As String
YR = Range("C24")
Dim DT As String
DT = Range("C27")
Dim MNTH As String
MNTH = Range("D23")
For Each wb In Workbooks
If wb.Name = "Top100gen2start.xls" Then
MsgBox "You need to close the Top100gen2start.xls file before
proceeding.", , "Close Top100gen2start.xls file"
Exit Sub
End If
Next
If CheckBox1.Object = False And CheckBox2.Object = False And
CheckBox3.Object = False And CheckBox4.Object = False And
CheckBox5.Object = False Then
MsgBox "You didn't select a sector report to run!", , "Select a
sector report"
Exit Sub
End If
If CheckBox1.Object = True Then
Call FullBlown
If gameover = 1 Then
Exit Sub
End If
End If
Once I call that Fullblown macro (or any of macro similar to it in this process), the whole things slows WAY down (I mean as soon as it is called)...Not sure what I'm doing wrong, but here's the called macro:
Public Top100Check As Integer, gameover As Integer
Sub FullBlown()
Application.ScreenUpdating = False
' Dim x As Date
' x = Format(Date, "mm-dd-yy")
On Error GoTo Jump
' Workbooks("PERSONAL.XLS").Close
Jump:
Dim YR As String
YR = Range("C24")
Dim DT As String
DT = Range("C27")
View 9 Replies
View Related
Aug 28, 2007
I have a date 07/28/2027 and need Excel to calculate a date 65 years in the future taking into account leap years.
View 9 Replies
View Related
May 22, 2009
I am not sure that I can do this, but here is what I would like to do. I have a worksheet that I initial when I start a job in on cell and then when I finish in another cell. What I's like is to have a macro running in the back ground that will tell me the total elapsed time from when I started to when I finished.
View 5 Replies
View Related
Aug 15, 2014
how to give attractive colours to my ordinary macro buttons to which we assign macros
View 2 Replies
View Related
Mar 10, 2014
The below code is giving wrong error.
[Code] ........
sheet1 has the following data
sheet1 has the following data
empid name loc
1 aaaa ddd
2 bbbb ggg
4 cccc dddd
the 3rd row is empty in sheet1 but still it is showing number of rows as 3.I need macro it will not count if entire row is empty in that sheet.
View 14 Replies
View Related
Jul 8, 2006
The following code is giving me an object-defined or application-defined error:
wsDst. Range("E" & NextRow) = "=VLOOKUP($B" & NextRow & ",PriceList!$A:$IZ,MATCH(HLOOKUP($I" & NextRow & ",PriceList!$C$3:$IZ$3,1,1,PriceList!$A$3:$IZ$3),FALSE)"
View 6 Replies
View Related
Feb 1, 2007
I need to develop a work sheet for agency booked people to count the number of hours worked by them on daily basis. Agency can clock in at three different times and clock out at 6 different times. i tried but could not even develop logic to calculate the total earned hours. i attached the sheet for reference.
View 5 Replies
View Related
Feb 21, 2013
Is there a way to conver a persons time spent (given in weeks) to adjust/convert to show per month. Attached is the sheet. Do note that week 2/25 - 3/1 is a combination of Jan and Feb so hours should be logically divided into jan and feb...
Name 2/18 - 2/22 2/25 - 3/1 3/4 - 3/8 3/11 - 3/15 Feb mar
Tom 40 10 0 20 ?? ??
name
2/18-2/22
2/25 - 3/1
3/4 - 3/8
3/11-3/15
Feb
Mar
tom
40
10
0
20
??
??
View 3 Replies
View Related
Jun 19, 2009
My userform has a group of controls, four navigation buttons and a textbox. The nav. buttons have accelerator keys, Alt+N for next record and so on. I was moving the group about on a userform, when hey presto! it suddenly disappeared. When I run the form, the navigation buttons do not appear, but I can still navigate using Alt+N, Alt+P. Here's what I've done so far, trying to find out what's going on.
* I selected all controls with Ctrl+A, but there was no sign of any object in the place where the missing group should have been.
* I looked on the Object Browser (which is something I don't understand too well yet) and the navigation buttons are there.
* I hid all the controls, and showed them again, didn't help.
* The group I was moving was near the bottom of the form. I set the zoom property of the form to 50, so all controls in design view were bunched up near the top left corner; the missing group didn't appear
View 2 Replies
View Related
Jan 16, 2012
why my Macros have suddenly stopped working for no apparent reason?
For Example - one macro would run when a Cell in Column A was selcted. It would trigger a macro to save the workbook.
I'm not giving details of what the specific macros do as it's more of a question as to why macros in a workbook just suddenly cease to function.
View 4 Replies
View Related
Mar 4, 2014
I have an the excel book with sheets that are password protected so that the users cannot delete rows or columns.
In each sheet I gave permissions for certain ranges that need to be filled out only to specific users that need to fill out those cells.
The users are located and managed in the Active Directory.
The point is that each user can only update his/her sheet within the Excel book.
It was working for a day or two and then all of a sudden without any changes all the permissions were gone and every user was able to edit every sheet.
View 1 Replies
View Related
Apr 2, 2014
I jst need to console data from different workbooks to one worksheet. There are 30 workbooks & each workbook contains one worksheet name as Data. The work i am currently doing mentioned below step wise
1) Open workbook
2) Apply filter on whole data
3) Select dates from "J" Column (E.g 02/10/2014 to 02/20/2014)
4) Then Copy whole filtered data
5)Paste into New workbook
6) Open another worbook
7) repeate Step # 2,3
8)Then paste whole data in below the first data New Workbook (In which we have paste the first data below that i will paste this data)
These steps i have to do manually it takes almost 1hr daily After pasting all 30 workbooks data in one workbook i have to make pivot of it & sent it to my Manager. What i need
I jst want a excel macro that will ask me date range from which i have to pull data from all 30 workbooks.
Once i gave date range will jst click on button say extract or pull. It willl console whole 30 workbooks data from the given date range in a one worksheet.
View 13 Replies
View Related
Jul 6, 2014
I need to create a macro in excel that will take data for a particular year in vertical rows and transpose it to horizontal rows.
What the data originally looks like:
380204198401014.72
380204198402016.34
380204198403015.66
380204198404013.71
380204198405013.86
380204198406013.51
[Code] ........
What I want the transposed data to look like:
StationYearJanFebMarAprMayJunJulAugSepOctNovDec
38020419844.726.345.663.713.863.5113.69 3.611.362.812.522.25
38020419853.194.211.021.292.372.416.082.500.143.196.122.04
I have never written a macro before and have tons of records (over 40,000) so using this will save me a lot of time.
View 5 Replies
View Related
Jan 1, 2009
I am trying to copy some data from on sheet(sales) to another sheet(totals) and later will try to copy to all the worksheets in my workbook
I am using the following Sub macro2()
Sheets("sales").Select
Range("c16:c17").Select
Application: CutCopyMode = False
Selection.Copy
Sheets("totals").Select
Range("b2:b3").Select
Active Sheet.Paste
End Sub
the data in cells C16 and c17 is 2000 and 1850. it shows an error: sub or function not defined
View 9 Replies
View Related
Mar 26, 2014
So i got a few macros on a worksheet and one of them is simply :
Sub Pr_Removefilter()
ActiveSheet.ShowAllData
End Sub
Ive worked on the excel a couple of times, everything fine. Then it started crashing upon saving (after changes made that had no possible connection to the macro, on another sheet). After a few painfull trials and errors i located the problem to be this macro button. I erased it, created a new one with the EXACT macro and it works. Why?..
View 1 Replies
View Related
Dec 5, 2008
I'm trying to get a loop started to perform tasks and then go to the next line if it has info in it and do the same thing, until it gets to a blank line, and can't seem to get it right. It's been a while since i've written macros, so i'm sure i'm doing something completely wrong.
View 4 Replies
View Related
Jan 8, 2009
Every time I open my excel spreadsheets the "Getting Started" window opens automatically. How do I turn it off so it does not open automatically
View 3 Replies
View Related
Feb 27, 2009
I dont know the real name about this scroll bar (like you can see in my attachment), so I cant search in the forum, sigh! how can I remove the bar in the red circle?
View 3 Replies
View Related
Oct 8, 2009
INDEX(Sheet1!$F$2:$F$10,0,1) returns #VALUE!
INDEX(Sheet1!$F$2:$F$100,0,1) returns 110 (e.g.)
As Both index have zero(0) for its row reference (row_num) I would expect both formulas to return #VALUE! (or even #REF!)
View 2 Replies
View Related
Jul 31, 2014
I've a set of tasks in Column A which I would like to measure. At times, Users flip from task A to task B then back to A and so on. I would like to be able to pause a timer on task A if when timer on task B is started and same as well with the other tasks.. When Task A is started again, it should continue the timer if not add the on-going time to the presently running time under that task.
View 2 Replies
View Related
Oct 20, 2008
I start my excel program from a "Shortcut" in "Folder 1".
That shortcut points an Excel program in "Folder 2".
When Excel is started,
Parent.Path returns "Folder 2",
but I really need to know what "Folder 1" was.
It seems that should be available as
when I do "Save File As"
it is pointing to "Folder 1".
Is there a way to find what "Folder 1" really was from the VBA enviornment.
View 9 Replies
View Related
Nov 6, 2008
I have a workbook with about 52 worksheets, I have a script that will unlock the protection from all the worksheets. However the problem I'm having is the script runs and starts and ends at sheet1 I want the script to start and end at the sheet I run the script from lets say sheet 40?
View 4 Replies
View Related
Jun 16, 2006
when mixing data types: a real and a complex as in
=IMSUM(-G1/2,IMSQRT(H1))
if G1 and H1 are both exactly equal to 4, the answer whould be 0
see [url]
and look at location I1 (highlighted).
View 9 Replies
View Related
Sep 16, 2009
there is a strange character that appears in an excel spreadsheet. It looks like a square with a question mark in it.
what this means and am I able to remove them
View 9 Replies
View Related
May 28, 2006
I made a userform that spits out 'logs' in a more readable easy to understand. It basically shoves everything into a nicely organized list box.
I have a load button that if pressed accepts .csv's
It works perferctly for practically every type of .csv that contains these logs however 1/100 times the logs contain korean or chinese characters which cause the program to crash.
I gone through some debugging and I found out that the characters that cause it to crash happen to equal the EOF character. So not only does it spit an error on that line, it stops loading anything after that point.
Here is my CheckFileSize function that runs first to tell me how long I should make my list.
Private Function CheckFileSize(ByVal TempFileName) As Double
Dim TempData As String
Dim SizeCount As Double
how to do this besides loading it into a worksheet first?
View 9 Replies
View Related
