Private Sub Zero()
If Range("G8").Value = "" Then
Range("G8").Value = "0"
End If
End Sub
I have this listed in as code for VBA. If I hit the "Run Sub/User Form" button on that screen, with the field G8 having nothing in it (having hit "Delete" just prior), it resets the field to "0".
But when I hit delete on the worksheet, the field just sits there......no zero, no nothing.
The formula I'm using is
Code:
=SUBSTITUTE(OFFSET(A10,-2,0),VLOOKUP(OFFSET(A10,-1,0),prim!$D$1:$I$263,5),VLOOKUP(OFFSET(A10,-1,0),prim!$D$1:$I$263,6))
But it doesn't work at all on one worksheet, half works on another, and works sometimes on another.
In the worksheet attached called workbook 4, it works a15 sheet 2, but not a10 sheet 2. But that was not always the case. In the worksheet attached justification copy, it works in a14 sheet 4 but not in a10 but for a good while it didn't work in both. In my own private doc it doesn't work in both cases.
[URL]
I need to create conditional formatting on a worksheet where I need to change the cell colour based on 6 cases.
The VBA that I have found does not work with referenced cells to another worksheet in the workbook which I need as all the data is referenced with calculations.
I also need to specify the cells that the VBA will apply to as I need to apply 6 or 7 different different sets of conditional formatting on the same worksheet to different groups of cells.
I am running into an odd issue and hope someone might be able to shed some light.
I have created several lists on one sheet in a workbook and have assigned named ranges to each individual list. So far, so good.
On various other worksheets, I have created Data Validation rules to allow users to select the relevant data from drop-down lists. (using formulas such as =List1, =List2, etc. in the Source box to capture the named ranges I had created).
Everything works perfectly...EXCEPT for one worksheet, which continues to give me the error message, "You may not use references to other worksheets or workbooks for Data Validation criteria." Can anyone explain why this error would only occur on one worksheet and work fine for all the other sheets? I'm perplexed!
I have searched the FAQ's but have not found a suitable answer to my problem. I have some code that works perfectly when it is run from the VB Editor but when I put it behind a command button it gives me an error almost straight away. I have read that when a command button is used the command button defaults the active sheet to the one that it is one therefore you always have to specify the active sheet but I have done this so am still confused as to why it is falling over. Below is my code, I have commented where it is tripping:
Sub FormattingAcutalReport()
Workbooks.Open Filename:="H:Risk ReportingDaily TemplatesMF Consolidated Risk DAILY LIVE DATA FROM BO.xls"
Workbooks.Open Filename:="H:Risk ReportingDaily TemplatesDaily Non Banks LIVE.xls"
Dim myBorders() As Variant, item As Variant
Set SEGNSEG = Workbooks("Todays Reports.xls").Worksheets("Seg and Non Seg Bank Summary")............................
I want to do a vlookup between two tabs on a date in cell A1 eg 01-Apr-08
The lookup needs to be on month and year so I'm using
=Month(A1) which gives an answer 4 in cell A2
=Year(A1) which gives an answer 2008 in cell B2
I then oncatenate the results (=A2&B2) to get a unique reference 12008 - works ok
On the second tab, I've repeated the formula, I get an answer of 1 and 2008 but when I concatenate, I get a #VALUE!
I've tried
"text to columns"
unchecked Protect on the cells
worksheet is not protected
on the 1 and 2008 and concatenated result
Microsoft Excel is no longer displayed at the top of the screen, and I can't see my worksheet tabs at the bottom of the screen.
View 3 Replies View RelatedI have a odd problem. I have workbook that I made I placed this formula in cell A1
View 3 Replies View RelatedI am trying to conditionally format using AND() to reference if there is a value in the top line of a table and the title in the right most column for each cell in the table, the formula I am using works when i paste it into cells (i get the correct TRUE or FALSE for each cell in the sheet) but is not working as a format formula, I dont get an error but the shading does not happen.
The formula:
=AND(OFFSET($A$1,0,(COLUMN()-1),1,1)"",INDIRECT("a"&ROW())="Item1")
I have this formula :
=SUM(F25:M25*$F$6:$M$6)
It works great in xl2003, and it works properly in XL2007 until you try and change it. I changed the column F to Column D and now it gives me a #value.
How can I re-work this for xl2007?
I know you can see it but If set to default when the mouse moves over an object it changes to that object's cursor. Access had a screen property that may have detected this change. Excel Examples On the worksheet the cursor is a cross If on a worksheet you have a shape with a macro the pointer changes to a hand On a shape without a macro the pointer is 6
HOW do you use vba excel to find its current number or send a message when it changes or peek message to notify when it changes
I have a nifty formula brought to me by one of the excellent members on this board. It works perfectly for what I want, but I must admit I cheated, I have a problem that was similar to the problem this formula rectified and I find myself not knowing how it works.
=IF('IIC Transfer Status'!O139="x",MIN('IIC Transfer Status'!K139+10-WEEKDAY('IIC Transfer Status'!K139-{1,3})), "" )
What Im wondering is if someone could enlighten me as to what is happening in this function, so Im not as blind to assume it is magic.
Eventually I need it to be customed tailored to work in this way: I have a column of dates, I want it to look at column A and if there is a date in it ADD 4 WEEKDAYS (business days) and return the resulting date.
i.e date in column a is march 19 plus 4 business days. Result in column B march 23.
If someone can explain how this string works that would be great, or if someone could just post a suitable function, that would work for now.
The problem i have is with the sum offset function. The formula i use is to sum up the totals for each page of an accounts document. At the top of each page there is a column with " £ p " in it and i use that as the basis of the formula. This formula is entered in the bottom of every page to calculate the total for each page.
=SUM(OFFSET(F211,-(ROW(F211)-MATCH("£*",$F$1:F210,1)-1),0,ROW(F211)-MATCH("£*",$F$1:F210,1)-1,1))
This works all the time but when text is entered into column F (the column i'm adding up) it messes up with the formula. the formula will instead find the £ p but for a page 2/3 pages before the one i'm calculating on. This formula only faults when text is entered. Another thing; this excel sheet i'm working on is an output from a different computer program. it is outputted as csv file i think.
Like i said the formula works a treat except when text is entered. The obvious way around this not to enter text but that is not an ideal option. I cannot think of another way of calculating the total for each page although i'm sure another exists.
The formula works on numbers but not on time with format cell : [u]:mm:ss
HTML Code:Â
8
7
6
11
12
HTML Code:Â
8:00:00
8:00:00
6:00:00
3:00:00
[Code]....
I figured out exactly what I wanted to do and got it to work in a test excel sheet. However, when integrating it into the actual workbook I wanted, I was unable to get it to work. So, I used the same cell references I need to work in my actual workbook and pasted it all back to the test excel sheet.
So, the test excel sheet has the working formulas up at the top, and a duplicate of what I need to work in the actual cells I need them to work in. Changing the shift start time should group any persons with the same shift togethor. I'm still fairly new, but I think the only portion that could have been changed is the portion that says 1:1.. I figure that is relative to the array so it shouldn't be changed, but not sure what else to do.
Here's an array formula that works:
[Code]....
Here's the array formula to be in the correct cells that doesn't work:
[Code] ....
How to to get it to work in the different cell area.
Attached File : Shift.xlsx‎
I have a spreadsheet with a VLOOKUP formula that I have to send to a customer weekly. They are required to type a code in column E of what they invoice, and it pulls the rate into column G based on a rate table I have. The formula is: VLOOKUP(E2,Rates!A:B,2,0)
When I made the spreadsheet, the formula worked perfect. But when they type their codes in and send it back to me, it's messed up. All of the codes they've typed in do pull the correct rate. However, if I try to go to column E and type a different code on top of what they typed (a code that IS IN the rate table), it will tell me: "The value you entered is not valid. The user has restricted values that can be entered into this cell".
If I create a new tab and mimic the exact formula and columns as I am trying to work with, it works perfectly. But for some reason, once they've typed their data into my spreadsheet, something messes up even though the formula is perfect, dragged down, etc. All columns are formatted as text. And the range in the rate table is correct as well. When I try to mimic the formula in the other tab, if I click a cell in column E, there is a drop down list there showing all the available codes from my rate table to choose from. But on the spreadsheet that is messed up has no drop down list on the cells.
I have attached a sample of the issue. The formula is found in cell B49. If I enter a gravity of 2.76 or higher in cell B47 I get #N/A in cell B49. Why will it not read any farther over than 2.75. I have been starring at this for awhile now. I might look pretty dumb after someone else looks at it.
View 2 Replies View RelatedI'm just looking to prevent users from using the print screen or the alt print screen buttons on the keyboard. I have this script that works if I use "39" (Right Arrow)but wont work if I use "42" (Print Screen Button).
View 5 Replies View RelatedI have one column that contains an If statement formula and would like the next column to then work off of the first column (i.e. if that 1st column returns a value then then adjacent column uses that result).
What is happening now is that it is returning #value (because I guess technically the cell isn't blank?)
In sheet 1, I have
Col A Col B
John Sedgwy - R
In sheet 2, I have the following names
Col A Col B
Peter Walker
John Sedgwicky
When I did an array match -> ={MATCH(1,(TRIM($a2)=Sheet2!$A$1:$A$2)*(left(b2,5)&"*"=Sheet2!$b$1:$b$2),0)}
gave me an error but when I did an individual match to both John and Sedgw, it works.
some call databases from my internal telemarketing team, which are all on Excel databases. These contain multiple contacts within the same organisation, with no "unique identifier", i.e. there is no information specific to individual records EXCEPT their email address - and unfortunately, not every contact has an email address, which would prevent using VLOOKUP, which is the only function I can use to perform this type of lookup.
I have five separate spreadsheets, plus one master database spreadsheet. I have added five columns to the end of my master database, and I would like to use each column to identify which spreadsheet(s) the individual records appear in - normally this would be possible by using a VLOOKUP in each of the five new columns, selecting a unique identifier, and using the VLOOKUP function for each separate spreasheet. However, without a consistent unique identifier, I do not know another function which would allow me to use multiple identifying info (e.g. "FirstName" + "Surname" + "Company") to perform this task.
If SheetExists(Sheets("data").Range("V" & x).Value) Then
'do Nothing
Else
Sheets.Add
ActiveSheet.Name = Sheets("Data").Range("V" & x).Value
End If
ProjectReview.Show
Public Function SheetExists(SName As String, _
Optional ByVal Wb As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
If Wb Is Nothing Then Set Wb = ThisWorkbook
SheetExists = CBool(Len(Wb.Sheets(SName).Name))
End Function
I'm trying to add a new sheet, if a sheet with that name doesn't exist already.
Which I have a feeling is pretty simple!. My Error is occurring at the * in the code. Basically I am adding a sheet per record. I'm using a unique identifier for each tab. The first run through, it creates a new sheet and opens a user form. When I close out that form, it returns to here and fails to work the second time through. I'm fairly confident it's because I'm using a select statement, I've also tried using it this way, but the set statement is invalid because it's not refrencing an object....
I need some information about How the auto filter in excel works?
Excel shows a drop box with list of unique values in that column, if i select one value only rows having that value are displayed.
Are other rows "hidden". I tried to unhide these rows but those are NOT hidden. What actually happens to rows that don't satisfy the filter criteria?
I wrote a UDF that does some advanced lookup trick in a particular workbook. That workbook has to be open during this process of course. I used to have a copy of this UDF in several workbooks where I need to use it. This turns out to be bothersome since I needed to update the UDF frequently. So I decided to make an add-in.
I then copied the UDF to a new workbook, save it as an add-in (XLA), then go into Excel Options - Add-in (I use Excel 2007) and enable the add-in there. I then remove the UDF from each and every workbook that used to have it.
Now I close everything.
I then open the lookup workbook that needs to be open if I were to use the UDF. Here is where I don't understand. If I open a new workbook and use the UDF, it works fine. If I save the new workbooks then reopen them, the function still works. However if I open some old workbook where the UDF used to be and try to use the UDF, Excel gives me #Name? errors.
I have put together the following code and it works fine on my PC. However, we are a mainly Mac based company and I nead it to work with a Mac. I am using the Actual ODBC Driver for Mac to give me a ODBC connection. I can do the query in excel but setting it up as a macro just will not work.
Sub Sales_Query()
Columns("C:D").Select
Selection.Delete Shift:=xlToLeft
Range("B2").Select
Dim area As Variant
area = Range("B2")
With ActiveSheet.QueryTables.Add(Connection:= Array("ODBC;DSN=my_database;Description=My................................
I have a very simple macro that copies data from one worksheet to the other. This macro worked fine in Excel 2007. I have since migrated to to 2013. The newly arisen problem is that when I run the macro, the data is not copied to the 'Previous Part Data' worksheet. However, if I step through the code line by line, it works fine.
Here is the code:
VB:
Sheets("Current Part Data").Select 'Select the 'Current Part Data' worksheet
'Copy the data to the 'Previous Part Data' worksheet then clear the data
Range("A4:V" & CurrentPartDataFinalRow).Copy
[Code]....
I have two questions:
1. Why did this work in 2007 and not in 2013?
2. Is there something I can do to get rid of the sheet selection statements to avoid cluttering my code?
I've sent the file to 2 different windows computers and it works fine, but doesn't work with the two macs that I've sent it to.
This is the code:
[Code] .....
How do I use Find to locate the first occurence of a particular value in a cell, and return the row that the cell is in?
View 14 Replies View RelatedThis code, supplied through this forum, works perfectly in one workbook but not another. I have created a range called ColourRange, one called ColourIndex but I am getting a '400' error message when I attempt to run it. Can anone explain to me (in very simple terms) why it won't work?
Sub CopyFormatMMT()
'Colour code Owners
Dim r As Range
Dim f As Range
Dim c As Range
Dim j As Range
Set r = Range("ColourRangeMMT")
Set f = Range("ColourIndex")
Range("ColourRangeMMT").Select
Selection.Interior.ColorIndex = xlNone
For Each c In r.Cells
For Each j In f.Cells
If c = j Then
c.Interior.ColorIndex = j.Interior.ColorIndex
End If
Next j
Next c
Range("C9").Select
End Sub
I run PCs at work and at home. Both machines run Windows XP and Office 2007
Home PC: Windows XP Home edition Version 5.1 (Service Pack 3)
Excel 2007 SP1 MSO
Work PC:Windows XP Professional version 5.1 (Service Pack 3)
Excel 2007 SP1 MSO
The VB was created on the home PC. It works. On the Work PC it crashes on the line with bold. Run-time error 9 - Subscript out of range. This have never happened before (or with any other VB routine). I also changed fileNew to include the full path, but same error. What has caused this? I suspect it’s the 'fileNew'.