if not found, add/insert

[monagan]

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

 

[Dave Peterson]

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]

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

 

[Dave Peterson]

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