Check For Same Data In Another Worksheet
Jul 30, 2006
A workbook contains two worksheets. The first sheet is a membership list (Mem06) containing adresses and telephone numbers. The second sheet is a list of activity groups (Groups) with members details who are attending. There are 28 activity groups whose venues are scattered over 20 miles, each has a Leader. It is difficult for the Leader to know if those attending are paid up members, we know that some are lapsed. The solution is to get those members attending to complete a form with their name and 1st line of address. The membership secretary enters the names in the Group sheet (1st line of address only required when people have the same First and Surname).
We would like a formula to check this entry against the list in Mem06. If the entry corresponds the Telephone number for that entry would be returned to the Group sheet. Col A in both sheets has a formula to Concatenate First Name and Surname. The Telephone number in Mem06 is at Col I. The required formula would be in the Group sheet in the Telephone number col.
View 9 Replies
ADVERTISEMENT
Feb 4, 2010
I would like a macro that adds a worksheet with the name say XML_JB to a workbook. I have ran the record button as shown in the code below but when I re-run the Macro the line tries to add the worksheet in Sheets("Sheet5").Select and I get a debug error. I realise the issue is around the fact that the count needs to be set some how as a variable and not set as Sheet 5 as the macro can't find sheet 5. The Macro also needs to check if the worksheet “XML_JB” is there and if so throw up a message like XML_JB already exists Do you want to continue? And if yes skip in adding of the worksheet and run my first macro. But as this is my second macro I don’t know how to go about it?
View 3 Replies
View Related
Mar 10, 2009
i would like to know whether a worksheet exists or not... say, the worksheet name is sheet1, i need to check whether the worksheet exists... the point is, i need to delete the worksheet if it exists and execute a code... if the worksheet doesn't exist also, i need to execute the code... so im getting a little confused with this... i need something like this...
if sheet1_exists then
delete the sheet
end if
my_code.......
View 9 Replies
View Related
Dec 14, 2007
I have a problem to check the value of a checkbox when I write the code in Module1. I have several worksheets, and in each worksheet there is a CheckBox1. I want the macro (in Module1) to perform a given code when the CheckBox1.value = true and visa versa.
The code I'm using is as follows:
Option Explicit
Global ws As Worksheet
Application. ScreenUpdating = False
For Each ws In Worksheets
If CheckBox1.Value = True Then
'Do code1
Else
'Do code2
End If
Next ws
Application.ScreenUpdating = True
End Sub
When the Macro is run, an error msg is showing "RunTime error '91'.
Does anyone know how to make this macro function? I've searched the forum, but couldn't make any of the codes suggested to work.
View 3 Replies
View Related
Nov 8, 2011
I have an array that opens a workbook containing close to 100 worksheets, and copies specific sheet names into there own individual workbooks. The problem I have now, is that I assume the worksheet exists in the workbook, but often times it does not Is there a way to add some sort of "catch" that will 1st verify the worksheet exists instead of my code crashing?
View 9 Replies
View Related
Mar 23, 2013
What is the simplest way to do it in VBA?
View 3 Replies
View Related
Sep 9, 2006
I want Excel VBA to check for named worksheets. Based on the return, I will have VBA either (1) delete the named worksheets and replace them or (2) refresh the pivot tables on the named worksheet. how to make VBA check for the presence of the named worksheets. My efforts are below and comments at the end of the Sub detail what I want to have happen.
Sub EnterProgram()
Dim Current_P As String, New_P As String
Current_P = Range("data!C2")
If Current_P = "" Then
New_P = InputBox("Which program?")
Do While New_P = ""
Prog = InputBox("Try again... Which program?")
Loop
Range("data!C2") = New_P
ElseIf Current_P = New_P Then
If MsgBox("Use the current program (yes or no)?", vbYesNo) = vbYes Then
Range("data!C2") = New_P
End If........................................
View 2 Replies
View Related
Jul 3, 2007
I am writing a macro which first needs to look and see if a certain worksheet exists. If it doesn't then I want it to create it but if it does I first want it to delete the sheet then create a blank one with the same name. At trhe moment I have this but it is scrappy and doesn't work very well:
On Error Goto AddSheet
If Sheets("MONEYSHEET").Activate Then Goto Data
AddSheet:
Sheets.Add before:=Sheets("LOOKUPSHEET")
ActiveSheet.Name = "MONEYSHEET"
Data:
It also does not delete the sheet if it already exists, just edits the existing one.
View 2 Replies
View Related
Dec 6, 2007
Trying to reference cells in other worksheet. Error comes after top line of code.
If Worksheets("Cheat Sheet").Cells(intActiveRow, intActiveCol) = "Mine" Then
Cells(intActiveRow, intActiveCol).Interior.Color = vbRed
MsgBox "You Lose!", vbOKOnly, "MineSweeper"
UserForm1.Enabled = True
cmdUncover.Enabled = False
cmdMark.Enabled = False
Exit Sub
End If
View 2 Replies
View Related
Apr 6, 2014
I need to add over 100 rows on the top of my check register worksheet, is there any way to do automatically?
View 4 Replies
View Related
Nov 3, 2009
I'm looking to use the value from a series of dropdowns (made via data validation lists).
Cell AM5 is a dropdown of named ranges made form a seperate sheet
Cell AO5 is a dropdown that uses '=INDIRECT(AM5) to lookup the values in the named range.
I need cell AO5's value to be used to filter rows in the current worksheet. The current problem is when i use the first dropdown in AM5 it still displays the last value, untill i use the dropdown to select a new one. This value typically will not be found and i do not want my code to execute in these cases.
View 4 Replies
View Related
Mar 10, 2012
I'm not sure why this macro in workbook "A" is not working when checking for the activesheet sheet opened in workbook "B"? I've included the tab name I'm looking for and the tab name opened in the workbook "B" in the MsgBox to verify it can pull the tab names properly in workbook "B" and it returns correctly. I'm not sure why the If Then Else is not working if that is the issue here.
Code:
Sub testmonthcheck()
Dim art156 As Workbook
Dim mm As String
Dim mmm As String
Set art156 = Workbooks("Actual_Run_Times_156.xls")
[Code] ........
View 1 Replies
View Related
Aug 31, 2006
I'm creating a macro to select, modify dimensions and place shapes on a excel sheet. (I'm talking about pictures insered and stocked in a specific sheet of my workbook)
When the users insert a new picture he has to set a name for each of them.
A combobox contain the choices, when an item is selected, the macro identify, size and place the corresponding picture.
But I have a bug if a shape doesn't exist when I try to select it :
Sheets("fiche de controle").Select
'select the sheet with the pictures
ActiveSheet.Shapes(Item_old).Select
'select the shapes "Item_Old"
Item_Old is a variable corresponding to the picture name.
If the user made a typo, I have a bug.
Someone knows how to test if the shapes exist to display a meesage if not ?
Or somethig to avoid this kind of bug ?
View 6 Replies
View Related
Apr 16, 2007
I need to allow users to add up to seven new worksheets as required and the new sheets must be named Image1, Image2, etc. I do not want to add seven worksheets at once, only one at a time as the need arises and no new ws can be allowed after Image7 ws is added.
I have a macro to test for the existence of ws Image1, but need help on further development to add and test for the extence of the remaining sheets.
Dim ws As Worksheet
Dim bTest As Boolean
For Each ws In Worksheets
If ws.Name = "Image1" Then
bTest = True
Exit For
End If
Next ws
If bTest = True Then
'Need some code here, not sure what I need
View 9 Replies
View Related
Apr 17, 2008
I have multiple worksheets of computer equipment, each worksheet is a group/department. Column H is the serial number column and the entries have to be unique. I have managed to create the code below which does find duplicates across worksheets.
When error message pops up about which sheet the duplicate already exists on, the duplicate entry is deleted and the cell is blank but the error checks again and reports the blank existing on another worksheet and then it is stuck in a loop. How can I ignore the blank or null.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim I As Integer
I = Sheets.Count
If Not Intersect(Target, Range("H2:H200")) Is Nothing Then
Do Until I = 0
If Application.IsError(Application.Match(Target, Sheets(I).Range("H2:H200"), 0)) Then
Else
MsgBox "That entry already exists in the " + Sheets(I).Name + " sheet"
Target.ClearContents
End If
I = I - 1
Loop
End If
End Sub
View 6 Replies
View Related
Dec 7, 2007
I have a cell in my spreadsheet that is used to designate if the row needs to be hidden or not. If the user enter's "Y", the row is hidden. I need to check some of the previous cells for entered data before the row is hidden. If data is not entered in these cells I want the empty cell(s) to be filled with a color. The user will then have to enter a value into all the correct cells before the function to hide the row will work. What is the best way to do this? I am using the following function to check for the "Y" and hide the row.
View 11 Replies
View Related
Jun 15, 2012
Check and format some data. I have 100,000+ rows so kinda need a macro to do it.
I have data like this
Sheet1 Â ABCDEFG1start_date start_time end_date end_timeTypeSOC Before SOC After
208/01/201218:36:2008/01/201218:40:00TÂ Â 308/01/201218:45:1208/01/201218:50:44TÂ Â
408/01/201218:52:1208/01/201219:50:46C3446508/01/201219:54:2708/01/201221:44:40C
[Code] .....
I'm trying to merge all the "C" rows in Column F into one row, like this:
Sheet1 Â ABCDEFG14start_date start_time end_date end_timeTypeSOC Before SOC After
1508/01/201218:36:2008/01/201218:40:00TÂ Â 1608/01/201218:45:1208/01/201218:50:44TÂ Â
1708/01/201218:52:1208/01/201223:02:00C341001809/01/201208:16:5109/01/201208:44:08TÂ Â
1909/01/201208:45:1109/01/201208:49:55TÂ Â
So you would just keep the first row,but replace the 'end date' and 'end time' with the 'end date' and 'end time' from the last row and same with the SOC.
View 1 Replies
View Related
Dec 11, 2006
I have the following macro which:
- opens a database checks each sheet in the database for a contract number,
- highlights the row in which it finds the contract and updates the database with claim number etc (not important)
- returns information to another sheet from the database
The macro works fine BUT I have recently realised that if the contract has already been paid out it will simple overwrite the information and I will not be aware that the contract had already been paid out.
I need the macro to return a value of "Already claimed" instead of "Found in Database" only if the contract number has already been updated.
Sub DataBaseCheck()
' Designed to check Database for claims issued each month
' Open Data Base file to facilitate matching process.
' Check for matching data
' Return checking data to spreadsheet & Update Database
Dim currentfile As String
Dim WS As Worksheet
Dim R As Range
Dim Myvalue As String
Dim Myrange As Range
Dim Tcell As Range
View 9 Replies
View Related
Mar 23, 2008
I am looking a program to check if any data be duplicated to avoid any mistakes.
There are many program for duplicate check but they all only compare the cells but not to check the data within any cells one by one.
Is there any program to check the duplicated data in the selected column, columns or cells?
View 9 Replies
View Related
Jun 14, 2009
I'm trying to check if data in column u is in column g if so highliglt the
Cell row in column G
Sub Macro1()
Sheets("Recipes").Select
Rows("2:5602").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
rlc = LastCell(ActiveSheet).Address(False, False)
lr = ActiveCell.Row
lc = ActiveCell.Column.............
View 9 Replies
View Related
Jul 19, 2007
If my cell range in Sheet 2, K2 to K25 is empty/blank, then I want to fill that blank range with the same number/Value. It can be a 0 or a 1 or any other Value as required. But an Error 13. Type Mismatch message appears. Where is the error if I'm trying to fill the range with a Value if blank ?
Sub Macro_2()
Sheets("Sheet2").Select
Sheets("Sheet2").Range("K2").Select
If Sheets("Sheet2").Range("K2:K25").Value = "" Then
Sheets("Sheet2").Range("K2:K25").Value = "1"
End If
Sheets("Sheet3").Select
End Sub
View 2 Replies
View Related
Nov 27, 2009
My company uses 4 types of barcodes 8, 12, 13, & 14 number barcodes for our products my problem is that I can't figure out how to force the barcode to format properly no matter how somebody enters it if they don't use spaces or put them in the wrong spots, I can't use custom formats because there is 4 different layouts
8 digit should be "#### ####"
12 digit should be "###### ######"
13 digit should be "# ###### ######"
14 digit should be "# ## ##### ######"
these barcodes are in columns L, M, & N also right now 'm using a formula in another cell to verify the barcodes by calculating the check digit and comparing it to the check digit typed the formula i'm using is
View 9 Replies
View Related
Aug 10, 2012
I have data in Row 53 that spans 7 columns, but stays in the same row. I want to design a loop to select every 7th cell in that row and check if it is empty. If not, add onto a "counter" then display the final number of occupied cells (the value of the counter) at the end. This is what I have so far, but I get all sorts of errors.
Code:
Sub Tester()
Dim WB As Workbook
Dim WS As Worksheets
Dim modCounter As Long
Dim Cell As Range
Set WB = Workbook("Transverse Series.xlsm")
Set WS = WB.Sheets(BM18)
[Code] ......
View 1 Replies
View Related
Dec 23, 2013
I have a workbook with 4 worksheet that store different type of data. It also has a userform that load at start of the application which is to search the data in the workbook. The userform has a combobox where the names of the sheets are stored. when the user selects say Sheet2 in the combobox, it enables the relevant textboxes on the userform and activates the worksheet at the change event. The userform has a search button that searches all the worksheets based on the text entered in a textbox.
The problem: how to search based on 1 textbox. What I want is: say for e.g the end-user selects sheet2 from the combobox, this intern enables 4 textboxes (Name, DOB, Nationality, ID #) on the userform. The end-user should have the liberty to enter data in 1 and/or any of the textboxes. The search should be performed, that if data is only in 1 of any of textboxes then give all rows that fit that criteria and display in a temp worksheet. if say the name and dob is filled by the user than what matches both should be displayed in a temp worksheet. if say dob, name and ID# given so the search button should narrow down to fit all 3 criteria and then display result in temp worksheet. As if mentioned data can be entered in either just 1 or any or all textboxes.
E.g. the worksheet is (Columns are Name, Nationality, DOB, ID#)
row 1 = name: Steven Martin, DOB: 27-may-1993, Nationality: Trinidad & Tobago, ID #: 1234567
row 2 = name: Gary Richards, DOB: 2-FEB-1993, Nationality: British, ID #: 456789
row 3 = name: David Cohen, DOB: 27-May 1993, Nationality: American, ID #: 98765
row 4 = name: Roberto McDonalds, DOB 21-Jul-1962, Nationality: British, ID # 654321
row 5= name: Gary Richards, DOB: 01-Dec-1978, Nationality: Australian, ID # 1234567
Now if the user enters only name as "Gary Richards" and search then row 2 and 5 should be displayed in a temp worksheet. if user enters name Roberto McDonald and ID# 1234567 then it should not display anything. if user enters DOB 27-may-1993 and nationality British and ID # 1234567 then as well shouldn't display anything and should a msgbox "no data found".
View 4 Replies
View Related
Feb 7, 2014
I need to reference the projected and actual expenses from the total on worksheet 'expenses' So i did so, however, if I change the drop down on the expenses worksheet to only display housing data, then the projected and actual expenses on my budget worksheet changes as well to the new data portrayed on the expenses worksheet.
I need to reference the cell, without it changing when I change the category display, but I need the cell to change accordingly if I enter new data in the overall tablet on the expenses worksheet.
Or do I need to create a seperate worksheet that has the data in and reference my cells on my budget worksheet to that new worksheet?
View 1 Replies
View Related
Aug 22, 2014
I've got a spreadsheet with 2 worksheets in it.
On the first one we've got:
Name of the agent | petition REf num | Task
Each petition can generate several tasks, one line per task.
John Doe | XXXX-YYYY | NCO
John Doe | | RIL
John Doe | XERT-WWWW | RMT
Jane Doe | QSZE-AQWC | RIL
On the second worksheet:
Complete list of agents | number of petitions | Status
John Doe | 2 | OK
Jane Doe | 1 | [BLANK]
I want to be able to fill in the second worksheet automatically. For each agent in my worksheet 2, I want to check if they appear in worksheet 1 and if so count the number of petitions related.
I don't know where to start
View 6 Replies
View Related
Mar 17, 2013
Dropbox - Final.xlsm
Here is the above link. Am looking at a button which saves whatever is the temp worksheet row in the customers worksheet. The temp worksheet basically takes the data from the Quotations worksheet and places it in a row.
View 9 Replies
View Related
Feb 23, 2014
I am copying data from worksheet "Microsoft" to another workbook and paste in sheet1, i want the cell G1 to auto input the worksheet name "Microsoft" where i copy the data from,
How to have G1 show the worksheet name after i copy and paste the data from worksheet name "Microsoft".
View 6 Replies
View Related
Mar 21, 2013
So let's say I have a table like this:
Apple
Banana
Cherry
Banana
Durian
And when I tick one row:
Apple
Banana
Cherry
Banana
✓
Durian
The rest of rows of same values will also be ticked:
Apple
Banana
✓
Cherry
Banana
✓
Durian
I would like to have the macro/VBA for this.
This question was also posted at:
ExcelForum: [URL]...
MrExcel: [URL]....
View 1 Replies
View Related
Dec 4, 2013
I need a formula to check if anyone from the Old employee col(B) exist in current employee column(A).
A B
Current employees Old employees
A C
B G
C T
D
E
F
G
View 2 Replies
View Related
