if not there, add/insert 2

[monagan]

I have sheet1 which a user enters in information. Name Date and WorksheetName

In a different workbook, If it doesn't find Name I want it to add it after the last name in column A. If it doesn't find date, I want it to add it after the last date in row 2. And if it doesn't find the worksheet, I want it to insert a new worksheet.

Any advice?

"We have enough youth, How about a fountain of SMART?

 

[Loomah]

Hi
This is all done from within the same workbook but you can adapt it for different books.

Sub a()
Dim sName As String
Dim dDate As Date
Dim sWkSht As String
Dim lRow As Long
Dim fnd As Range

With Worksheets("Sheet3")
    sName = .Range("a1")
    dDate = .Range("B1")
    sWkSht = .Range("C1")
End With
With Worksheets("Sheet2")
    lRow = .Range("A65536").End(xlUp).Row
    'find name
    Set fnd = .Range("A2:A" & lRow).Find(sName, , , xlWhole, xlByRows, xlNext)
    If fnd Is Nothing Then
        Cells(lRow, 1).Offset(1, 0) = sName
    End If
    'find date
    Set fnd = Rows(1).Cells.Find(dDate, , , xlWhole, xlByRows, xlNext)
    If fnd Is Nothing Then
        .Range("IV1").End(xlToLeft).Offset(0, 1) = sName
    End If
End With
Set fnd = Nothing
'check for existence of sheet
If Not SheetExists(sWkSht) Then
    Worksheets.Add after:=Worksheets(Worksheets.Count)
    ActiveSheet.Name = sWkSht
End If
End Sub

Function SheetExists(sht As String) As Boolean
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    If ws.Name = sht Then
        SheetExists = True
        Exit Function
    End If
Next
End Function

;-)


If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[Loomah]

Typical!
Just discovered a couple of minor errors in the above code (for now!)

Just use these lines to replace the same lines above - there was a missing . (dot) and a wrong variable name used)

    'find name
    Set fnd = .Range("A2:A" & lRow).Find(sName, , , xlWhole, xlByRows, xlNext)
    If fnd Is Nothing Then
        .Cells(lRow, 1).Offset(1, 0) = sName
    End If
    'find date
    Set fnd = Rows(1).Cells.Find(dDate, , , xlWhole, xlByRows, xlNext)
    If fnd Is Nothing Then
        .Range("IV1").End(xlToLeft).Offset(0, 1) = dDate
    End If

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[Loomah]

Hi - yet again
The validation when checking for the existence of the sheet isn't good enough as it's case sensitive in the original which could lead to the sheet name "sheet1" not being found as it's "Sheet1" in the workbook

Just need to substitute this line in the function

If ws.Name = sht Then

with

If UCase(ws.Name) = UCase(sht) Then

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[monagan]

I'm have trouble with this line

Worksheets.Add after:=(Worksheets.Count)

Method 'Add' of Object 'Sheets' Failed

"We have enough youth, How about a fountain of SMART?

 

[Loomah]

That's odd because I did actually make that mistake originally! But the code as I posted it is:-
Worksheets.Add after:=Worksheets(Worksheets.Count)

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[monagan]

The code:
For Each ws In ThisWorkbook.Worksheets
If UCase(ws.Name) = UCase(sht) Then
SheetExists = True
Exit Function

I am having a problem with, I think it is ws

Where is that getting set?

"We have enough youth, How about a fountain of SMART?

 

[Loomah]

Hi again
This is part of the function that determines whether or not the worksheet named actually exists. ws is an object variable declared at the begining of the function as type worksheet.

ie
Function SheetExists(sht As String) As Boolean
Dim ws As Worksheet

It is then used to reference each worksheet in the worksheets collection
ie
For Each ws In ThisWorkbook.Worksheets
etc etc

Precisely what kind of problems are you having?
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[monagan]

I believe the code :

Function SheetExists(sht As String) As Boolean
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Name = sht Then
SheetExists = True
Exit Function
End If
Next
End Function

Is coming back false every time.

ws.Name is "Sheet1" sht is correct.
"Sheet1" is the name of the worksheet the data is on, and sht is the name of where I want the data to go

"We have enough youth, How about a fountain of SMART?

 

[Loomah]

Are you sure that the name of the sheet is "Sheet1" and not " Sheet1" or "Sheet1 " etc?

This is the most common cause of theses type of problems - the name 'appears' to be right on the sheets tab but occasionally has a leading or trainling space. Or both!

Of course it might not be the sheet tab but the input that has rogue spaces.

So would you check on that first?

If that is the problem you can work around it by using

If UCase(ws.Name) = [b]trim[/b]UCase(sht)

I haven't used Trim on the ws.name as you should fix any spaces in the sheet names if they exist.

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[monagan]

I think I didn't explain enough.

I feel that this problem is occurring because your code is meant for one workbook, and mine involves two.

workbook1 has sheet1

and workbook2 has the worksheets to look through, and find. The name of the worksheet to find is in workbook1, and it looks in workbook2 for it.

I think that is why ws.name is coming up Sheet1, instead of the name typed in a cell on worksheet1.

"We have enough youth, How about a fountain of SMART?

 

[monagan]

i figured that part out, now how do i put in a value at an intersection of the date an name within the code you supplied


"We have enough youth, How about a fountain of SMART?

 

[Loomah]

I'd forgotten about that bit - the 2 workbooks!

I haven't got time at the moment (it's 6pm here and food and footy beckons!) but I'll set it up to work with 2 workbooks tomorrow assuming
workbook1 has the input
workbook2 is where to find the name, date and sheet.

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[SkipVought]

monagan,

What's the status? Post your current code and explain what it needs to do with 2 sheets.

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[Loomah]

Hi
Didn't see you second post!!

the intesect can be woked out at this point

Dim IntersectRow As Long
Dim IntersectCol As Integer

'find name
Set fnd = .Range("A2:A" & lRow).Find(sName, , , xlWhole, xlByRows, xlNext)
If fnd Is Nothing Then
    .Cells(lRow, 1).Offset(1, 0) = sName
    IntersectRow = lRow + 1
Else
    IntersectRow = fnd.Row
End If
'find date
Set fnd = Rows(1).Cells.Find(dDate, , , xlWhole, xlByRows, xlNext)
If fnd Is Nothing Then
    .Range("IV1").End(xlToLeft).Offset(0, 1) = dDate
    IntersectCol = .Range("IV1").End(xlToLeft).Offset(0, 1).Column
Else
    IntersectCol = fnd.Column
End If

'The intersect occurs at
Cells(IntersectRow, IntersectCol) = SOMETHING

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[monagan]

What I ended up doing here is this to accomodate my other workbook

With wsOther
EName = .Range("a14")
EDate = .Range("h7")
sWkSht = .Range("e18")
opensheet = .Cells(18, 5).Value
End With


AND...


Set wbTS = Workbooks("TIME SHEETS.xls")

For Each ws In wbTS.Worksheets
If UCase(ws.Name) = UCase(sht) Then
SheetExists = True
Exit Function
End If


Your code works excellent, it puts in the name in column A, and puts the date in the first row, now I am trying to incorporate my other code into it, so that hours worked will be in the cell that the date and the name meet(intersection)


"We have enough youth, How about a fountain of SMART?

 

[monagan]

is there a place in your code that stores the row and column number?

where I can incorporate something like this?

.Cells(Row, Col) = EHOURS


"We have enough youth, How about a fountain of SMART?

 

[monagan]

everytime an entry is made, the date is entered in the nest column, how do Istop this from happening?

I just want it so that if there is no 9/12/04 in row 1, then put it at the end

"We have enough youth, How about a fountain of SMART?

 

[monagan]

it's not find the first date here
so fnd =nothing

Set fnd = Rows(1).Cells.Find(EDate, , , xlWhole, xlByRows, xlNext)
If fnd Is Nothing Then



"We have enough youth, How about a fountain of SMART?

 

[Loomah]

monagan
Re the second of your last 4 posts the code I last posted is to give you the intersection.

As for not finding the dates this is frustrating because I can't remember the solution! I can't recreate the problem myself so I can't work it out either.

xlbo is just one contributor who has posted a solution to this problem countless times. Try doing a search on the site. I thought there might have been a FAQ but there isn't.

One little mistake I've just noticed that won't really affect anything is that
Set fnd = Rows(1).Cells.Find(EDate, , , xlWhole, xlByRows, xlNext)
should be
Set fnd = Rows(1).Cells.Find(EDate, , , xlWhole, xlByColumns, xlNext)

Sorry I can't be more help with this bit but, as I say, the find is working OK for me.

xlbo if your reading, help!!
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244