IF ... ELSE HELP please

[FurRelKT]

I can't get my if statements correct... desArray(0 to 2) never changes,
but srcArray, might not
have a str1 or str2 but only str3 or it can be that there is only str1
and non of the others.
How do i account for those combinations? I used a boolean, but i can't
get it to work. Is there another way i can do this? My basic need is if
the str1 is found 1st then it adds the sheet and renames. If str2 and
str3 is not found, i don't need to add another sheet, just process.
If str1 is not found, i need to continue with the loop, and it doesn't
need to add the sheet yet. If str2 is found then at that point add the
sheet and rename it. I str3 is not found, continue processing. If
Str1,str2 is not found continue with loop, if str3 is found, add a
sheet and rename it. See below where i commented out. thanks for any
suggestions.

Keri~

srcArray(0)="str1"
srcArray(1)="str2"
srcArray(2)="str3"
desArray(0)="str1A"
desArray(1)="str2A"
desArray(2)="str3A"

For i = 0 To 2
With searchRange
WhatToFind = srcArray(i)
Set FindFirst = .Find(What:=WhatToFind, _
LookIn:=xlValues,
LookAt:=xlWhole, _
after:=.Cells(.Cells.Count),
_
SearchDirection:=xlNext)
If FindFirst Is Nothing Then
MsgBox "Nothing found"
W = False
GoTo nextI
End If

.... do some stuff

strGroup = desArray(i)
Set FoundCell = Cells.Find(strGroup, _
LookIn:=xlValues,
LookAt:=xlWhole)

If i = 0 And W = True Then
dSheet.Copy after:=.Item(.Count)
ActiveSheet.Name = theName
End If

' If i = 1 And W = False Then
' dSheet.Copy after:=.Item(.Count)
' ActiveSheet.Name = theName
' End If
' If i = 2 And W = False Then
' dSheet.Copy after:=.Item(.Count)
' ActiveSheet.Name = theName
' End If

NextI:
Next i

 

[Henning]

inline comment:

I can't get my if statements correct... desArray(0 to 2) never changes,
but srcArray, might not
have a str1 or str2 but only str3 or it can be that there is only str1
and non of the others.
How do i account for those combinations? I used a boolean, but i can't
get it to work. Is there another way i can do this? My basic need is if
the str1 is found 1st then it adds the sheet and renames. If str2 and
str3 is not found, i don't need to add another sheet, just process.
If str1 is not found, i need to continue with the loop, and it doesn't
need to add the sheet yet. If str2 is found then at that point add the
sheet and rename it. I str3 is not found, continue processing. If
Str1,str2 is not found continue with loop, if str3 is found, add a
sheet and rename it. See below where i commented out. thanks for any
suggestions.

Keri~

srcArray(0)="str1"
srcArray(1)="str2"
srcArray(2)="str3"
desArray(0)="str1A"
desArray(1)="str2A"
desArray(2)="str3A"

For i = 0 To 2
With searchRange
WhatToFind = srcArray(i)
Set FindFirst = .Find(What:=WhatToFind, _
LookIn:=xlValues,
LookAt:=xlWhole, _
after:=.Cells(.Cells.Count),
_
SearchDirection:=xlNext)
If FindFirst Is Nothing Then
MsgBox "Nothing found"
W = False
GoTo nextI

Here you will jump past everything below, to the label nextI:
So you will only continue if W = True.

End If

... do some stuff

strGroup = desArray(i)
Set FoundCell = Cells.Find(strGroup, _
LookIn:=xlValues,
LookAt:=xlWhole)

If i = 0 And W = True Then
dSheet.Copy after:=.Item(.Count)
ActiveSheet.Name = theName
End If

' If i = 1 And W = False Then
' dSheet.Copy after:=.Item(.Count)
' ActiveSheet.Name = theName
' End If
' If i = 2 And W = False Then
' dSheet.Copy after:=.Item(.Count)
' ActiveSheet.Name = theName
' End If

NextI:
Next i

/Henning

 

[John Bundy]

I don't see for starters anything that sets/resets W to True, that means once
it turns false it stays that way. Unless I misunderstand that W is boolean
and set to true if the str(x) is found, then you are only handling 4 events
1. Nothing found continue on
2. Store 1 is found add a sheet
3. Store 2 is not found add a sheet
4. Store 3 is not found add a sheet.

This doesn't match your description. Under what conditions would a sheet be
added? It looks like by your description you want a sheet added no matter
what.

-John

 

[FurRelKT]

I solved it by this and works like i want:

If theName <> sSheet.Name Then
theName = sSheet.Name
Else
GoTo NextB
End If
With dBook.Worksheets
dSheet.Copy after:=.Item(.Count)
ActiveSheet.Name = theName

NextB:
dBook.Activate
Worksheets(theName).Select