Statement Effecting Wrong Ranges
Oct 4, 2007
clearcontents is clearing some cells outside of the specified range. Some of the cells in row 10, which is above the beginning row of specified range, are also getting cleared. I've tried several variations to no avail.
Here is the code I am using:
Sub ClearDataSheets()
Application.DisplayAlerts = False
Application. ScreenUpdating = False
Dim d1 As Date, d2 As Date
d1 = Date
d2 = Worksheets("FPY").Range("A375").Value
If d1 <= d2 Then
MsgBox ("Resetting the program prior to start of new year is not permitted."), vbExclamation
Exit Sub
Else
View 9 Replies
ADVERTISEMENT
Aug 22, 2007
I have a FOR loop which is supposed to loop through all the worksheets in my workbook, create a named range, then add some data to the right of the range. However, when I run my code, all the named ranges refer to the last worksheet in my workbook. The loop only seems to be partly working, and I just can't work out why.
Sub maxLifData()
' for each worksheet in the workbook, do dmax formulae and add results to new sheet. bhole id on left, depth across top.
Dim ws As Worksheet
Dim wsName() As String
Dim i As Integer
Dim mCount As Integer
Dim nr1 As Range
Dim nr2 As Range
Dim head1 As String
Dim head2 As String
Dim wsRangeName() As String
Dim maxF As String
Dim fRange As Range
Dim rng As Range
Dim mRng As Range
head1 = "STCN_DPTH"
Set fRange = Range("G3")
Set mRng = Range("A2")
Redim wsName(Worksheets.Count)
Redim wsRangeName(Worksheets.Count).............
View 4 Replies
View Related
May 2, 2013
I have a excel based timesheet that looks at an external table named "Station Master" to populate columns from a vlookup. I have recently linked the Station Master to a database to populate it. From the database we may change status so that the project does not show up in the station master anymore to make sure complete projects are not getting hours charged anymore. Now if a project that was on the master goes away previous timesheets lose the correct description because it is not on the station master anymore.
My question is there anyway to make the previous data lock so the data that is in the cells won't change when the station master changes.
A little more info... the time sheets are by quarter so 12 weeks are in the same workbook as individual worksheets. All of those worksheets vlookup to a worksheet called "projects" inside the workbook. Then that worksheet references the external Station Master workbook.
View 2 Replies
View Related
Feb 14, 2007
Im sure this is straight forward and I've trid a few times, but I'm not quite getting what I'm after:
I have 3 differnet cells containing the following values 36 (B1), 72 (B2) and 108 (B3)
I want to look at a cell, lets say A1 and if the number is less than or equal to 36, then output, lets say X1
if it is greater than 36, but less than or equal to 72, then outputs X2
and if it is greater than 72 and less than or equal to 108 then outputs X3
I tried this and a number of combinations
=IF(AND(A1>B2,A1
View 9 Replies
View Related
Aug 27, 2007
I need to be able to give a grade A, B, C or a D by using a range of scores in an IF function for a markbook as part of my IT coursework.
I have had several failed attempts, and this is one of my many formulas that has failed:
=IF(O6<45,"D",IF(O6=$46:$60,"C",IF(O6<$61:$74,"B",IF(O6>75,"A"))))
View 9 Replies
View Related
Jun 11, 2007
how can i compare a range, which may contain multiple cells of the same information ie 1s and 0s with a separate cell, if they match, return a message box?
View 9 Replies
View Related
Apr 26, 2014
How would i wrte this formula correctly?
=IF(SUM(I5:J26))-(SUM(D5:E26))>=0,0,(SUM(I5:J26))-(SUM(D5:E26)
It needs to say if the sum of the two cell ranges is less than or equal to zero then display as zero, otherwise display the answer
View 3 Replies
View Related
Feb 22, 2009
I have created a spreadsheet which creates an average of feedback for trainers in a training company. The form adds up the feedback score into column L of the summary sheet and I have created a summary sheet which I want you use to calculate the average for each trainer.
I have cobbled together an array formula which creates the overallaverage for each trainer based on the named ranges entered via the form.
It looks something like this:
View 10 Replies
View Related
Mar 5, 2009
I generated my urls to online photos, I referenced cells where some are only numerical (ex. 479) while others contain a numerical/text mix (ex. 3014-RACK). Here is my url code in excel...
View 10 Replies
View Related
Jul 28, 2009
I am currently using an Intersect statement in a worksheet module to perform two things:
1. Insert a time stamp into row 2 when row 1 has a price inserted
2.To clear that time stamp if the price is deleted at some later date.
My problem is with the time stamp value being deleted by the user.
If I try to clear the price (now that the time cell =empty) I get a Runtime error 91 - Object Variable or With block variable not set.
I would like to convert this code to a select case statement but I'm not sure how to do this in this situation. Would error coding be appropriate in this instance?
View 5 Replies
View Related
Feb 17, 2009
I am using a For Each Next to cycle through a list of names, check certain conditions and then will be adding a further lookup (instead of Result = Result+1), but all I get is #Value!
Function maxbarometer(Name As String, Round As Integer, NameList As Variant, RoundRange As Range, RoundRangeTwo As Range)
Dim Roundname As String
Dim Result As Double
Dim NameColRound As Integer
Dim ListObject As Variant
Roundname = "Round " & Round
NameColRound = Application.WorksheetFunction.Match(Name, RoundRange, 0)
For Each ListObject In NameList
If ListObject.Value = Name Then
Result = Result
ElseIf Application.WorksheetFunction.VLookup(ListObject.Value, RoundRange2, NameColRound, False) = Roundname Then
Result = Result + 1
End If
Next
maxbarometer = Result
End Function
View 9 Replies
View Related
Mar 19, 2007
when i try to get the final row witht the following:
KROGrow = Cells(65536, 1).End(xlUp).Row
i am getting a row that is consistently 27 rows off of when i manually do control up? i am pasting data from and existing workbook into a newly created workbook in the sub. i would assume that since the destination is new that they would never be any data in this?
View 9 Replies
View Related
Oct 25, 2007
I need help creating a macro that will search through my excel spreadsheet and for every instance where column A isn't empty it should cut a range of columns from that row and paste them in a different range of columns in the row before it. It should then delete the row that it cut the columns from and keep searching until it has done this for the whole worksheet. I can modify which range of columns are needed, but it has been so long since I've worked with excel macros that I haven't been able to do it.
View 5 Replies
View Related
Jul 3, 2013
I am trying to use the Vlookup function to return State name based on the corresponding state code.
I have the list of State along with the Codes in adjacent sheet, arranged in ascending order. I am using the Vlookup function to list the State name based on the State code.
Vlookup function: Vlookup(A4,Sheet2.A2:B51,1)
This function returns me a wrong value. It gives "Arkansas" for the state code "AZ", when it should be Arkansas.
Also, if I add "FALSE" as a 4th argument, it returns "#N/A".
View 9 Replies
View Related
Jan 29, 2014
I have the following formula: IF(SELL_INVESTMENT = "YES" , 1,2) It returns not 1 or 2 but 0. The worksheet contains over 1000 cells. Trace Precedents verifies that the formula evaluates the correct cell and that it contains "yes". I created a very simple worksheet to test the formula and here it correctly returns 1.
View 2 Replies
View Related
Apr 18, 2014
When copying data from a website to excel, excel recognizes the dates wrongly.
About half of the dates he sees as: DD/MM/YYYY, while it should be: nothing/MM/DD.
For example: The cell contains the date: 01/04/2029 (The first of April, 2029)
While it should actually be: 04/29 (The 29th of April, no year!)
For all dates it starts with the 1, followed by a month, followed by a year (which consists of the year 2000 + the actual day), which is totally wrong.
About the other half of the cells, it just contains text with the month and the day (which are correct), but excel doesn't recognize this as a day.
For example: The cell contains the text: June 12
When I set the cell format to date (or any other format for that matter), nothing happens, so excel does not see this as a date.
View 1 Replies
View Related
Nov 7, 2008
I'm trying to figure out an Internal Rate of Return for a spreadsheet. The answer is supposed to be: 29.42% however I'm getting 25.94%. Does anyone know what I'm doing wrong in my IRR function on the Profitability worksheet? I'm currently using Excel 2007 by the way.
View 4 Replies
View Related
Feb 18, 2010
I have a problem with my references when sorting in excel. I have been searching for the problem, but did not found a useful solution.
I'm using excel 2003.
Let me try to explain my problem with a simple exampel.
I have a column with numbers and one with formulas pointing at these.
A B
2 =A1
4 =A2
3 =A3
Now I want to sort column A, and the column B should keep pointing at the original number. So I want this:
A B
2 =A1
3 =A3
4 =A2
But I get this:
A B
2 =A1
3 =A2
4 =A3
This output is what I would expect if I used absolut reference($).
Using offset(B1;0;-1) will work, but I cannot use this, since I will add/delete rows and the reference is not neccessarily next to it. Plus it can be a rather large sheet.
I cannot use dynamically name definition either, because it is intended for other users, and this will be too much work for them. What I basically need is a simple formula they can enter.
My sorting is done via a VBA macro, so if there is anything I can do through macro/VBA that will be okay as well, since I can do this without the user seeing it.
View 14 Replies
View Related
Oct 22, 2013
I have modified this micro I found in a forum. All I changed was the Range on Sheet 1.
[Code].....
It works fine except when it cuts to sheet 2 it starts at on column B I need it to start with Column C. What do I need to add ?
Attached File : Survey.xlsm
View 7 Replies
View Related
Sep 25, 2006
I have the following code which saves my worksheet with a name extracted from particular cells:
Public Sub SaveAsMaximoWO()
ThisFile = Range("AC5").Value
ThisFile2 = Range("E3").Value
saveName = ThisFile & " - " & ThisFile2
ActiveWorkbook.SaveAs Filename:=saveName
End Sub
This (for reasons I don't understand) saves the file to My Documents by default.
What I would like it to do is save to another folder within my documents as default.
View 14 Replies
View Related
Mar 13, 2009
how to control this,it should be 0.55. is they a wat too round off too next highest hole number? Here is a example.
View 2 Replies
View Related
Apr 18, 2009
I have this workbook with 10 sheets. Each sheet has macros that are called from a floating toolbar. What I am trying to do:
I want to create a msgbox that warns me when I click in a toolbar's button to call a macro that not runs in the activesheet. Something like: "This code (or macro) doesn't match (or run) with the active sheet.
View 3 Replies
View Related
May 4, 2006
as such Excel (2003) is putting in the wrong weeknumbers in my spreadsheet, for example 02/01/2006 is listed as week 0 when in fact it is week 1, how can I get it too correct this problem?
I am using the following formula, could someone explain this formula in
english?
=CONCATENATE(YEAR(BM2),"_","w",IF((WEEKNUM(BM2)-1)<10,CONCATENATE("0",(WEEKNUM(BM2)-1)),(WEEKNUM(BM2)-1)))
View 14 Replies
View Related
Sep 16, 2007
Thers no easy way to explain this other than looking at the sheet (which is attached), but basically i have a load time start time and end time.
In the example in the sheet the load time is 01:00
The start time is 23:00
The end time is 23:35
Th eload took 35 mins and was not late as it is going 01:00 next morning, yet the answer it replies is 'N' which makes it show as late.
Does anyone have a formula to slove this issue
View 10 Replies
View Related
Dec 22, 2011
The code is supposed to project review dates 1 month, 2 months and 3 months after the start date, but the first "cell offset" line on is projecting the next day instead of next month, its driving me nuts!!, the others are working fine
Code:
Sub reviews()
Dim cell As Range
For Each cell In Range("L1:L200")
If IsDate(cell) Then
cell.Offset(, 10).Value = DateAdd("m", 1, cell.Value)
cell.Offset(, 11).Value = DateAdd("m", 2, cell.Value)
cell.Offset(, 12).Value = DateAdd("m", 3, cell.Value)
cell.Resize(, 10).AutoFill cell.Resize(, 11)
End If
Next cell
End Sub
View 3 Replies
View Related
Dec 28, 2006
I was trying to explain modulus to someone and they wanted to know why you can "flip" symbols mod(-6,7) = 1 in Excel. So I got to explaining that -6 Mod 7 is the same as -6-(|-6/7|)*7 which is how you get 1.
And that's when I realized... |-6/7| = 0 not -1. Then I looked in VBA and sure enough -6 mod 7 = -6. Apparently the problem boils down to the Integer conversion. Excel is performing the integer coversion by rounding down (INT) wheras VBA appears to be using CINT.
So here is how it work out in excel:
-6-(|-6/7|)*7
-6-(|-0.857142857|)*7
-6-(-1)*7
-6--7
-6+7=1
But in VBA you get
-6-(|-6/7|)*7
-6-(|-0.857142857|)*7
-6-(0)*7
-6-0=-6
View 9 Replies
View Related
Mar 27, 2007
I have a very basic spreadsheet to calculate golfer handicaps based upon a course index. For some reason, my "LOOKUP" formula is not retrieving the data from the cell (column) next to the value I am searching...?
View 9 Replies
View Related
Jul 29, 2009
I'm trying to parse a text file into Excel and the date is coming out as "1940" instead of "2040". In the text file the date reads "5/14/40". I've already changed my regional setting in Excel so that if I type in "5/14/40" it is understood to be "5/14/2040". For some reason this doesn't work when I parse the data though.
View 9 Replies
View Related
Feb 27, 2010
The formula below was written a couple of years ago and I just discovered an error. It returns an "S" for the value 0789 when it should be a "C", (Consecutive).
IF(OR(AND(ISNUMBER(MATCH({0,1,2,9},$O215:$R215,0))),AND(ISNUMBER(MATCH(MIN($O215:$R215)+{0,1,2,3},$O215:$R215,0)))),"C",IF(SUM(IF(FREQUENCY($O215:$R215,$O215:$R215)=3,2,1))=2,"DD",INDEX({"S","D","T","Q"},MAX(FREQUENCY($O215
******** ******************** ************************************************************************>Microsoft Excel - FL MID PLAY 4 CONSECUTIVES.xlsm___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutT215=OPQRST215078907893SSheet2 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
View 9 Replies
View Related
Jun 30, 2006
The macro was orininally set up for the 7 days of the week. Now I want to set it up for the 31 days of a month. It's set to pull information from a register sales journal. Here is the button macro-
Sub Button2_Click()
Dim sDay As String
Dim sShift As String
sDay = InputBox("Which Day? (1 For MONDAY, 2 For TUESDAY, ... 7 For SUNDAY)")
Select Case sDay
Case "1"
sDay = "1"
Case "2"
sDay = "2"
Case "3"
sDay = "3"
Case "4" ........................
View 10 Replies
View Related
