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....
Code: Function CA2GT(ByVal S As String) As String Dim x As Long, CA As Long, Total As Long CA = InStr(1, S, "CA", vbTextCompare) For x = CA To Len(S) If Mid(S, x, 1) = "(" Then
'replace arguments: oldtext, start at number, number of characters, new text
[Code] .......
It works on one workbook but not another workbook. Why?
I just started using vb two days ago so the problem is probably from the code. I created a userform with 2 comboboxes and 10 textboxes, when i choose an item from the first combobox it filters out the result from the second combobox.
And when i choose an item from the 2nd combobox it shows certain textboxes corresponding to that item. Everything works fine if i run the userform while on the sheet i pull the data off of (Devices sheet), but if i try it on another sheet then the comboboxes don't work anymore.
Another question i had is if there is simpler way to view the textboxes rather than the one i used in my code. And if there's a way to autoarrange the textboxes when i they are visible so that they all are in the same row.
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 this code attached to a button on the first sheet of a workbook with hundreds of sheets.
it is suposed to look for a cell that contains "SAY:" and then move one column to the right and make it a zero. It works on the first sheet but not on any other sheet.
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.
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
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?
This 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'.
Really simple data sheet: list of names with homerooms Lookup table: list of homerooms with teachers. (named "Range")
=VLOOKUP(E2,Range,2)
Some of the cells return the correct name, others #N/A.
As far as I can tell they are all formatted exactly the same; I've tried several different formatting options. The correct ones stay the same; the no data ones do not change either.
I have a very simple spreadsheet that I use for preparing quotations. It was created on my PC at home and includes a simple roundup function (=ROUNDUP((F199*G199)+F199 2) ).
I now use this spreadsheet on my PC at the office and the function works perfectly well but if I try to edit it, or create a similar rounding fx, on my office PC I keep getting the error message "You've entered too few arguments for this function".
I've tried using the Help Menu in Excel but even if I copy one of the formulas from the Help Menu into the spreadsheet I still get the error message. The 2 versions of Excel are the same (2003). I tried inserting a ',' and a ';' in front of the '2' but this has also not helped.
I have attached a sample of the offending spreadsheet. The rounding function is in column "B"
I 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.
I want to have a fullscreen application all the time, so for this I've added to things in my code:
1) Application.DisplayFullScreen = True in Workbook_Open
2) Same in WorkBook_WindowsResize
I open my workbook, it goes fullscreen. Until now all OK. However, if I press ESC or doubleclick on the topbar, it goes out of it.
Now the weird thing: if I click the "Restore window" button (which is right under the application maximize button that incidentally I have disabled) it snaps on fullscreen and THEN it keeps this state forever, even on ESC/doubleclick. Which is what I want.
So my question is: what does exiting from fullscreen and going back in change so that the workbook event starts triggering, and how I can replicate it programmatically? I've already tried WindowState = xlNormal followed by xlMaximize, it doesn't change anything.
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.
I’m trying to do the following IF function but as far I can tell IF only works with 7 different selections.
Cell B2 contains the numbers I through 8 Cell D2 contains a pre-calculated number (e.g. 53.012) Cell F2 is the cell that I need an IF function or something similar – the function would be as follows =IF(B2=1,D2*1.000) =IF(B2=2,D2*1.00057) =IF(B2=3,D2*1.00171) =IF(B2=4,D2*1.002281) =IF(B2=5,D2*1.003421) =IF(B2=6,D2*1.003991) =IF(B2=7,D2*1.004215) =IF(B2=8,D2*1.004538)
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
I have written this code to change the colour of a row of cells to bright green and to change the cell contents of cell(Row, 15) from “L” to “F”: This is the main workbook into which are pulled values from three others. I then want it to open one of the subsidiary workbooks, that feeds the information, to change the same job row to bright green and to change the cell contents of cell(Row, 15) from “L” to “F”. Then I want to save the changes and return to the main sheet: - The ActiveCell contains the Job Number.
Option Explicit Public rw As Integer Public Col As Integer Public Job As String Public JobNo As String Public RowNo As Integer Public wName As String Public times As Integer Public Pips As String Public Nicolas As String Public Cindys As String Public wb
Sub FinishedinOfficeCindy() . rw = ActiveCell.Row JobNo = ActiveCell Col = ActiveCell.Column If Col 1 Then Check that the correct MsgBox "Please choose the Job Number first"column is selected and Exit SubThat the workbook is End Ifthe correct one If Cells(rw, 14) "Cindy" Then MsgBox "Wrong Secretary Chosen - whoops!" Exit Sub End If
All works perfectly when I step through it but, when it is activated (Ctrl+Shift+C) it opens the other workbook and then seems to end? I even tried putting in delays to see if they would help but they didn’t.
Changing the first row of the range to row 2, or adding the FALSE at the end makes it work. But why would it work in 2003 and not in 2007?
This is an *enormous* file, and this is just one example of a *load* of formulas that are returning errors (so far, all seem to be related to VLOOKUPs - at least the ones I've found so far). It's 25MB worth of complicated formulas, with external links to Access databases for the source data, just to give you an idea of the scope. So going through and trying to find and then change every error that might be occurring just because 2007 doesn't like it is going to be a complete disaster. You never find them all. It might be simpler to just downgrade back to 2003!
I create a vb script file using SAS that will open excel and create pivot tables. It works in XP but not in office 97. I am not a vb expert. Here is the code.
If you run the Int function on a product of two variables, it will return a different (wrong) result if the variables are both defined as single:
Sub roundingtest() Dim a As single Dim b As single Dim cases As Integer a = 18200 b = 0.01 cases = Int(a * b) Debug.Print cases; End Sub
this returns 181 instead of 182. If you define one of the variables as double, then it works fine. Is it just me?
Anyway, I found it to be quite useful, as i was trying to get a function to round to the first higher integer - as opposed to the first lower. so 182.1 -> 183 182.9 ->183 182 ->182
So weirdly enough, int(a*b)+1 does the trick! Of course if a or b is defined as a double, then it all goes to 182. So without any IFs, this works real nice!
I have a proyect evaluation. I use solver to find a value, which works. I recorded a macro for this situation (which repeats all the time), but when I run it, it does not work. The problem is not the answer, it is that the macro does not run
Sub TIR() SolverOk SetCell:="$M$41", MaxMinVal:=1, ValueOf:="0", ByChange:="$C$44" SolverSolve Range("E44").Select End Sub
i am trying to use the autofilter command to paste 4 columns else where on the page using unique = true. this only will run once. when i try to run it again everything is blank. please see code below.
Sub copyindex01() Columns("L:O").Select Range("O1").Activate Selection.Delete Shift:=xlToLeft Range("G2").Select Columns("G:J").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Columns( _ "M:P"), Unique:=True Range("L1").Select End Sub
I am trying to programmatically select an item from a single selection listbox (i.e., set to fmMultiSelectSingle). The following code works properly only every other time I run it; the problem is extremely repeatable.
Const storeStartCell = "B5000" Private Sub ListBox_CounterTOs_Click() selectedTOName = CStr(Mid(Worksheets("Sheet1"). Range(storeStartCell).Offset(ListBox_CounterTOs.ListIndex, 0), 3, 3)) selectedTONumber = CInt(Mid(Worksheets("Sheet1").Range(storeStartCell).Offset(ListBox_CounterTOs.ListIndex, 0), 6, 4)) ListBox_TO_Name.Value = selectedTOName ListBox_TO_Number.Value = selectedTONumber End Sub
The times that it works, ListBox_TO_Name.Value is set to something like "ABC" and ListBox_TO_Number.Value is set to something like "1234". When the values get set properly, the associated click subroutines (e.g., ListBox_TO_Name_Click()) get called automatically when the value is set. The times that it doesn't work, they are both set to "" and the click subroutines do not get called. I also tried
