Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
if not found, add/insert
I have a worksheet, sheet 1, with Name, Date, Hours, and WorksheetName
They fill these value and runs this code with Name Date and Hours: ENAME = Sheets("sheet1").Range("a14", "a14").Value EDate = Sheets("sheet1").Cells(7, 8).Value EHOURS = Sheets("sheet1").Range("a20", "a20").Value flName = "TIME SHEETS.xls" flPath = "C:\Documents and Settings\Jon Monagan\My Documents\Gary\" OpenSheet = Sheets("sheet1").Cells(18, 5).Value If Not WorkbookIsOpen(flName) Then Workbooks.Open (flPath & flName) Set wbTS = Workbooks(flName) wbTS.Activate wbTS.Sheets(OpenSheet).Activate lRow = wbTS.Sheets(OpenSheet).Columns(1).Cells.Find(ENAME , LookIn:=xlValues).Row iCol = wbTS.Sheets(OpenSheet).Rows(1).Cells.Find(EDate, LookIn:=xlValues).Column wbTS.Sheets(OpenSheet).Cells(lRow, iCol) = EHOURS AND/Or THIS CODE with Name Date Hours And WorksheetNAme: flName = "MISC. HOUR COMP.xls" flPath = "C:\Documents and Settings\Jon Monagan\My Documents\Gary\" OpenSheet = "Data" If Not WorkbookIsOpen(flName) Then Workbooks.Open (flPath & flName) Set wbTS = Workbooks(flName) wbTS.Activate wbTS.Sheets(OpenSheet).Activate What I need is something to add to these that if it can't find the name or date in the first block of code, add the date or name In the second block of code I need something that will insert a new worksheet and name it the specified name if it can't find it. Any ideas? or direction --- Message posted from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
if not found, add/insert
It's usually easier to use a range variable to look for something--then you can
check to see if you found it: dim FoundCell as range .... with wbts.sheets(opensheet) set foundcell = .Columns(1).Cells.Find(ENAME, LookIn:=xlValues) if foundcell is nothing then lrow = .cells(.rows.count,"A").end(xlup).row + 1 else lrow = foundcell.row end if And you can use Chip Pearson's code to check for the existence of a worksheet: Function WorksheetExists(SheetName As String, _ Optional WhichBook As Workbook) As Boolean 'from Chip Pearson Dim WB As Workbook Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook) On Error Resume Next WorksheetExists = CBool(Len(WB.Worksheets(SheetName).Name) 0) End Function ------ in your code: OpenSheet = Sheets("sheet1").Cells(18, 5).Value if worksheetexists(opensheet,workbooks(flname)) then 'do nothing else workbooks(flname).worksheets.add activesheet.name = opensheet end if === I think I referred to the correct workbook. "monagan <" wrote: I have a worksheet, sheet 1, with Name, Date, Hours, and WorksheetName They fill these value and runs this code with Name Date and Hours: ENAME = Sheets("sheet1").Range("a14", "a14").Value EDate = Sheets("sheet1").Cells(7, 8).Value EHOURS = Sheets("sheet1").Range("a20", "a20").Value flName = "TIME SHEETS.xls" flPath = "C:\Documents and Settings\Jon Monagan\My Documents\Gary\" OpenSheet = Sheets("sheet1").Cells(18, 5).Value If Not WorkbookIsOpen(flName) Then Workbooks.Open (flPath & flName) Set wbTS = Workbooks(flName) wbTS.Activate wbTS.Sheets(OpenSheet).Activate lRow = wbTS.Sheets(OpenSheet).Columns(1).Cells.Find(ENAME , LookIn:=xlValues).Row iCol = wbTS.Sheets(OpenSheet).Rows(1).Cells.Find(EDate, LookIn:=xlValues).Column wbTS.Sheets(OpenSheet).Cells(lRow, iCol) = EHOURS AND/Or THIS CODE with Name Date Hours And WorksheetNAme: flName = "MISC. HOUR COMP.xls" flPath = "C:\Documents and Settings\Jon Monagan\My Documents\Gary\" OpenSheet = "Data" If Not WorkbookIsOpen(flName) Then Workbooks.Open (flPath & flName) Set wbTS = Workbooks(flName) wbTS.Activate wbTS.Sheets(OpenSheet).Activate What I need is something to add to these that if it can't find the name or date in the first block of code, add the date or name In the second block of code I need something that will insert a new worksheet and name it the specified name if it can't find it. Any ideas? or direction --- Message posted from http://www.ExcelForum.com/ -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
if not found, add/insert
Thanks for the Reply, but I've been working on this, and discovered som
other code. I just wanted to know if you had some thoughts on my date because it cannot seem to find the date that matches. So everytime add an entry, it adds the date again in row 1. For instance: 9/15/04 9/15/04 9/15/04 9/15/04 Bob 5 Jen 2.5 Harm 4 Jill 8 Here is my code that is not working: (This first line) Set fnd = Rows(1).Cells.Find(EDate, , , xlWhole, xlByColumns, xlNext) If fnd Is Nothing Then .Range("IV1").End(xlToLeft).Offset(0, 1) = EDate IntersectCol = .Range("IV1").End(xlToLeft).Offset(0, 1).Column Else IntersectCol = fnd.Column End If My code for name is very similiar to this and to yours that yo posted: 'find name Set fnd = .Range("A2:A" & lRow).Find(EName, , , xlWhole, xlByRows xlNext) If fnd Is Nothing Then .Cells(lRow, 1).Offset(1, 0) = EName IntersectRow = lRow + 1 Else IntersectRow = fnd.Row End If But this works fine. Any advice, or tweeking I may have missed -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
if not found, add/insert
Yeah, sometimes dates and VBA don't works so well together.
Maybe it'd be better to use something like: Dim res As Variant With ActiveSheet With .Rows(1) res = Application.Match(CLng(Date), .Cells, 0) If IsError(res) Then MsgBox "not found" Else MsgBox res 'same as column End If End With End With "monagan <" wrote: Thanks for the Reply, but I've been working on this, and discovered some other code. I just wanted to know if you had some thoughts on my date, because it cannot seem to find the date that matches. So everytime I add an entry, it adds the date again in row 1. For instance: 9/15/04 9/15/04 9/15/04 9/15/04 Bob 5 Jen 2.5 Harm 4 Jill 8 Here is my code that is not working: (This first line) Set fnd = Rows(1).Cells.Find(EDate, , , xlWhole, xlByColumns, xlNext) If fnd Is Nothing Then Range("IV1").End(xlToLeft).Offset(0, 1) = EDate IntersectCol = .Range("IV1").End(xlToLeft).Offset(0, 1).Column Else IntersectCol = fnd.Column End If My code for name is very similiar to this and to yours that you posted: 'find name Set fnd = .Range("A2:A" & lRow).Find(EName, , , xlWhole, xlByRows, xlNext) If fnd Is Nothing Then Cells(lRow, 1).Offset(1, 0) = EName IntersectRow = lRow + 1 Else IntersectRow = fnd.Row End If But this works fine. Any advice, or tweeking I may have missed? --- Message posted from http://www.ExcelForum.com/ -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What is a BTW? Where can BTWs be found? | Excel Discussion (Misc queries) | |||
Look up a value in one colum and insert the value found in the adj | New Users to Excel | |||
IF NOT FOUND | Excel Worksheet Functions | |||
Search for a test string and if found insert 'x' in clumn 'A' | Excel Discussion (Misc queries) | |||
not found | Excel Programming |